daily sales comparison - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

daily sales comparison

Author

Topic

kiwiroo
Starting Member

3 Posts

Posted - 2009-01-16 : 17:53:12

I need a little help in how to approach this one- management wants a comparison of all daily sales this month to the previous month, which I could do with grabbing the day/week/year and comparing the same day/wk/yr in the previous year.
The twist is they want to compare the first day of sales this year to the first day of sales last year, which doesn't always match up using the day/week number.
All I can think of is to query the first year sorted by date and assign a row number, do the same with the previous year and match the row numbers up.
Any input appreciated! Thx,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 23:53:22

something like:-


SELECT DATEADD(dd,DATEDIFF(dd,0, datefield),0) AS Date,
SUM(CASE WHEN YEAR(datefield)=YEAR(GETDATE()) THEN yoursalesqtyfield ELSE 0 END) AS CurrentYearSales,
SUM(CASE WHEN YEAR(datefield)=YEAR(GETDATE())-1 THEN yoursalesqtyfield ELSE 0 END) AS PrevYearSales,

SUM(CASE WHEN YEAR(datefield)=YEAR(GETDATE())-1 THEN yoursalesqtyfield ELSE 0 END)*100.0/NULLIF(SUM(CASE WHEN YEAR(datefield)=YEAR(GETDATE()) THEN yoursalesqtyfield ELSE 0 END),0) 
 AS Percentchange
FROM YourTable
WHERE datefield>= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)
AND datefield< DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
GROUP BY DATEADD(dd,DATEDIFF(dd,0, datefield),0)

kiwiroo
Starting Member

3 Posts

Posted - 2009-01-19 : 11:17:43

Isn't it possible that the previous year date could land on a weekend or holiday, and might not be the first workday of that month?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-19 : 12:17:06

Could you give sample input data....and matching expected results?
See the FAQ on how to post useful questions, to get useful answers

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-19 : 12:21:25

It sounds like your real issue is not how to do this in SQL.

It sounds like you actually need a good definition from the requestor of the periods of time that you need to compare. Once you have that, it should be easy enough to write a query.

CODO ERGO SUM

kiwiroo
Starting Member

3 Posts

Posted - 2009-01-19 : 12:38:57

Here is an example of what they want- If you compare work days in jan 09 to jan 08- our first work day in 09 was the 5th(second week on a monday) because of new years. In 2008 the first workday of the month was the 2nd(first week on a wednesday).
So what they want is to start at the first workday of the month and compare from that, so the 5th in 09 compared to the 2nd in 08, 6th to the 3rd etc. This is difficult because you can't go back exactly one year and you can't just compare 1st week first day of 09 to 1st week first day of 08.
All I can think is to query out all the days sales from each month and assign an incrementing number, and then compare row 1 to row 1 etc. Thanks for you help on this-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources