quote:

---

Originally posted by tupacmoche

With respect that is clear that there is a duplicate, I pointed out the same thing. That is why when, I remove the DISTINCT key word, I double the number of records but when I add it the duplicates are gone. The problem is when I use an aggregate function COUNT(MYFIELD)with a GROUP BY the number of records are counted twice while other are not. Strange

---

select P4DistrictNumber, Cycle, WorkSetID,
count(CASE WHEN Rn = 1 THEN msi.MeterSessionInputKey END) as ASND
from
(
Select ws.p4Districtnumber, ws.cycle, ws.worksetid, 
msi.MeterSessionInputKey,
ROW_NUMBER() OVER (PARTITION BY ws.p4Districtnumber, ws.cycle, ws.worksetid ORDER BY (SELECT 1)) AS Rn
from fcs.dbo.WorkSet as ws

left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID

left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey

left outer join fcs.dbo.MeterSessionOutput as mso
on msi.MeterSessionInputKey = mso.MeterSessionInputKey

where msi.P4UtilityTypeReadIndicator != 'F' and 
wa.RetiredFlag = 0 and 
wa.LoadStatusIndicator <> 0 and 
wa.LoadStatusIndicator <> 4 and 
ws.StartDateTime IS NOT NULL and 
wa.RetiredFlag = 0 and
mso.ReadCode <> 0 and
ws.Cycle <> 75 and
ws.Cycle = 16 and
mso.BestResultIndicator = 2 and
ws.P4DistrictNumber = 45 and
ws.WorkSetID = '00016456' and
case
when 
cast(wa.startdatetime as date) <= cast(ws.scheduledreaddate as date) and
wa.workfiltername not in ('DNRs','Type 2s/3s') or 
((ws.worksetid %100 < 50 and
substring(ws.worksetid, len(ws.worksetid) - 3, 1) = 0))
then 1
End = 1
)t
group by P4DistrictNumber, Cycle, WorkSetID
order by P4DistrictNumber, Cycle, WorkSetID