December 6, 2006 at 2:47 pm
I have a data like below:
IDTest_DateLabEPOTX11110/1/2006110011110/3/20061120111110/4/20061500111110/15/2006281500111110/20/20061400111110/27/2006301500111110/28/20061600111110/29/2006401700111110/30/2006110011111/1/20061200111111/3/20061100111111/6/2006251300111111/7/20061700111111/8/200639.51200111111/9/2006111111/10/2006180011210/2/2006111210/5/20063211210/6/20061700111210/11/2006111210/14/2006200011210/24/200624111210/29/2006220011211/4/200611211/6/2006423000111211/8/2006240011211/11/20062400111211/20/2006230011211/21/20061000111211/22/200635120011211/24/20061500111211/26/200640170011211/27/20061100111211/28/2006130011211/29/20061400111211/30/200611001
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:
IDTest_DateLabEPOTXY_N11110/1/2006110011110/3/20061120111110/4/20061500111110/15/2006281500111110/20/20061400111110/27/2006301500111110/28/20061600111110/29/20064017001N11110/30/2006110011111/1/20061200111111/3/20061100111111/6/2006251300111111/7/20061700111111/8/200639.512001Y11111/9/2006111111/10/2006180011210/2/2006111210/5/20063211210/6/20061700111210/11/2006111210/14/2006200011210/24/200624111210/29/2006220011211/4/200611211/6/20064230001Y11211/8/2006240011211/11/20062400111211/20/2006230011211/21/20061000111211/22/200635120011211/24/20061500111211/26/2006401700N11211/27/20061100111211/28/2006130011211/29/20061400111211/30/200611001
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
December 6, 2006 at 2:49 pm
I have a data like below:
IDTest_DateLabEPOTX11110/1/2006110011110/3/20061120111110/4/20061500111110/15/2006281500111110/20/20061400111110/27/2006301500111110/28/20061600111110/29/2006401700111110/30/2006110011111/1/20061200111111/3/20061100111111/6/2006251300111111/7/20061700111111/8/200639.51200111111/9/2006111111/10/2006180011210/2/2006111210/5/20063211210/6/20061700111210/11/2006111210/14/2006200011210/24/200624111210/29/2006220011211/4/200611211/6/2006423000111211/8/2006240011211/11/20062400111211/20/2006230011211/21/20061000111211/22/200635120011211/24/20061500111211/26/200640170011211/27/20061100111211/28/2006130011211/29/20061400111211/30/200611001
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:
IDTest_DateLabEPOTXY_N11110/1/2006110011110/3/20061120111110/4/20061500111110/15/2006281500111110/20/20061400111110/27/2006301500111110/28/20061600111110/29/20064017001N11110/30/2006110011111/1/20061200111111/3/20061100111111/6/2006251300111111/7/20061700111111/8/200639.512001Y11111/9/2006111111/10/2006180011210/2/2006111210/5/20063211210/6/20061700111210/11/2006111210/14/2006200011210/24/200624111210/29/2006220011211/4/200611211/6/20064230001Y11211/8/2006240011211/11/20062400111211/20/2006230011211/21/20061000111211/22/200635120011211/24/20061500111211/26/2006401700N11211/27/20061100111211/28/2006130011211/29/20061400111211/30/200611001to 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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply