Link to home
Start Free TrialLog in
Avatar of hydev
hydevFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Challenge, not sure if it can be done?

Hi,

I have been requested by my boss to create a report and Im not sure if I can do the SQL to genrerate the data.  I don't really want to 'launder' the data through excel etc..

I wont give you the exact layout of the tables as it is beyond the scope but an example will suffice.

Date,     NetSales
8/4/05       10
1/4/05       20
24/4/05     15
17/4/05     16
etc..          etc..

There is data going on by week for 2 years.  What my boss wants is a 52 week moving average. So for a given starting date the database will calculate the average for Week 8/4/05 to 8/4/04, Week 1/4/05 to 1/4/04, 24/4/05 to 24/4/04 and so on.  That way you get 52 figures that represent a trend over the last 52 weeks.

How can I do that in SQL?

Feel free to ask me more questions, any help is always appreciated!

Mike
Avatar of xiong8086
xiong8086

here the week is exactly the week in the calendar?

I think you need to create a table "tbl_weekday" to record the date, weekday,weekNo.

Date                    weekday    weekNo
01/01/2005          Sat             1
01/02/2005          Sun             1
....                       ....              ..


you need to record the all date range concerned by you. then just use this table to match with your original data table. you can use a store procedure to generate the date for your tbl_weekday.

so get it? if so, your job will become extremely easier.

cheers,
xiong
Try this:

SELECT Top 52 T1.Date, AVG(T2.NetSales)
FROM Table1 T1 LEFT JOIN Table1 T2
    ON DateDiff(Year,T2.Date,T1.Date) BETWEEN 0 AND 1
WHERE DateDiff(Year,T1.Date,@RepDate) BETWEEN 0 AND 1
GROUP BY T1.Date
ORDER BY T1.Date DESC

Where @RepDate is the date entered. This should give you the moving average for the 52 weeks prior to RepDate.
Actually, I just realised you will need to use weeks not years in the DateDiff.
Eg DateDiff(Week,T2.Date, T1.Date) BETWEEN 0 and 51
Avatar of Vitor Montalvão
Using OLAP is a good solution too.
Avatar of hydev

ASKER

Thanks for all the input.

PPSUser,

Thanks for your code but I am slightly confused, is Table1 the table containing all the dates and Table2 the table containing the data? I think I am confused by how you are joining the tables.

Mike
ASKER CERTIFIED SOLUTION
Avatar of PSSUser
PSSUser
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hydev

ASKER

Thanks great solution!