2

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

  • What version of sql server are you using ? In the latest versions, there are many advance functions that you can use. – Kin Shah Dec 8 '14 at 16:57
  • @Kin, using sql server 2008 r2 – smcfisher Dec 8 '14 at 20:08
  • @PaulWhite, correct. Gregorian calendar month. – smcfisher Dec 8 '14 at 22:51
1

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;
0

I believe something like this will do what you want:

DECLARE @Test DATE=GETUTCDATE()
SELECT @Test
-- compute the DATE for the last day of the previous month
DECLARE @LastDayOfLastMonth DATE=DATEADD(day,-DATEPART(Day,@Test),@Test)
-- dump out the DATE for the last day of the previous month
SELECT LastDayOfLastMonth=@LastDayOfLastMonth
-- dump out the first day of the previous month
SELECT FirstDayOfLastMonth=DATEADD(day,1-DATEPART(Day,@LastDayOfLastMonth),@LastDayOfLastMonth)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.