SQL Challenge, not sure if it can be done?

Posted on 2005-05-05
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
    LVL 3

    Expert Comment

    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

    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

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

    Expert Comment

    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
    LVL 44

    Expert Comment

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

    Author Comment

    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


    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

    Thanks great solution!

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now