• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

Rolling moving annual total

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
DanielGra
Asked:
DanielGra
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
Patrick MatthewsCommented:
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
 
GRayLCommented:
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
 
GRayLCommented:
Anyway:

SELECT * FROM tblTotSales WHERE TheDate BETWEEN DateAdd("m",-12, Date()) AND Date()
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DanielGraAuthor Commented:
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
 
DanielGraAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
DanielGraAuthor Commented:
Hey that worked - thanks everyone for your help - I learned something new with solving this  problem
0
 
Patrick MatthewsCommented:
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
 
SharathData EngineerCommented:
You are welcome MP. even i learned that method at EE only. :)
0
 
yoh268Commented:
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
 
yoh268Commented:
oop there should be a date heading

date | product | total cases | price
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now