Search code examples
sql-servercommon-table-expressionlaglead

capture reoccurring seventh day in new column

I have the below table...

run_dt   check_type  curr_cnt
 6/1/21    ALL         50
 5/31/21   ALL         25
 5/26/21   ALL         43
 5/25/21   ALL         70
 6/1/21    SUB         23
 5/25/21   SUB         49

I would like to capture the value of what the check_type was seven days from the run_dt. What was the previous weekday value.

Something like...

run_dt   check_type  curr_cnt  prev_nt
 6/1/21    ALL         50        70
 5/31/21   ALL         25        
 5/26/21   ALL         43
 5/25/21   ALL         70
 6/1/21    SUB         23        49
 5/25/21   SUB         49

Can I use lead/lag or CTE?

What's the best option here, appreciate the feedback.

Solution

  • You could join the table to itself:

    SELECT
  a.run_dt,
  a.check_type,
  a.curr_cnt,
  b.curr_cnt as prev_nt
from table a
left join table b on b.run_dt = dateadd(d,-7,a.run_dt)