hydev
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
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
Try this:
SELECT Top 52 T1.Date, AVG(T2.NetSales)
FROM Table1 T1 LEFT JOIN Table1 T2
ON DateDiff(Year,T2.Date,T1.D ate) BETWEEN 0 AND 1
WHERE DateDiff(Year,T1.Date,@Rep Date) 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.
SELECT Top 52 T1.Date, AVG(T2.NetSales)
FROM Table1 T1 LEFT JOIN Table1 T2
ON DateDiff(Year,T2.Date,T1.D
WHERE DateDiff(Year,T1.Date,@Rep
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
Eg DateDiff(Week,T2.Date, T1.Date) BETWEEN 0 and 51
Using OLAP is a good solution too.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks great solution!
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