quote:

---

Originally posted by visakh16

are you using sql 2005? over works only in sql 2005 & above

---

quote:

---

Originally posted by Rosembm1

quote:

---

Originally posted by visakh16

are you using sql 2005? over works only in sql 2005 & above

---

Yes - 2005. The query is against a detail table with the group by- is that a problem? I tested the query against a summary table and it does work, so I think I have the basics.


MWRosenblatt

---

select unit_group, [ICU Year] , [ICU Month],[total swabs by unit],
[Total Swabs]= sum(o.Swab_YN)
from
(
select 
lu.unit_group , 
gen.udfleadingzeroformat(cast(datepart(mm,adt.admitdate)as varchar(2)),2) as [ICU Month],
cast(datepart(yy,adt.admitdate) as varchar(4)) as [ICU Year], 
o.Swab_YN,
-- this aggregate query does not work 
[total swabs by unit] = sum(o.Swab_YN) OVER (partition by lu.unit_group) 
from 
[cc-clbisql51\sqlp105].qpsi_data.inf.ICUAdmit_Outcomes as O 
inner join [cc-clbisql51\sqlp105].qpsi_data.Adt.ICUAdmissions as ADT
on ADT.icuadmit_ID= O.icuadmit_ID 
left join [cc-clbisql51\sqlp105].qpsi_data.inf.SwabResults as swab 
on O.swab_YN_ID=swab.swab_id
and swab.collectdate between adt.admitdate and adt.dischargedate 
left join [cc-clbisql51\sqlp105].qpsi_data.clr.mupirocin as clr
on clr.mupirocin_id = o.mupirocin_id
left join qpsi_data.inf.Unit_Lookup as LU
on adt.Unit=LU.Nursing_Unit 
and (adt.admitdate between unitchangedate and unitenddate)
where O.HospitalAdmitDate is not null and O.HospitalAdmitDate >= '1/1/2008'
and unit_group is not null 
)t
group by unit_group, [ICU Year] , [ICU Month],[total swabs by unit]
order by unit_group, [ICU Year] , [ICU Month]