CODEWITHSUNDEEP
sqlsql-serversql-server-2008
zhifff
zhifff

Reputation: 309

How to iterate through a table

ID  Date    flag_14  flag_21
1   1/1/2013    1     1
1   1/16/2013   1     0
1   1/19/2013   0     0
1   1/23/2013   0     1
1   1/26/2013   0     0
2   1/1/2013    1     1    
2   1/18/2013   1     0

Hi, I am sorry this may be a stupid question but I really could use some help. So I want to create the above output. the first 2 columns are input (id and date). The logic is to use 14 days and 21 days as cutoffs to decide whether a record is kept within same id by comparing the dates of current record and last kept one. the first record for each id is always kept (flag is 1 to indicate "keep", 0 indicates otherwise) .

For example, for id 1, if the cutoff is 21, the second record's date is 1/16/2013, which is 15 days after the previous kept one (which is the first record 1/1/2013), 15<21 so the flag for second record is 0. Same for the third record, 1/19/2013 and 1/1/2013 are 18 days apart, 18<21 so flag =0. But for the 4th record, 1/23/2013 and 1/1/2013 are 22 days apart, 22>21, so this record is kept, flag=1. Then 5th record is to be compared with the last kept one (now is the 4th record) , 1/26/2013 and 1/23/2013 are 3 days apart, 3<21, so flag =0.

Is there a simple to iterate this using something like partition by?

Thanks!!

Upvotes: 2

Views: 100

Answers (2)

code save
code save

Reputation: 1106

Please Try it ,, It's Working as per question

with cte as
(
select o.*
from( 
    select  yourid,yourdate,ROW_NUMBER () Over (partition by yourid order by (select(0)) ) as RN
    from sedata 
    ) as o 
),
cte2 as
(
select r.*,
    case when  r.RN %2=0
    then
    (select DAY(r.YourDate) - DAY(r1.YourDate) where r.yourid = r1.yourid) 
    else
   (select DAY( r.YourDate) - DAY(min(YourDate)) from sedata where r.yourid = r1.yourid )
    end as Total   


 from cte r left  join cte r1 ON r.RN-1 = r1.RN  
 )
 select *
  ,case when   Total is null then 1 when Total >14 and Total <21 then 1   else 0  end as flag_14 ,
   case when  Total is null then 1 when Total > 21 then 1   else 0  end  as flag_21

  from cte2   where Total is not null or RN=1

Upvotes: 1

Question3CPO
Question3CPO

Reputation: 1202

If we're talking about ordering the data based on date, like in your example, we can use something similar to the below code (note, I'm only using YourID and YourDate fields):

CREATE TABLE SeData(
    YourID INT,
    YourDate DATE
)

INSERT INTO SeData
VALUES (1,'2013-01-01')
 , (1,'2013-01-16')
 , (1,'2013-01-19')
 , (1,'2013-01-23')
 , (1,'2013-01-26')

;WITH Roll AS(
    SELECT ROW_NUMBER() OVER (ORDER BY YourDate) ID
        , YourID
        , YourDate
    FROM SeData
)
SELECT *
    , (DAY(r1.YourDate) - DAY(r.YourDate)) AS NumberBetween -- just an example of comparing
FROM Roll r
    INNER JOIN Roll r1 ON r.ID = (r1.ID - 1)

From there, you can compare the day from r to r1 and update/insert what you need.

Update: I know this works on 2012 (though it has LAG and LEAD), 2008R2 and not sure it runs on 2005 or below.

Upvotes: 0

Related Questions