CODEWITHSUNDEEP
sqlsql-server
Mr 2017
Mr 2017

Reputation: 113

Dynamic date selection

I've got some weekly data spanning 3 years and I've written code which extracts the last 104 weeks of data (the data goes up to 26th Dec 2014, so I've selected everything from 2nd Jan 2013).

Do you know how I can amend the code (below), so that it gets the last two years' worth of data regardless of when the query is run? eg if the data is run next week, and data is enter code here available up to last week, I'd want it to get the last 104 weeks of data, based on last week.

If you know, could you please modify the code below?

I'm using Sql Server Management Studio 2014. 

select 
    pz.PriceZoneID,
    pz.Name,
    CAST (ash.Date as date) as Date,    
    format(sum (Sales), '#,###') as SalesByZone 
from AggregatedSalesHistory as ash  
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSid
join PriceZone as pz on pz.PriceZoneID = ash.PriceZoneID
WHERE ash.date >= '2013-01-02' and ash.date<= '2014-12-24'
group by pz.PriceZoneID,
        pz.Name,
        ash.Date
order by PriceZoneID,
        ash.Date

Upvotes: 1

Views: 109

Answers (2)

MEdwin
MEdwin

Reputation: 2960

See this modification: if you declare the date as DECLARE @DATE DATE= '2013-01-02' Just take 2 years away from the @date to get the last 2 years data. 

   WHERE 
    ash.date BETWEEN  dateadd(year, -2, @DATE) and @DATE

Based on your reqest if you want (

I'd want it to get the last 104 weeks of data, based on last week

).

You can do :

  WHERE 
        ash.date BETWEEN  dateadd(WEEK, -104, @DATE) and @DATE

see the full code below

DECLARE @DATE DATE= '2013-01-02'



select 
    pz.PriceZoneID,
    pz.Name,
    CAST (ash.Date as date) as Date,    
    format(sum (Sales), '#,###') as SalesByZone 
from AggregatedSalesHistory as ash  
join v_EnterpriseStructure as es on es.ProductSID = ash.ProductSid
join PriceZone as pz on pz.PriceZoneID = ash.PriceZoneID
WHERE 
ash.date BETWEEN  dateadd(year, -2, @DATE) and @DATE
group by pz.PriceZoneID,
        pz.Name,
        ash.Date
order by PriceZoneID,
        ash.Date

Upvotes: 2

Milney
Milney

Reputation: 6417

Depends how you define two years but yes, just change the where, for 2 years to date, like;

WHERE ash.date >= dateadd(year, -2, getdate()) and ash.date <= getdate()

If you want to get 2 full years, from say the start of the year it would be more like;

WHERE ash.date >= DATEADD(yy, DATEDIFF(yy, 0, DATEADD(YEAR, -2, GETDATE())), 0)  and ash.date <= DATEADD(yy, DATEDIFF(yy, 0, DATEADD(YEAR, -1 ,GETDATE())) + 1, -1)

Upvotes: 3

Related Questions