[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Challenge, not sure if it can be done?

Posted on 2005-05-05
Medium Priority
Last Modified: 2010-03-19

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!

Question by:hydev

Expert Comment

ID: 13933711
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.

LVL 10

Expert Comment

ID: 13933715
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

Where @RepDate is the date entered. This should give you the moving average for the 52 weeks prior to RepDate.
LVL 10

Expert Comment

ID: 13933730
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 53

Expert Comment

by:Vitor Montalvão
ID: 13934530
Using OLAP is a good solution too.

Author Comment

ID: 13937389
Thanks for all the input.


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.

LVL 10

Accepted Solution

PSSUser earned 2000 total points
ID: 13937592

I only use table1. It is joined to itself. The references T1 and T2 are 2 alias to the same table. The query is acting like it has 2 identical copies of table1.

Basically T1 is used to return the last week of the 52 weeks in the rolling total.
The "ON" link tells the query to return all records from T2 that fall in the 52 weeks prior to the week in T1.

If you where to change this to show the detail instead of the average, and add in T2.Date you would see (for the rows listed in your sample:

T1.Date     T2.Date     T2.NetSales
8/4/05       8/4/05       10
8/4/05       1/4/05       20
8/4/05       24/3/05     15
8/4/05       17/3/05     16
1/4/05       1/4/05       20
1/4/05       24/3/05     15
1/4/05       17/3/05     16
24/3/05      24/3/05     15
24/3/05      17/3/05     16
17/3/05      17/3/05     16

As you can see week 8/4/05, although it is only in the table once, it is repeated once for each record that falls in the 52 weeks prior to it. This is a kind of controlled cartesian product effect. Normally cartesians are caused by missing required fields from the join statement. In this case we have chosen not to put a precise link (T1.Date=T2.Date) in the join code, so that we can get the required 52 weeks we wish to report on.

Each row in T1 will be shown up to 52 times, once with each record from T2 that falls in the previous weeks. Just because T1 and T2 are referencing the same table doesn't mean they reference the same row at the same time. Like I said previously, the query is effectively working with 2 seperate tables which just happend to be identical.

Author Comment

ID: 13943137
Thanks great solution!

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

873 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