fix extraction dates

Question

I have this SQL query that extract data depending on a period of time that the user can chose , the user can change dates manually with the dates that he needs , is it possible to fix this period of time like when we extract we will have data of last three months to fix it for the last three months , this is the query if someone can help me please :

SELECT
      ROW_NUMBER() OVER (partition by ptf_id ORDER BY sum(isnull([pnr_count],0)) desc)  as id
      ,[coresitecode]
      ,[sitecode]
      ,ptf_id 
      ,sum(isnull([pnr_count],0)) TOTAL_PNRs 
      ,sum(isnull([child_pnr_count],0)) TOTAL_child_PNRs into #pnr_by_site_and_platform
  FROM 
    [SWAT_V2_PLL].[rpt].[PNR_per_site_daily] pnr
    inner join ctrl.PTF_Platform ptf on pnr.ptf_id = ptf.uid
  where 
    DAY_id >= 20150701  -- time period you need to consider YYYYMMDD
    and DAY_id <= 20150901  -- end of PERIOD
    and ptf_label in ('AETMEU1EUR', 'AETMEU2EUR', 'AETMAPASIA', 'AETMUSAMER') 
  group by 
    [coresitecode]
     ,[sitecode]
     ,ptf_id
  order by    
    TOTAL_PNRs desc

-- ordered PNRs ....   
select ptf_id, id , coresitecode, sitecode, TOTAL_PNRs
 from  #pnr_by_site_and_platform  order by ptf_id, TOTAL_PNRs desc

Assuming `DAY_id` is an `INT` then something like `DAY_id BETWEEN CONVERT(INT,CONVERT(CHAR(8), DATEADD(MONTH, -3, GETDATE()), 112)) AND CONVERT(INT,CONVERT(CHAR(8), GETDATE(), 112))` — Liesel, Jul 18 '16 at 08:46
Yes it is an INT , and thank you for the help it works , but if i want to cahnge the periode should i change only the ,-3 , ? — mateos, Jul 18 '16 at 09:08
@mateos - Exactly you are right, you need to change the argument (where the -3 is there) to change the period — PowerStar, Jul 18 '16 at 12:03

score 2 · Accepted Answer · edited May 23 '17 at 10:28

Further to my quick comment

...DAY_id BETWEEN 
     CONVERT(INT,CONVERT(CHAR(8), DATEADD(MONTH, -3, GETDATE()), 112)) AND
     CONVERT(INT,CONVERT(CHAR(8), GETDATE(), 112))

GETDATE() is today, DATEADD is adding -3 months (in other words subtracting three months from today. CONVERT(CHAR(8),...,112) is converting toYYYYMMDDand finallyCONVERT(INT,....)` is converting the string to an integer.

Convert documentation: https://msdn.microsoft.com/en-AU/library/ms187928.aspx

This question has some more details on calculating the first/last days of previous week/month etc.

How to get first and last day of previous month (with timestamp) in SQL Server

fix extraction dates

1 Answers1