July 21, 2011 at 4:30 pm
I am trying to get the running total for a set of data broken out by location. I am struggling to get it to only provide a running total for each location. Included is my code and a sample of the results. Any help would be greatly appreciated!
(
@STARTDATE smalldatetime = NULL,
@ENDDATE smalldatetime= NULL,
@REPORTTYPE varchar(50) = NULL,
@REPORTON varchar(50) = NULL
)
AS
DECLARE @Region varchar(50)
DECLARE @District varchar(50)
DECLARE @Unit varchar(50)
DECLARE @All int
--Filters for District, Region, Unit
IF(UPPER(@ReportType) = 'REGION')
SET @Region = @Reporton
IF(UPPER(@ReportType) = 'DISTRICT')
SET @District = @Reporton
IF(UPPER(@ReportType) = 'UNIT')
SET @Unit = @Reporton
IF(UPPER(@ReportType) = 'ALL')
SET @All = 1
CREATE TABLE #Sales (UnitID int, SalesDate smalldatetime,E1 money, MTD1 money, E2 money, MTD2 money, E3 money, MTD3 money, E4 money,
MTD4 money, E5 money, MTD5 money, E6 numeric, MTD6 numeric, E7 numeric, MTD7 numeric,E8 numeric, MTD8 numeric,
E9 numeric, MTD9 numeric,E10 numeric, MTD10 numeric,E11 numeric, MTD11 numeric,E12 numeric, MTD12 numeric,E13 numeric, MTD13 numeric,
E14 numeric, MTD14 numeric, E15 numeric, MTD15 numeric, E16 numeric, MTD16 numeric)
DECLARE @UnitID int,
@SalesDate smalldatetime,
@E1 money,
@E2 money,
@E3 money,
@E4 money,
@E5 money,
@E6 numeric,
@E7 numeric,
@E8 numeric,
@E9 numeric,
@E10 numeric,
@E11 numeric,
@E12 numeric,
@E13 numeric,
@E14 numeric,
@E15 numeric,
@E16 numeric,
@MTD1 money,
@MTD2 money,
@MTD3 money,
@MTD4 money,
@MTD5 money,
@MTD6 numeric,
@MTD7 numeric,
@MTD8 numeric,
@MTD9 numeric,
@MTD10 numeric,
@MTD11 numeric,
@MTD12 numeric,
@MTD13 numeric,
@MTD14 numeric,
@MTD15 numeric,
@MTD16 numeric
SET @MTD1 = 0
SET @MTD2 = 0
SET @MTD3 = 0
SET @MTD4 = 0
SET @MTD5 = 0
SET @MTD6 = 0
SET @MTD7 = 0
SET @MTD8 = 0
SET @MTD9 = 0
SET @MTD10 = 0
SET @MTD11 = 0
SET @MTD12 = 0
SET @MTD13 = 0
SET @MTD14 = 0
SET @MTD15 = 0
SET @MTD16 = 0
DECLARE rt_cursor CURSOR
FOR
SELECT UNITNUM,SalesDate,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 1 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD1,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 2 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD2,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 3 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD3,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 4 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD4,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 5 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD5,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 6 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD6,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 7 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD7,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 8 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD8,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 9 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD9,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 10 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD10,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 11 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD11,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 12 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD12,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 13 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD13,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 14 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD14,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 15 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD15,
SUM(CASE dbo.tblGLRawData.ElementID WHEN 16 THEN dbo.tblGLRawData.Amount ELSE 0 END) AS MTD16
FROM dbo.tblGLRawData
INNER JOIN
dbo.Dim_Unit ON dbo.tblGLRawData.UNITNUM = dbo.Dim_Unit.Unit_Number
WHERE Unit_Number = CASE WHEN @REPORTTYPE='unit'
THEN COALESCE(@REPORTON,Unit_Number)
ELSE Unit_Number END
AND Unit_Region = CASE WHEN @REPORTTYPE='region'
THEN COALESCE(@REPORTON,Unit_Region)
ELSE Unit_Region END
AND Unit_District = CASE WHEN @REPORTTYPE='district'
THEN COALESCE(@REPORTON,Unit_District)
ELSE Unit_District END
GROUP BY UNITNUM, SalesDate
HAVING (SalesDate BETWEEN @StartDate and @EndDate)
ORDER BY UNITNUM, SalesDate
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @UnitID,@SalesDate,@E1,@E2,@E3,@E4,@E5,@E6,@E7,@E8,@E9,@E10,@E11,@E12,@E13,@E14,@E15,@E16
WHILE @@FETCH_STATUS = 0
BEGIN
SET @MTD1 = @MTD1 + @E1
SET @MTD2 = @MTD2 + @E2
SET @MTD3 = @MTD3 + @E3
SET @MTD4 = @MTD4 + @E4
SET @MTD5 = @MTD5 + @E5
SET @MTD6 = @MTD6 + @E6
SET @MTD7 = @MTD7 + @E7
SET @MTD8 = @MTD8 + @E8
SET @MTD9 = @MTD9 + @E9
SET @MTD10 = @MTD10 + @E10
SET @MTD11 = @MTD11 + @E11
SET @MTD12 = @MTD12 + @E12
SET @MTD13 = @MTD13 + @E13
SET @MTD14 = @MTD14 + @E14
SET @MTD15 = @MTD15 + @E15
SET @MTD16 = @MTD16 + @E16
INSERT #Sales VALUES (@UnitID,@Salesdate,@E1,@MTD1,@E2,@MTD2,@E3,@MTD3,@E4,@MTD4,@E5,@MTD5,@E6,@MTD6,@E7,@MTD7,@E8,@MTD8,@E9,@MTD9,@E10,@MTD10,@E11,@MTD11,@E12,
@MTD12,@E13,@MTD13,@E14,@MTD14,@E15,@MTD15,@E16,@MTD16)
FETCH NEXT FROM rt_cursor INTO @UnitID,@SalesDate,@E1,@E2,@E3,@E4,@E5,@E6,@E7,@E8,@E9,@E10,@E11,@E12,@E13,@E14,@E15,@E16
END
CLOSE rt_cursor
DEALLOCATE rt_cursor
SELECT * FROM #Sales ORDER BY UnitID, SalesDate
DROP TABLE #Sales
You can see below that it continues the running total across all locations. I would like it to start a new running total based on the UnitID.
UnitID SalesDate E1 MTD1
1 5/1/2011 0:00$14,584.43$14,584.43
1 5/2/2011 0:00$12,825.57$27,410.00
1 5/3/2011 0:00$19,242.50$46,652.50
2 5/1/2011 0:00$17,595.66$64,248.16
2 5/2/2011 0:00$13,152.40$77,400.56
2 5/3/2011 0:00$20,475.80$97,876.36
3 5/1/2011 0:00$13,318.16$111,194.52
3 5/2/2011 0:00$8,912.16$120,106.68
3 5/3/2011 0:00$19,964.23$140,070.91
5 5/1/2011 0:00$12,690.43$152,761.34
5 5/2/2011 0:00$10,613.71$163,375.05
July 21, 2011 at 8:37 pm
Please read this article[/url] for how to perform running totals.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2011 at 8:23 am
Thanks for the help! That seems to work except when I get to the second column. Any ideas? Here is what I have.
SELECT @MTD1 = CASE
WHEN @CurUnitID = @PrevUnitID
THEN @MTD1 + @E1
ELSE @E1
END,
@PrevUnitID = @CurUnitID,
@MTD2 = CASE
WHEN @CurUnitID = @PrevUnitID
THEN @MTD2 + @E2
ELSE @E2
END,
@PrevUnitID = @CurUnitID
July 22, 2011 at 8:46 am
Sorry, I am an idiot. Thanks again for the help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply