0

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.

Toby
  • 135
  • 2
  • 17
  • If you had a row for *every* day you could use `LEAD`/`LAG`. You could use a Tally or Calendar Table to create rows for that, `LEAD`/`LAG` and then filter out the rows that didn't have data though. How big a date range are you going to be running for. – Thom A Jun 01 '21 at 13:28
  • Is there a possibility a row for seven days ago might not exist, if so should the value be null or something else? – Stu Jun 01 '21 at 13:37

1 Answers1

1

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)
mike6383
  • 36
  • 3