February 5, 2008 at 10:08 am
Hi the below code gives me a running total for sales. I'd like to go one step futher and add in a period to date into the code. 4 weeks are = 1 period so week 1,2,3,4 are all in period 1. weeks 5,6,7,8 are in period 2.
So for my period sales field like like to keep the running total from week 1 to 4 and then for week 5 it would be back to only show week 5 data..
if i had week 5 and 6 then i would show the sum on only week 5 & 6
--In the table Sales_data i do have the period column
Any help would be great thanks..
SELECT s.[Branch],
s.[Region],
s.[week_no],
s.[year],
s.[sales_weekly],
tmp.[RunningTotal]
FROM Sales_Data s
CROSS APPLY (SELECT SUM([sales_weekly]) AS RunningTotal from Sales_Data WHERE [Branch]=s.[Branch]
AND [Region]=s.[Region]
AND [year]=s.[year]
AND week_no<=s.[week_no])tmp
February 5, 2008 at 12:35 pm
There was a very good article recently on the front page of this site about running totals, and being able to split them into running sub-totals. Check that. It will probably solve what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 5, 2008 at 1:07 pm
Fully agree with GSquared. The correlated sub-query won't scale well, so if your data gets big it will start to choke on performance.
That being said - you want to revise your join criteria to get the right result:
SELECT s.[Branch],
s.[Region],
s.[week_no],
s.[year],
s.[sales_weekly],
tmp.[RunningTotal]
FROM Sales_Data s
CROSS APPLY (
SELECT SUM([sales_weekly]) AS RunningTotal
from Sales_Data s1
WHERE s1.[Branch]=s.[Branch]
AND s1.[Region]=s.[Region]
AND s1.[year]=s.[year]
--modifications start here
AND s1.week_no/5=s.[week_no]/5
and s1.week_no%5<=s.[week_no]%5
--done with modifications
) tmp
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply