?
Solved

Sales Report for Same day last year

Posted on 2007-10-20
14
Medium Priority
?
698 Views
Last Modified: 2008-01-09
Hi There
I am trying to write a query to find the EQUIVALENT (Not the Same) day of the week as last year.
IE today is Sunday the 21st October 2007.  The equivalent Sunday last year is 22nd October 2006.

The way I thought I could tackle this is by comparing at the day of the week and also the week of the year.

This is what I have so far:
/* Find Day of Week
SELECT DATEPART(dw, GETDATE())
= 1 (Sunday)
*/

/* Find Week of year
SELECT DATEPART(wk, GETDATE())
= 43 (The forty third week)

How do I convert these date parts into a date last year?
Ie the Sunday of the 43rd Week of last year.
0
Comment
Question by:omfgwtflolbbq
  • 9
  • 4
14 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 20116961
Hi there,

select dateadd(d,@TheDay,dateadd(wk,@TheWeek,@TheYear + '0101'))

good luck =)
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20116976
- TheYear is a string, but you can use cast(@TheYear as varchar(4))

- Note that 'equivalent' is a personal point of view, it can be day number, week and day of week, month and day of month, etc. All has pros and cons, like, the first days of the year sometimes belong to the previous year's week, depending on the parse method you use, so the number of weeks may be different for some years for some parsing methods.

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20116979
ah, a correction,

select dateadd(d,@TheDay - 1,dateadd(wk - 1,@TheWeek,@TheYear + '0101'))

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Expert Comment

by:MrRobot
ID: 20117000
oops wrong place, sorry for this =)

select dateadd(d,@TheDay - 1,dateadd(wk,@TheWeek - 1,@TheYear + '0101'))

0
 

Author Comment

by:omfgwtflolbbq
ID: 20117067
Thanks for your comment MrRobot
I am not sure if I am using your example right.  
My example date is today (Sunday 21st October 2007).  Or the 1st day of the week of the 43rd Week in 2007.
Using your query and replacing the variables where I think they should go eg:

select dateadd(d,1 - 1,dateadd(wk,43 - 1,2007 + '0101'))

My result is:
1906-07-31 00:00:00.000

Can you please confirm this?

Thanks
Tyson


 
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20117186
Hi,

2007 should be a string as I mentioned,

2007 + '0101'
will give you
2007 + 101 = 2108

so you should use,

select dateadd(d,@TheDay - 1,dateadd(wk,@TheWeek - 1,cast(@TheYear as varchar(4)) + '0101'))


0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20117208
but again, don't forget you'll have to solve several things in your business logic, there may be no equivalent day for another year, like a year can have only a 'saturday'  as its first week depending on your definition of 'week'. or otherwise, you'll have missing days from the last week, also the number of weeks may differ for two given years. you may need to review your logic.

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20117243
fix for regional settings (the first day of week);

select dateadd(d,@TheDay - 1,dateadd(wk,@TheWeek - 1,dateadd(d,-datepart(dw,cast(@TheYear as varchar(4)) + '0101')+1,cast(@TheYear as varchar(4)) + '0101')))
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 20117495
Maybe this will help.
I wrote a app for a client that needed to compare last year week to this year week.  Not exact week but comparitave week.  It was a 'seasonal' market company that needed to compare, for instance, holiday weeks.  We created a table that included week ending dates and week number.  The app would allow the user to select Week 1 and the system would automatically fill in the year's week number values.
We then could accurately compare last years' week 22 with this years week 22.  We used this table extensively when printing reports, queries, etc.  We found this much faster and more client accurate by doing it this way.  

Scott C
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20118325
Hi Scott,

That's exactly what I say, weekly or even monthly results will be better, since some years can take 53 weeks and some can take 54. However this time day numbers matter, the formula I gave solves it, I just wanted to notice that it will always be missing some days, and the application needs to be aware of it.
0
 

Author Comment

by:omfgwtflolbbq
ID: 20123578
Hi Thanks to you both for your answers:

Mr Robot I am sorry that I do not quite know how to use your answer.

Using the Example date of 21/10/2007.  Is the Sunday of the 43rd Week.  I know this by using
SELECT DATEPART(wk, '2007-10-21')
Result = 43

Using your answer and replacing the Variables.

select
dateadd(d,21 - 1,dateadd(wk,43 - 1,dateadd(d,-datepart(dw,cast(2007 as varchar(4)) + '0101')+1,cast(2007 as varchar(4)) + '0101')))

I get the result of 2007-11-10.  Which is unfortunately not the 43rd Sunday of 2006.

When I use
SELECT DATEPART(wk, '2007-11-10')
Result = 45
(or the 45th Saturday of 2007)

I understand your concern.  But I am convinced that comparing the equivalent day of the same numbered week is suitable for my purposes.

A quick visual comparison of 'equivalent' Sundays in previous years gives me a result of the 43rd Sunday for every year.

SELECT DATEPART(wk, '2007-10-21')
Result = 43
SELECT DATEPART(wk, '2006-10-22')
Result = 43
-- SELECT DATEPART(wk, '2005-10-16')
Result = 43
-- SELECT DATEPART(wk, '2004-10-23')
Result = 43
-- SELECT DATEPART(wk, '2003-10-19')
Result = 43
-- SELECT DATEPART(wk, '2002-10-20')
Result = 43
-- SELECT DATEPART(wk, '2001-10-21')
Result = 43
-- SELECT DATEPART(wk, '2000-10-15')
Result = 43

To answer this question I am looking for a SQL query:
That finds the equivalent date of the previous year by comparing the week number.
IE the example date is 21/10/2007.
21/10/2007 is a Sunday and is the 43rd Sunday of 2007.
SELECT DATEPART(wk, '2007-10-21')
Result = 43

The correct Result to the Example date is 22/10/2006.
22/10/2006 is a Sunday and is the 43rd Sunday of 2006

SELECT DATEPART(wk, '2006-10-22')
Result = 43

I understand this is a pretty hard task.  Good Luck.  And thanks for the advice.
0
 

Author Comment

by:omfgwtflolbbq
ID: 20123648
I guess another way I could get this answered is.

How do I convert the DATEPARTs I get into an actual date.

IE
Day = 1
Week = 43
Year = (2007-1)

Is there a Way to CAST these together to get 22/10/2006 ?
0
 

Author Comment

by:omfgwtflolbbq
ID: 20141319
In the end I answered my own question.

/* Sales Target based on equivalent day of the previous year */
SELECT CAST(FLOOR( CAST( docket_date AS FLOAT ) ) AS DATETIME), SUM(total_inc)
FROM DOCKET
WHERE   DATEPART(dw, docket_date) = DATEPART(dw, GETDATE())
      AND DATEPART(wk, docket_date) = DATEPART(wk, GETDATE())
      AND DATEPART(yyyy, docket_date) = (DATEPART(yyyy, GETDATE()) -1)
      AND [transaction] = 'SA'
GROUP BY CAST(FLOOR( CAST( docket_date AS FLOAT ) ) AS DATETIME)

This query is "most of the time" accurate.  Which is good enough for my application.
0
 
LVL 8

Accepted Solution

by:
MrRobot earned 1500 total points
ID: 20145929
>How do I convert the DATEPARTs I get into an actual date.
>
>IE
>Day = 1
>Week = 43
>Year = (2007-1)
>
>Is there a Way to CAST these together to get 22/10/2006 ?

Hi Tyson,

Sorry for the latency. The formula I gave does this. Found it, in your last try, you used 21, instead of 1, the day of the week.

Uh, I would suggest using the where clause, right, in a typical situation it's the best way to do it. But I figured you needed to convert the week-day-year data, which would also work with no table data, with missing rows, and with no full-index-scan performance hits on bigger tables, as you said,

> How do I convert these date parts into a date last year? Ie the Sunday of the 43rd Week of last year.

Btw, instad of replacing the variables this will be easier for testing


declare
      @TheDay int,
      @TheWeek int,
      @TheYear int

select
      @TheDay = 1,
      @TheWeek = 43,
      @TheYear = 2007

select dateadd(d,@TheDay - 1,dateadd(wk,@TheWeek - 1,dateadd(d,-datepart(dw,cast(@TheYear as varchar(4)) + '0101')+1,cast(@TheYear as varchar(4)) + '0101')))

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question