CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mateos
mateos

Reputation: 59

fix extraction dates

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

Upvotes: 0

Views: 76

Answers (1)

Liesel
Liesel

Reputation: 2979

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

Upvotes: 2

Related Questions