Solved

Rolling moving annual total

Posted on 2009-03-29
12
884 Views
Last Modified: 2012-05-06
Hi Experts,

I'm trying to write a query that gives me a moving annual total of sales based on a table with the following data structure:

ItemCode      TheDate      NetDollarsInvoiced
KT100R      01-Jul-07      12598
KT100R      01-Aug-07      8306
KT100R      01-Sep-07      4765
KT100R      01-Oct-07      4746
KT100R      01-Nov-07      20131
KT100R      01-Dec-07      2942
KT100R      01-Jan-08      1409
KT100R      01-Feb-08      735
KT100R      01-Mar-08      1854
KT100R      01-Apr-08      1591
KT100R      01-May-08      4653
KT100R      01-Jun-08      2541
KT100R      01-Jul-08      3486
KT100R      01-Aug-08      1454
KT100R      01-Sep-08      733
KT100R      01-Oct-08      682
KT100R      01-Nov-08      902
KT100R      01-Dec-08      1282
KT100R      01-Jan-09      1638
KT100R      01-Feb-09      5057

I want the query result to be as follows:
ItemCode      TheDate      NetDollarsInvoiced Last 12 months
KT100R      01-Jun-08      66271
KT100R      01-Jul-08      57159
KT100R      01-Aug-08      50307
KT100R      01-Sep-08      46275
KT100R      01-Oct-08      42211
KT100R      01-Nov-08      22982
KT100R      01-Dec-08      21322
KT100R      01-Jan-09      21551
KT100R      01-Feb-09      25873

The issue is that there are 100's of item codes so a dlookup is out of the question (takes too long and inevitably crashes.

Is there another way to do this?

Suggestions gratefully received
0
Comment
Question by:DanielGra
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 total points
ID: 24015302
Try something like this

SELECT t1.ItemCode, t1.TheDate,
      (SELECT Sum(t2.NetDollarsInvoiced)
      FROM tblSales t2
      WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate) AS Last12Mo
FROM tblSales t1
ORDER BY t1.ItemCode, t1.TheDate


Replace instances of tblSales with your actual table name.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24015312
OK the month is March 09,  the last 12 months to my thinking go from March 08 to February 09.  Whether or not you include March 09 pales in comparison to: "What happened to April and May of 08?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24015317
Anyway:

SELECT * FROM tblTotSales WHERE TheDate BETWEEN DateAdd("m",-12, Date()) AND Date()
0
 

Author Comment

by:DanielGra
ID: 24015419
Thanks Guys,

I think MathewPatricks solution works (after running a quick check) - Although I haven't seen this approach before - looks like the quesry creates a sub table t2 that pulls out the correct months that are then sumed - correct?

I'm not sure that GRayL's solution will work because it will not pull a rolling month total on every line. It will just pull the last 12 months from today's date (ie it wont tell me the 12 months up to Dec '08)
0
 

Author Comment

by:DanielGra
ID: 24015536
Hi Mathews patrick:

I ran your code as follows:

SELECT t1.ItemCode, t1.TheDate, (SELECT Sum(t2.revenue)
      FROM tblLifeCycle t2
      WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate) AS Last12Mo
FROM tblLifeCycle AS t1
WHERE (((t1.ItemCode)="kt100r"))
ORDER BY t1.ItemCode, t1.TheDate;

And got the following results:

ItemCode      TheDate      Last12Mo
KT100R      1/07/2007      17383495
KT100R      1/08/2007      34983568
KT100R      1/09/2007      54913400
KT100R      1/10/2007      81593893
KT100R      1/11/2007      106965587
KT100R      1/12/2007      122771431
KT100R      1/01/2008      136334994
KT100R      1/02/2008      148210569
KT100R      1/03/2008      162925525
KT100R      1/04/2008      181239768
KT100R      1/05/2008      199465450
KT100R      1/06/2008      213609107
KT100R      1/07/2008      215489688
KT100R      1/08/2008      215871983
KT100R      1/09/2008      216424031
KT100R      1/10/2008      218917899
KT100R      1/11/2008      218472023
KT100R      1/12/2008      223527081
KT100R      1/01/2009      228470753
KT100R      1/02/2009      230579162

Which look to me like the sum of all item codes for the Rolling last 12 months - can it be tweaked to only sum for each item code?

Thanks
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24016108
try this
SELECT t1.ItemCode, 

       t1.TheDate, 

       (SELECT Sum(t2.revenue)

          FROM tblLifeCycle t2

         WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate

           AND t2.ItemCode = t1.ItemCode) AS Last12Mo

  FROM tblLifeCycle AS t1

 WHERE (((t1.ItemCode)="kt100r"))

 ORDER BY t1.ItemCode, t1.TheDate;

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 24016109
If you don't want the ItemCode filter in the main query, you can remove that.
SELECT t1.ItemCode, 

       t1.TheDate, 

       (SELECT Sum(t2.revenue)

          FROM tblLifeCycle t2

         WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate

           AND t2.ItemCode = t1.ItemCode) AS Last12Mo

  FROM tblLifeCycle AS t1

 ORDER BY t1.ItemCode, t1.TheDate;

Open in new window

0
 

Author Comment

by:DanielGra
ID: 24016186
Hey that worked - thanks everyone for your help - I learned something new with solving this  problem
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24017831
DanielGra,

You are correct in the interpretation--the subquery calculates the rolling sum.  Of course, I forgot to also
put the ItemCode criterion in the subquery, and that is why the sums were coming up wrong.  My thanks
to Sharath_123 for making that correction :)

Using subqueries is one of the tricks I learned hanging out here on Experts Exchange.

Regards,

Patrick
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24019601
You are welcome MP. even i learned that method at EE only. :)
0
 

Expert Comment

by:yoh268
ID: 25847892
hi guys... can you help me? what if i need to have a moving annual total for two or more data entry?

here's an example heading:

product | total cases | price

i need to get the running sum for 'price' and 'total case'

thanx
0
 

Expert Comment

by:yoh268
ID: 25847904
oop there should be a date heading

date | product | total cases | price
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 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

11 Experts available now in Live!

Get 1:1 Help Now