Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Query challenge - 5days back and 5days forward

Author  Topic 

jung1975
Aged Yak Warrior

503 Posts
Posted - 2006-12-06 : 16:31:47
I have a data like below:


ID	Test_Date	Lab	EPO	TX
111	10/1/2006		1100	
111	10/3/2006		1120	1
111	10/4/2006		1500	1
111	10/15/2006	28	1500	1
111	10/20/2006		1400	1
111	10/27/2006	30	1500	1
111	10/28/2006		1600	1
111	10/29/2006	40	1700	1
111	10/30/2006		1100	
111	11/1/2006		1200	1
111	11/3/2006		1100	1
111	11/6/2006	25	1300	1
111	11/7/2006		1700	1
111	11/8/2006	39.5	1200	1
111	11/9/2006			1
111	11/10/2006		1800	
112	10/2/2006			1
112	10/5/2006	32		
112	10/6/2006		1700	1
112	10/11/2006			1
112	10/14/2006		2000	
112	10/24/2006	24		1
112	10/29/2006		2200	
112	11/4/2006			
112	11/6/2006	42	3000	1
112	11/8/2006		2400	
112	11/11/2006		2400	1
112	11/20/2006		2300	
112	11/21/2006		1000	1
112	11/22/2006	35	1200	
112	11/24/2006		1500	1
112	11/26/2006	40	1700	
112	11/27/2006		1100	1
112	11/28/2006		1300	
112	11/29/2006		1400	1
112	11/30/2006		1100	1



What I am trying to do is grab the last lab value of previous month in each ID and if the lab value is > 39,
After that,
1) Review 5 days prior preceding and including the lab result date, and total the number of tx and amount of EPO. Do the calculation (EPO / TX).
In this case, PID 111 has 40 as the last lab value of the prior month, so we do our calculation (1700 + 1600 + 1500 +1400+ 1500 ) / 5 = 1540

2) Review proceeding 5 calendar days (excluding the lab result date), and total the number of tx and amount of EPO. Do the calculation ( EPO/Tx)
In this case, ( 1200 + 1100 + 1300 + 1700+1200) / 5 = 1300.

Here we do the comparison 1) and) 2) if 1) > 2) then we put N in the Y_N column, if 1) < 2) then Y, if 1) = 2) then E
In this case, 1) > 2), so we put N in the Y_N column




If the last lab value of previous month is < 39, then we go to the current month, in this case November, 2006 and look for Lab value > 39. If the lab value is less then 39 then just skip until you find a lab value > 39 in the same ID and then do our calculation like above.
In this case , we have 39.5 on 11/8/2006, so we do the same calculation.. five days back ( including the lab test date) and five days forward ( excluding the lab test date).

1) Five days back - (1200 + 1700 + 1300+1100+1200) / 5 = 1300
2) Five days forward – (1800) / 1 = 1800
In this case 1) is < 2), so we put Y in the Y_N column


And we move to the next ID and do the same calculation. In this case, the last lab value of prior month for ID=112 is < 39, so we are just going to go to the current month ( which is November , 2006) and do the calculation. In this case there is lab value > 39 on 11/6/2006, so we do 5 days back and 5 days forward calculation.

And there is also the lab test value 40 on 11/26/2006, so we do the 5 days back and 5 days forward calculation as well.

The desire output should look like:

ID	Test_Date	Lab	EPO	TX	Y_N
111	10/1/2006		1100		
111	10/3/2006		1120	1	
111	10/4/2006		1500	1	
111	10/15/2006	28	1500	1	
111	10/20/2006		1400	1	
111	10/27/2006	30	1500	1	
111	10/28/2006		1600	1	
111	10/29/2006	40	1700	1	N
111	10/30/2006		1100		
111	11/1/2006		1200	1	
111	11/3/2006		1100	1	
111	11/6/2006	25	1300	1	
111	11/7/2006		1700	1	
111	11/8/2006	39.5	1200	1	Y
111	11/9/2006			1	
111	11/10/2006		1800		
112	10/2/2006			1	
112	10/5/2006	32			
112	10/6/2006		1700	1	
112	10/11/2006			1	
112	10/14/2006		2000		
112	10/24/2006	24		1	
112	10/29/2006		2200		
112	11/4/2006				
112	11/6/2006	42	3000	1	Y
112	11/8/2006		2400		
112	11/11/2006		2400	1	
112	11/20/2006		2300		
112	11/21/2006		1000	1	
112	11/22/2006	35	1200		
112	11/24/2006		1500	1	
112	11/26/2006	40	1700		N
112	11/27/2006		1100	1	
112	11/28/2006		1300		
112	11/29/2006		1400	1	
112	11/30/2006		1100	1



to be able to create a sample data. Hereis the script:
CREATE TABLE [dbo].[lab](
[ID] [int] NULL,
[Test_Date] [datetime] NULL,
[Lab] [int] NULL,
[EPO] [int] NULL,
[TX] [int] NULL,
[Y_N] [nvarchar](1)
) ON [PRIMARY]


insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061001',null,1100,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061003',null,1120,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061004',null,1500,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061015',28,1500,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061020',null,1400,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061027',30,1500,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061028',null,1600,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061029',40,1700,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061030',null,1100,null)
insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061101',null,1200,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061103',null,1100,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061106',25,1300,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061107',null,1700,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061108',39.5,1200,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061109',null,null,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(111,'20061110',null,1800,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061002',null,null,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061005',32,null,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061006',null,1700,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061011',null,null,1)
insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061014',null,2000,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061024',24,null,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061029',null,2200,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061104',null,null,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061106',42,3000,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061108',null,2400,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061111',null,2400,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061120',null,2300,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061121',null,1000,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061122',35,1200,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061124',null,1500,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061126',40,1700,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061127',null,1100,1)
insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061128',null,1300,null)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061129',null,1400,1)

insert into lab (ID,Test_Date,Lab,EPO, TX) values(112,'20061130',null,1100,1)



i am using sql server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-07 : 01:56:15
Is it really FIVE DAYS back? With your examples, you calculate the latest 5 readings.
quote:
1) Review 5 days prior preceding and including the lab result date, and total the number of tx and amount of EPO. Do the calculation (EPO / TX).
In this case, PID 111 has 40 as the last lab value of the prior month, so we do our calculation (1700 + 1600 + 1500 +1400+ 1500 ) / 5 = 1540

1700 10/29/2006
1600 10/28/2006
1500 10/27/2006
1400 10/20/2006
1500 10/15/2006
quote:
2) Review proceeding 5 calendar days (excluding the lab result date), and total the number of tx and amount of EPO. Do the calculation ( EPO/Tx)
In this case, ( 1200 + 1100 + 1300 + 1700+1200) / 5 = 1300.
You are missing reading for 10/30/2006, which has a TX value of NULL.

Please restate your question more properly.



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts
Posted - 2006-12-07 : 10:11:04
you are corrct pesco..sorry for the confusion

It should be the actual 5 days ( including the lab test date) when you goiung backward...
(1700+1600+1500) /3


Same thing.it should be the actual calendar 5 days when you going forward
it should be (1100 +1200 + 1100) /24 .. I forgot Octeber , 30th.

yes..the calculation has to be done by each ID and previous/current month ( in thic case, Oct and Nov, 2006).. .First, We look for the last labvalue in the privious month to see if it's > 39 .. if it is we do the cacculation from that point , if it;s not > 39 then jump to the current month and look for the labvalue > 39 and do the calculation in the same ID... then move to the next ID

Does it make sense?


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-12-11 : 06:23:21
This is not complete, but has everything but "less than 39 case".
select		q.ID,
		q.LastTestDate,
		w.lab,
		(select isnull(sum(w.epo), 0) from lab w where w.id = q.id and w.test_date between dateadd(day, -4, q.LastTestDate) and q.lasttestdate) pepo,
		(select isnull(sum(w.tx), 0) from lab w where w.id = q.id and w.test_date between dateadd(day, -4, q.LastTestDate) and q.lasttestdate) ptx,
		(select isnull(sum(w.epo), 0) from lab w where w.id = q.id and w.test_date between dateadd(day, 1, q.LastTestDate) and dateadd(day, 5, q.LastTestDate)) nepo,
		(select isnull(sum(w.tx), 0) from lab w where w.id = q.id and w.test_date between dateadd(day, 1, q.LastTestDate) and dateadd(day, 5, q.LastTestDate)) ntx,
		cast(null as smallmoney) as p,
		cast(null as smallmoney) as n,
		cast(null as char) as y_n
into		#stage
from		(
			select		a.ID,
					max(a.test_date) LastTestDate
			from		lab a
			where		a.lab is not null
			group by	a.id,
					year(a.test_date),
					month(a.test_date)
		) q			
inner join	lab w on w.id = q.id and w.test_date = q.LastTestDate


update	#stage
set	p =  case when ptx = 0 then 0 else 1.0 * pepo / ptx end,
	n =  case when ntx = 0 then 0 else 1.0 * nepo / ntx end
from	#stage

update	#stage
set	y_n = case when p > n then 'y' when p < n then 'n' else 'e' end
where	lab >= 39

select * From #stage

drop table #stage

drop table lab


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site