Results keep starting from the start at the start

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

General SQL Server Forums

New to SQL Server Programming

Results keep starting from the start at the start

Author

Topic

Tom O Sullivan
Starting Member

1 Post

Posted - 2012-06-08 : 10:47:44

Hi I am using mySql and i need a little help with my code, its nearly right but just one little thing going wrong. I have 3 columns in my results. 'entries_per_week' which gets the new entries each week so there could be 5 one week 3 the next 8 the next and so on. 'total_entries' which keeps track of the total entries each week so it keeps adding up the 'entries_per_week' column. Then the '6WK_total' which adds up the previous 6 weeks total. So basically adds up 6 of the 'previous_total_entries'. I nearly have it working but at the start of a new year the 6WK_total starts new from that year instead of adding up the entries from the previous year. Heres my code.



SELECT a.*
     , sum(b.total_entries) 6WK_total
  FROM 
     ( SELECT x.*
            , SUM(y.entries_per_week) total_entries
         FROM
            ( SELECT YEARWEEK(date_created) week_created
                   , COUNT(*) entries_per_week
FROM entries
               GROUP 
                  BY week_created
            ) x
         JOIN   
            ( SELECT YEARWEEK(date_created) week_created
                     , COUNT(*) entries_per_week
FROM entries
                 GROUP 
                    BY week_created
            ) y
           ON y.week_created <= x.week_created

        GROUP
           BY x.week_created
     ) a

  JOIN
     ( SELECT x.*
            , SUM(y.entries_per_week) total_entries
         FROM
            ( SELECT  YEARWEEK(date_created) week_created
                   , COUNT(*) entries_per_week
FROM entries
               GROUP 
                  BY week_created
            ) x
         JOIN   
            ( SELECT YEARWEEK(date_created) week_created
                     , COUNT(*) entries_per_week
FROM entries
                 GROUP 
                    BY week_created
            ) y
           ON y.week_created <= x.week_created

        GROUP
           BY x.week_created
     ) b
    ON b.week_created BETWEEN a.week_created - (6 - 1) AND a.week_created

 GROUP
    BY week_created;

And what happens, here are some results, notice the bold result, my desired results are below in the other table. It should be 1056 like the desired results table below not 248


+--------------+-------+-----------------------+-----------+
| week_created | total | total_entries         | 6WK_total |
+--------------+-------+-----------------------+-----------+
|       201149 |     49| 131                   |       243 |
|       201150 |     37| 168                   |       411 |
|       201151 |     37| 205                   |       614 |
|       201152 |     18| 223                   |       837 |
|       201201 |     25| 248                   |       248 |
|       201202 |     33| 281                   |       529 |
|       201203 |     66| 347                   |       876 |
|       201204 |     70| 417                   |       1293|
|       201205 |     61| 478                   |       1771|
|       201206 |     88| 566                   |       2337|
|       201207 |     72| 638                   |       2727|
|       201208 |     72| 710                   |       3156|
|       201209 |     67| 777                   |       4030|
+--------------+-------+-----------------------+-----------+

Desired results, notice at week_create '201206' the 6 week total would be the 6 results previous in the total_entries so it would be 566 + 478 + 417 + 347 + 281 + 248 = 2337.


+--------------+-------+-----------------------+-----------+
| week_created | total | total_entries         | 6WK_total |
+--------------+-------+-----------------------+-----------+
|       201149 |     49| 131                   |       243 |
|       201150 |     37| 168                   |       411 |
|       201151 |     37| 205                   |       614 |
|       201152 |     18| 223                   |       837 |
|       201201 |     25| 248                   |       1056|
|       201202 |     33| 281                   |       1256|
|       201203 |     66| 347                   |       1472|
|       201204 |     70| 417                   |       1127|
|       201205 |     61| 478                   |       1989|
|       201206 |     88| 566                   |       2337|
|       201207 |     72| 638                   |       2727|
|       201208 |     72| 710                   |       3156|
|       201209 |     67| 777                   |       4030|
+--------------+-------+-----------------------+-----------+

Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 11:51:44

this is not MYSQL forum. This is MS SQL Server forum and we deal with only t-sql. so please post in relevant forums like www.dbforums.com for more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources