Running Totals - SQL Server Forums

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

Running Totals

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-11 : 09:10:01

Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.

Declare @Brand as varchar(10)
DECLARE @StartDate datetime
Declare @EndDAte as Datetime

Set @Brand = 'business'
SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)

Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales
From

/**********Finds projection per day****************/
(Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS
From
(Select StoreID, Projection as Projection,
Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth
From Reporting.dbo.RetailSalesComparison_ProjectionsView
Where StoreID between 12000 and 12999
)ttTemp

Right Join

(SELECT DATEADD(dd,number,@StartDate) as DAYS
FROM
(
select number from master..spt_values
WHERE type='p'
union all
select number+256 from master..spt_values
WHERE type='p'
) as s
where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection

Left Join

(Select Date, StoreID, Sum(Sales) as Sales
From Reporting.dbo.RetailSales_byStore_byDay
Group By Date, StoreID)ttSales
on ttProjection.StoreID = ttSales.StoreID
and ttProjection.DAYS = ttSales.Date
Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID
Where Month(Days) = Month(getdate())
Order By Days, ttProjection.StoreID

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-06-11 : 09:43:27

Is this what you looking for.

Declare @Table as Table (CustomerNO Char(3),MyDate Datetime, PurchaseValue  Int)

set dateformat dmy

INSERT INTO @Table  Values('A','1/1/2008',234)
INSERT INTO @Table  Values('A','1/2/2008',1000)
INSERT INTO @Table  Values('A','1/7/2008',768)
INSERT INTO @Table  Values('B','1/1/2008',553)
INSERT INTO @Table  Values('B','1/3/2008',1900)
INSERT INTO @Table Values('B','1/10/2008',120)
INSERT INTO @Table Values('C','2/3/2008',10)

SELECT T1.*
	,(SELECT SUM(Tsub.PurchaseValue) 
      FROM @table TSub 
      WHERE TSub.CustomerNo = T1.CustomerNo AND Tsub.MyDate <=T1.MyDate) RSum
FROM @Table T1

Dallr

gavakie
Posting Yak Master

221 Posts

Posted - 2008-06-11 : 12:34:04

I tried something like this but on the Cross Join its not liking StoreID to StoreID so is giving me the wrong totals. over twice as much.

Select A.StoreID, A.StoreName, A.DailyProjection, A.DAYS, Sum(B.Sales) as RunningTotal,
(CAST(Day(A.DAYS) as INT) * A.DailyProjection) as RunningDailyProjection
From #ttSales A
CROSS Join #ttSales B
Where (Day(B.DAYS) <= Day(A.DAYS))
Group By A.StoreID, A.StoreName, A.DailyProjection, A.DAYS, (CAST(Day(A.DAYS) as INT) * A.DailyProjection)
Order By A.StoreID, A.DAYS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 12:38:06

quote:
Originally posted by gavakie

I tried something like this but on the Cross Join its not liking StoreID to StoreID so is giving me the wrong totals. over twice as much.

Select A.StoreID, A.StoreName, A.DailyProjection, A.DAYS, Sum(B.Sales) as RunningTotal,
(CAST(Day(A.DAYS) as INT) * A.DailyProjection) as RunningDailyProjection
From #ttSales A
CROSS Join #ttSales B
Where (Day(B.DAYS) <= Day(A.DAYS))
Group By A.StoreID, A.StoreName, A.DailyProjection, A.DAYS, (CAST(Day(A.DAYS) as INT) * A.DailyProjection)
Order By A.StoreID, A.DAYS

Did you try the solution provided?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources