469,366 Members | 2,243 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

time-series SQL code.


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 ***
Jul 23 '05 #1
1 2005
On Fri, 15 Apr 2005 14:24:04 GMT, anthony hanson wrote:

(snip)

Hi anthony,

I just replied to your post in microsoft.public.sqlserver.programming.
Please post to one group only!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Bart Nessux | last post: by
5 posts views Thread by David Stockwell | last post: by
6 posts views Thread by David Graham | last post: by
8 posts views Thread by peterbe | last post: by
3 posts views Thread by Szabolcs Nagy | last post: by
5 posts views Thread by Erich Schreiber | last post: by
1 post views Thread by Drew | last post: by
1 post views Thread by davelist | last post: by
9 posts views Thread by Ron Adam | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.