I have a data like below.Id DT EPO HGB QM 10days_back 7days_forward1001 2006-09-01 1100 32.4 1001 2006-09-06 1200 32.11001 2006-09-11 1200 32.11001 2006-09-22 1300 39.1 39.11001 2006-09-23 1400 40.51001 2006-09-25 1500 381001 2006-10-01 1500 32.4 1001 2006-10-06 1001 2006-10-11 2000 32.11001 2006-10-15 1100 42 1001 2006-10-23 1100 35 42 1001 2006-10-30 40.5 1002 2006-09-01 1100 32.4 1002 2006-09-06 1200 32.11002 2006-09-11 1200 401002 2006-09-15 1300 35.4 401002 2006-09-23 1400 40.51002 2006-09-25 1500 40.51002 2006-10-01 1500 32.4 1002 2006-10-06 1002 2006-10-11 2000 32.11002 2006-10-15 1100 39 1002 2006-10-23 1100 42 42 1002 2006-10-30 40.5 1003 2006-09-01 1100 32.4 1003 2006-09-06 1200 32.11003 2006-09-11 1200 401003 2006-09-14 1200 35.4 35.41003 2006-09-20 1400 40.51003 2006-09-25 1500 40.51003 2006-10-01 1500 32.4 1003 2006-10-07 1003 2006-10-11 2000 32.11003 2006-10-15 1100 39 1003 2006-10-23 1100 42 42 1003 2006-10-30 40.5
I would like to calculate the sum of EPO (10days_back) and the sum of EPO(7 days_forward ) where the last QM value of privious month is> 39 and equal to HGB within the same ID... possibly using the analystic function in sql server 2005FOr example,ID 1001 , the last lab value of privious month is 39.1 which is greate than 39 and there is the same value 39.1 in HGB on 2006-09-22, so we doing the calculation starting from 2006-09-2210 days_back: 09/22/06 ~ 09/13/06 --> 13007days forward : 09/23/06 ~ 09/29/06 --> 2900 ( 1400 + 1500)ID 1002 , the last lab value of privious month is 40 which is greate than 39 and there is the same value 40 in HGB on 2006-09-11, so we doing the calculation starting from 2006-09-1110 days_back: 09/11/06 ~ 09/02/06 --> 3500 ( 1200 + 1200 + 1100)7days forward : 09/12/06 ~ 09/18/06 --> 1300ID 1003 , the last QM value of privious is < 39 ( 35.4)m so we skip it,so the final output should look like:Id DT EPO HGB QM 10days_back 7days_forward1001 2006-09-01 1100 32.4 1001 2006-09-06 1200 32.11001 2006-09-11 1200 32.11001 2006-09-22 1300 39.1 39.1 1300 29001001 2006-09-23 1400 40.51001 2006-09-25 1500 381001 2006-10-01 1500 32.4 1001 2006-10-06 1001 2006-10-11 2000 32.11001 2006-10-15 1100 42 1001 2006-10-23 1100 35 42 1001 2006-10-30 40.5 1002 2006-09-01 1100 32.4 1002 2006-09-06 1200 32.1 1002 2006-09-11 1200 40 3500 1300 1002 2006-09-15 1300 35.4 40 1002 2006-09-23 1400 40.51002 2006-09-25 1500 40.51002 2006-10-01 1500 32.4 1002 2006-10-06 1002 2006-10-11 2000 32.11002 2006-10-15 1100 39 1002 2006-10-23 1100 42 42 1002 2006-10-30 40.5 1003 2006-09-01 1100 32.4 1003 2006-09-06 1200 32.11003 2006-09-11 1200 401003 2006-09-14 1200 35.4 35.41003 2006-09-20 1400 40.51003 2006-09-25 1500 40.51003 2006-10-01 1500 32.4 1003 2006-10-07 1003 2006-10-11 2000 32.11003 2006-10-15 1100 39 1003 2006-10-23 1100 42 42 1003 2006-10-30 40.5
How can I do somthing like this using Partiion by in sql server 2005? or any other methods...
Analystic function and query challenge
Posted - 2007-01-25 : 01:44:16
