How to loop through a table with SQL looking for closest max date?

Question

activity_date   Employee_id
5/29/2017       1
4/15/2017       1
1/14/2017       2
4/14/2017       2
2/15/2017       2
6/15/2017       3
1/13/2017       4

How do I write a query that will take a snapshot for latest activity_date by employee as if it were 5/1/2017, then loop again and take a snapshot as if it where 4/1/2017 and so on.

This is the output I want for looping last 3 calendar months.

activity_date   Employee_id  snapshot_date
4/15/2009       1            5/1/2017
4/14/2009       2            5/1/2017
1/13/2009       4            5/1/2017
2/15/2009       2            4/1/2017
1/13/2009       4            4/1/2017
2/15/2009       2            3/1/2017
1/13/2009       4            3/1/2017

Thank you

I removed the incompatible database tags. Please tag only with the database you are using. — Gordon Linoff, Jun 29 '17 at 19:16
Are you sure employee_id 1 should have a record for 4/1 snapshot? That snapshot would have happened after the activity date — S3S, Jun 29 '17 at 19:50

S3S · Answer 1 · 2017-06-29T20:15:37.887

0

One way is UNION...

select
   employee_id,
   Max(activity_date) activity_date,
   '2017-05-01' as snap_shot
from SomeTable 
where activity_date <= '20170501'
group by employee_id 

union

select
   employee_id,
   Max(activity_date) activity_date,
   '2017-04-01' as snap_shot
from SomeTable 
where activity_date <= '20170401'
group by employee_id 

union

select
   employee_id,
   Max(activity_date) activity_date,
   '2017-03-01' as snap_shot
from SomeTable 
where activity_date <= '20170301'
group by employee_id

Or if you expect to have a bunch of dates, use a CTE... just set the @start and @end variables accordingly

declare @table table (activity_date date, Employee_id int)
insert into @table
values
('5/29/2017',1),
('4/15/2017',1),
('1/14/2017',2),
('4/14/2017',2),
('2/15/2017',2),
('6/15/2017',3),
('1/13/2017',4)


DECLARE @start DATE, @end DATE;
SELECT @start = '20170301', @end = '20170501'

;WITH n AS 
(
  SELECT TOP (DATEDIFF(month, @start, @end) + 1) 
  n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
),

x as
(
    SELECT DATEADD(month, n-1, @start)  DT
    FROM n
)



select
    Employee_id
    ,Max(activity_date) activity_date
    ,DT
from @table
inner join x on activity_date <= DT
group by Employee_id, DT
order by DT desc

edited Jun 29 '17 at 20:15

answered Jun 29 '17 at 19:21

S3S

24,809
5
26
45

Thank you, employee 3 does not return any records because the activity date is after the snapshot dates. Is there a way to simplify the code with a loop rather than doing a bunch of unions? – Bob Jun 29 '17 at 19:35
see the edit @Bob but in your data i dont' see how you are getting your 4th row – S3S Jun 29 '17 at 19:48
Thanks, I fixed the output. The join solution worked great. – Bob Jun 29 '17 at 20:09
No problem @Bob the CTE is something to keep in your back pocket – S3S Jun 29 '17 at 20:14
@Bob don't forget to accept correct answers and upvote useful ones – S3S Jul 05 '17 at 19:13

OLIVER.KOO · Answer 2 · 2017-06-29T19:55:21.340

0

would something like this work?

SELECT Max(activity_date), Employee_id,  '2017-05-01' AS snapshotDate
FROM YOUR_TABLE_NAME
WHERE activity_date <= '2017-05-01'
GROUP BY Employee_id

it gives you result like this:

2017-04-15  1   2017-05-01
2017-04-14  2   2017-05-01
2017-01-13  4   2017-05-01

EDIT: IF you want multiple and loop it through you can use a sql for loop. Here is the code:

CREATE TABLE prac(
    activity_date DATE,
    Employee_id int
)


INSERT INTO prac (activity_date, Employee_id)
VALUES ('2017-05-29', 1),
('2017-04-15', 1),
('2017-01-14', 2),
('2017-04-14', 2),
('2017-02-15', 2),
('2017-06-15', 3),
('2017-01-13', 4)

 DECLARE @i date = '2017-05-01'
        WHILE @i >= '2017-03-01'
        BEGIN
            SELECT Max(activity_date), Employee_id,  @i AS snapshotDate
            FROM prac
            WHERE activity_date <= @i
            GROUP BY Employee_id

            SET @i = DATEADD(MONTH,-1,@i)
        END

The above code will loop through month March to May and the output looks like this:

2017-04-15  1   2017-05-01
2017-04-14  2   2017-05-01
2017-01-13  4   2017-05-01

2017-02-15  2   2017-04-01
2017-01-13  4   2017-04-01

2017-02-15  2   2017-03-01
2017-01-13  4   2017-03-01

edited Jun 29 '17 at 19:55

answered Jun 29 '17 at 19:32

OLIVER.KOO

5,654
3
30
62

Yes, but that only captures the one snapshot date. I'm looking to create a dataset that loops through and takes many snapshots. – Bob Jun 29 '17 at 19:37
1

@Bob: That's against the fundamental design of SQL. You'll need to pair some functional logic with this. That could come from either your application or within a stored procedure, but there isn't a great way to do with with pure SQL. A pure SQL query like this has no way to *loop*. – Jacobm001 Jun 29 '17 at 19:39
I updated my response that includes code that loops through and take many snapshots – OLIVER.KOO Jun 29 '17 at 19:52
When ever you think about looping in SQL stop and think again. There's almost always a better way and VERY rarely will you ever, ever use a WHILE loop. The performance of this in SQL is god awful generally speaking. – S3S Jun 29 '17 at 19:54
@scsimon Thank you. Good tip. I will keep this in mind why using while loop in sql is a bad idea - https://stackoverflow.com/questions/3022965/which-is-faster-in-sql-while-loop-recursive-stored-proc-or-cursor – OLIVER.KOO Jun 29 '17 at 20:00

How to loop through a table with SQL looking for closest max date?

2 Answers2