Sql-server – How to return data from a table where the data is the max date of the previous month’s data submissions (-1 of current date)

sql serversql-server-2008-r2t-sql

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 Tracker_Live_Latest.

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):

source tabe, tracker_live

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:

enter image description here

The stored procedure code that I use to get the max submit_date to insert into a table called Tracker_Live_Latest is:

delete from Tracker_Live_Latest;

insert into tracker_live_latest 
(client_nbr, tracker_date, submit_date)
select client_nbr, tracker_date, max(submit_date)
from tracker_live 
group by client_nbr, tracker_date 
order by client_nbr, tracker_date;

update tracker_live_latest 
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:

sql_max

Any help to insert the correct data into Tracker_Live_Last_Month is appreciated!!

Shara

Best Answer

Expanding on James' answer

Try this code on SqlFiddle.

Also check out @SqlKiwi Paul White's post regarding Delete vs Truncate 

create table tracker_live (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live primary key (client_nbr, tracker_date, submit_date));

insert into tracker_live (client_nbr, tracker_date, submit_date, live, retainer, bonus) values
   (9,'2014-10-01','2014-10-08',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-08',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-08',45585.4029,3,3)
  ,(9,'2014-10-01','2014-10-17',537841.5657,1,1)
  ,(9,'2014-11-01','2014-10-17',890293.714,2,2)
  ,(9,'2014-12-01','2014-10-17',45585.4029,3,3)
  ,(9,'2014-10-01','2014-11-13',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-13',907925.1072,2,2)
  ,(9,'2014-12-01','2014-11-13',392917.5237,3,3)
  ,(9,'2014-10-01','2014-11-18',2321938.59,1,1)
  ,(9,'2014-11-01','2014-11-18',795725.1072,2,2)
  ,(9,'2014-12-01','2014-11-18',725835.0474,3,3)

create table tracker_live_last_month (
  client_nbr int not null, tracker_date date not null, submit_date date not null, live float not null, retainer float not null, bonus float not null
  , constraint pk_tracker_live_last_month primary key (client_nbr, tracker_date));  

/* example query for procedure */


declare @test date;
  set @test = '2014-11-18';
  --set @test = convert(date,getdate());
declare @LastDayOfLastMonth date;
  set @LastDayOfLastMonth  = dateadd(day,-datepart(day,@test),@test);
declare @FirstDayOfLastMonth date;  
  set @FirstDayOfLastMonth = dateadd(day,1-datepart(day,@LastDayOfLastMonth),@LastDayOfLastMonth);

  truncate table tracker_live_last_month;

  /* inserting the data with this combined query should be quicker than creating the table and then updating values */
  insert into tracker_live_last_month (client_nbr, tracker_date, submit_date, live, retainer, bonus)
    select tl.client_nbr, tl.tracker_date, ca.submit_date, ca.live, ca.retainer, ca.bonus
      from tracker_live tl
        cross apply (select top 1 i.submit_date, i.live, i.retainer, i.bonus 
          from tracker_live i 
          where i.client_nbr=tl.client_nbr 
            and i.tracker_date=tl.tracker_date
          order by i.submit_date desc) as ca
      where tl.submit_date between @FirstDayOfLastMonth and @LastDayofLastMonth
      group by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus
      order by tl.client_nbr, tl.tracker_date, ca.submit_date, ca.retainer, ca.live, ca.bonus;
Related Question