I am trying to write a stored proc the calculates a moving average over
three periods. In the following example, I need to stratify the data by
personID and RecordID in the #Temp table, but I am not sure how to do
it. Right now I am restricting the data I use to build my time series by
personID and I get the results I want *by PersonID*. If I can figure out
how stratify by personID so I don't have to use this restriction, I'm
sure I can extend it to the RecordID.
Create Table #Temp(
tmpID int identity,
DetailID int,
RecordID int,
AdminDate Datetime,
AdminTime datetime,
Status tinyint,--decimal(9,2),
Location varchar(100),
PersonID char(9),
PatientName varchar(100),
DOB Datetime,
Drug varchar(100),
Sort varchar(10))
--populate with data by personID
insert into
#Temp(DetailID,RecordID,AdminDate,AdminTime,Status ,Location,PersonID,Pat
ientName,DOB,Drug,Sort)
Select MD.PatMedOrderDetailID, MD.PatMedOrderID, M.Date as AdminDate,
Case M.Time When 'A' then '8:00:00 AM' When 'N' then '12:00:00 AM' When
'P' then '4:00:00 AM'
When 'H' then '8:00:00 PM' else M.Time End as Admintime,
100*M.Status, P.Location,P.PersonID, P.Name as PatientName, P.DOB,
D.GenericName + ' (' + D.TradeName + ') ' +
D.Strength,Left(P.Location,3)
From PatMedOrderDetail MD Inner Join PatMedOrder MO on MD.PatMedOrderID
= MO.PatMedOrderID
inner Join PatMedPass M on MD.PatMedOrderDetailID =
M.PatMedOrderDetailID
inner join Patient P on M.PersonID = P.PersonID
inner join Drugs D on MO.DrugID = D.DrugID
Where P.PersonID = '000126230'
Order by P.PersonID,MD.patMedorderID, M.Date, M.Time
Select * from #Temp -- to view entire set
--returns relevant rows
Select Derived.RefusalRate,T.* from #Temp T inner join
(select t1.tmpID, avg(t2.Status) as RefusalRate
from #Temp t1 cross join #Temp t2
WHERE t1.tmpID>=3 AND t1.tmpID BETWEEN t2.tmpID AND t2.tmpID+2
group by T1.tmpID
having avg(t2.Status)< 100
) as Derived on T.tmpID = Derived.tmpID
Drop Table #Temp
*** Sent via Developersdex
http://www.developersdex.com ***
