Query problem

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)