Grouping based on count

[luvmy2kids]

I have the below code:

select cd.custdeptname, costcenterdesc, weekend, 'Regular Hours' = SUM(case when t.paycodeid = 1 then ISNULL(t.units,0) else 0 end),
'Overtime 1' = SUM(case when t.paycodeid = 2 then ISNULL(t.units,0) else 0 end)--, t.paycodeid
from prchecktc r
inner join vw_tcdetailall t on t.tcdetailid = r.tcdetailid
inner join vw_tcheaderall h on h.tcid = t.tcid
inner join ss_htrnj.dbo.custcustomer c on c.custid = r.custid
left outer join custdepartment cd on cd.custid = c.custid and cd.custdeptid = t.custdeptid
left outer join custcostcenter cc on cc.custid = c.custid and cc.costcenterid = t.costcenterid
where employeeid = 38716 and h.weekend between '2008-10-01' and '2008-10-31' and t.paycodeid in (1,2,3,4,5,6,7)
Group by weekend, cd.custdeptname, costcenterdesc order by weekend
Returns these results:

38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 462 55.5 15.5 10/04/2008
38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 452 10.5 4.25 10/11/2008
38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 462 55.5 21.75 10/11/2008
38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 462 64.5 24.5 10/18/2008

I only want to return fields that have more than 1 costcenter for the same week, therefore only these 2 rows should display:

38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 452 10.5 4.25 10/11/2008
38716 RTI Field Services 541727661 MARYANN EDMONDS 209009 462 55.5 21.75 10/11/2008

How can I achieve that?

Thanks!!!

 

[markros]

Try adding at the end

having count(distinct(weekend)) > 1

 

[RyanEK]

declare @test table (idx int, val int, wk varchar(3))
insert into @test values (1,10,'aaa')
insert into @test values (2,20,'bbb')
insert into @test values (3,30,'bbb')
insert into @test values (4,40,'ccc')

select * 
from @test t1
where exists(
 select t2.wk 
 from @test t2
 where t1.wk = t2.wk
 group by t2.wk 
 having count(*) > 1
)