I have a stored procedure where I select the latest/max submit_date data for a tracker_date and client from a source table called
Tracker_Live, and insert it into a new table called
I am wanting to do the same as the above, but for last month, where last month is one month less than today's month.
It seems there should be a way to use
DATEPART(m, DATEADD(m, -1, getdate()) to get last month and then max of that month.
There are multiple submissions per month to the source table, and the submit_date is recorded as 'submit_date'.
Source table, called
Tracker_Live, example (note there are two submit_dates per month for this client):
Assuming today's date is
2014-11-22, and knowing that other
client_nbr's may have a different latest submit_date for October, I would want the destination table, called
Tracker_Live_Last_Month to have the following data:
The stored procedure code that I use to get the max submit_date to insert into a table called
delete from Tracker_Live_Latest;
insert into tracker_live_latest
(client_nbr, tracker_date, submit_date)
select client_nbr, tracker_date, max(submit_date)
group by client_nbr, tracker_date
order by client_nbr, tracker_date;
set retainer = tl.retainer, live = tl.live, bonus = tl.bonus
from tracker_live as tl
where tracker_live_latest.Client_Nbr = tl.Client_Nbr
and tracker_live_latest.Tracker_Date = tl.Tracker_Date
and tracker_live_latest.Submit_Date = tl.Submit_Date
which will return the following from the above source table:
Any help to insert the correct data into
Tracker_Live_Last_Month is appreciated!!