CODEWITHSUNDEEP
sqlt-sql
Semih Ural
Semih Ural

Reputation: 47

How do you compare the same day at same week for current year and previous year?

Most of the retailers, use a merch calendar for their sales report that can help them to find a same day at same week for current year and previous year.

For example today is '2016-10-10' Monday. For previous year; 2015, for same week, Monday is '2015-10-12'.

How can we catch this via query at SQL and show them in one table ?

Upvotes: 3

Views: 3723

Answers (2)

Twinkles
Twinkles

Reputation: 1994

SELECT t1.data AS data1, t1.date AS date1,
  t2.data AS data2, t2.date AS date2
FROM table t1
LEFT OUTER JOIN table t2 
  ON ( DATEPART(week,t1.date) = DATEPART(week,t2.date)
     AND DATEPART(weekday,t1.date) = DATEPART(weekday,t2.date) )
WHERE DATEPART(year,t1.date) = 2016 
      AND DATEPART(year,t2.date) = 2015

If that query is too slow, you can assist it by using computed columns:

ALTER TABLE table ADD year AS DATEPART(year,date) PERSISTED;
ALTER TABLE table ADD week AS DATEPART(week,date) PERSISTED;
ALTER TABLE table ADD weekday AS DATEPART(weekday,date) PERSISTED;

CREATE INDEX idx_datecalc ON table (year, week, weekday);

And now adapt above query:

[...]
LEFT OUTER JOIN table t2 
  ON ( t1.week = t2.week) ...etc.

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

You have all date functions to do it with long way:

Or shortcut just substracting 52 weeks it it is enough for you:

DATEADD(week, -52, some_Date)

Upvotes: 3

Related Questions