Running Total - Split By Acct

Question

Post reply

Running Total - Split By Acct

texpic

SSCertifiable

Points: 5885
More actions
December 23, 2011 at 12:54 am

#248715

This was done using Jeff Modem's stuff. I'd like to do two things:

#1 There are three AcctId in this example. Would like the running total for each individual AcctId.

#2 There is a valid code. I'd like to get a running total for non-valids only (valid = 0).

Any help would be appreciated, thanks!

CREATE TABLE ##TempRt

(AcctId numeric(18,0),

Com_date numeric(18,0),

Com_Id numeric(18,0),

Hrs numeric(18,2),

Valid int)

INSERT INTO ##TempRt

(AcctId, Com_date, Com_Id, Hrs, Valid)

SELECT '914100', '20110722', '305421','0', '0' UNION ALL

SELECT '914100', '20110722', '305422','0', '1' UNION ALL

SELECT '931600', '20110707', '301149','0', '1' UNION ALL

SELECT '931600', '20110707', '301152','0', '1' UNION ALL

SELECT '931600', '20110708', '301645','1', '0' UNION ALL

SELECT '931600', '20110708', '301935','0.5', '0' UNION ALL

SELECT '931600', '20110713', '302732','0.75', '0' UNION ALL

SELECT '931600', '20110714', '302912','0.25', '0' UNION ALL

SELECT '931600', '20110714', '302915','1.5', '0' UNION ALL

SELECT '931600', '20110714', '302917','0.25', '0' UNION ALL

SELECT '931600', '20110714', '302920','0', '0' UNION ALL

SELECT '931600', '20110718', '304049','1', '0' UNION ALL

SELECT '931600', '20110718', '304065','0.25', '0' UNION ALL

SELECT '931600', '20110718', '304066','1', '0' UNION ALL

SELECT '931600', '20110718', '304069','0.25', '0' UNION ALL

SELECT '931600', '20110720', '304655','0.75', '0' UNION ALL

SELECT '931600', '20110720', '304803','2', '0' UNION ALL

SELECT '931600', '20110721', '304956','1', '0' UNION ALL

SELECT '931600', '20110721', '304959','0.5', '0' UNION ALL

SELECT '931600', '20110721', '305105','2.75', '0' UNION ALL

SELECT '931600', '20110727', '306573','1.25', '0' UNION ALL

SELECT '968800', '20110630', '299505','0', '0' UNION ALL

SELECT '968800', '20110714', '302882','0', '0' UNION ALL

SELECT '968800', '20110715', '303651','0', '0' UNION ALL

SELECT '968800', '20110715', '303688','0.25', '0' UNION ALL

SELECT '968800', '20110727', '306569','0', '0' UNION ALL

SELECT '968800', '20110804', '308661','0', '0' UNION ALL

SELECT '968800', '20110804', '308705','0', '0' UNION ALL

SELECT '968800', '20110804', '308709','0.25', '0' UNION ALL

SELECT '968800', '20110804', '308743','0.25', '0' UNION ALL

SELECT '968800', '20110804', '308745','0.5', '0' UNION ALL

SELECT '968800', '20110804', '308852','2.5', '0' UNION ALL

SELECT '968800', '20110804', '308870','0', '0' UNION ALL

SELECT '1048500', '20110630', '299502','0', '0' UNION ALL

SELECT '1048500', '20110714', '302883','0', '0' UNION ALL

SELECT '1048500', '20110719', '304268','0.25', '0' UNION ALL

SELECT '1048500', '20110719', '304435','1.25', '1' UNION ALL

SELECT '1048500', '20110719', '304436','0.25', '1' UNION ALL

SELECT '1048500', '20110720', '304666','1', '1' UNION ALL

SELECT '1048500', '20110727', '306570','0', '0' UNION ALL

SELECT '1048500', '20110727', '306572','0', '0' UNION ALL

SELECT '1048500', '20110728', '306871','0.5', '0' UNION ALL

SELECT '1048500', '20110801', '307691','0.25', '0' UNION ALL

SELECT '1048500', '20110801', '307695','0.5', '0' UNION ALL

SELECT '1048500', '20110801', '307709','1', '0' UNION ALL

SELECT '1048500', '20110801', '307716','0.25', '0' UNION ALL

SELECT '1048500', '20110801', '307726','0.25', '0' UNION ALL

SELECT '1048500', '20110803', '308432','4', '0' UNION ALL

SELECT '1048500', '20110803', '308525','0.25', '0' UNION ALL

SELECT '1048500', '20110805', '309182','1', '0' UNION ALL

SELECT '1048500', '20110805', '309184','0.25', '0' UNION ALL

SELECT '1048500', '20110805', '309192','0.25', '0' UNION ALL

SELECT '1048500', '20110810', '310138','0.25', '0' UNION ALL

SELECT '1048500', '20110810', '310142','0.25', '0' UNION ALL

SELECT '1048500', '20110810', '310193','0.25', '0' UNION ALL

SELECT '1048500', '20110810', '310210','1.5', '0' UNION ALL

SELECT '1048500', '20110811', '310578','1', '0' UNION ALL

SELECT '1048500', '20110811', '310584','0.25', '0' UNION ALL

SELECT '1048500', '20110811', '310610','0', '0' UNION ALL

SELECT '1048500', '20110811', '310611','0.25', '0' UNION ALL

SELECT '1048500', '20110812', '311007','1', '0' UNION ALL

SELECT '1048500', '20110817', '312180','0.5', '0' UNION ALL

SELECT '1048500', '20110818', '312463','0', '0' UNION ALL

SELECT '1048500', '20110818', '312468','0.25', '0' UNION ALL

SELECT '1048500', '20110818', '312532','0.75', '0' UNION ALL

SELECT '1048500', '20110818', '312629','1.25', '0' UNION ALL

SELECT '1048500', '20110822', '313188','3', '0' UNION ALL

SELECT '1048500', '20110822', '313261','1', '0' UNION ALL

SELECT '1048500', '20110822', '313288','0.75', '0' UNION ALL

SELECT '1048500', '20110823', '313527','1', '0' UNION ALL

SELECT '1048500', '20110823', '313556','0.25', '0' UNION ALL

SELECT '1048500', '20110823', '313667','1', '0' UNION ALL

SELECT '1048500', '20110823', '313694','1', '0' UNION ALL

SELECT '1048500', '20110824', '313877','0.25', '0' UNION ALL

SELECT '1048500', '20110824', '313951','0', '0' UNION ALL

SELECT '1048500', '20110824', '313982','0.25', '0' UNION ALL

SELECT '1048500', '20110824', '314057','0.25', '0' UNION ALL

SELECT '1048500', '20110824', '314164','1.25', '0' UNION ALL

SELECT '1048500', '20110825', '314411','1.25', '0' UNION ALL

SELECT '1048500', '20110825', '314447','0.5', '0' UNION ALL

SELECT '1048500', '20110902', '316818','0', '0' UNION ALL

SELECT '1048500', '20110902', '316832','0.25', '0' UNION ALL

SELECT '1048500', '20110915', '319829','0', '0' UNION ALL

SELECT '1048500', '20110915', '319979','0', '0' UNION ALL

SELECT '1048500', '20110927', '323034','0.25', '0' UNION ALL

SELECT '1048500', '20111007', '325971','2', '0' UNION ALL

SELECT '1048500', '20111007', '326073','0.25', '0' UNION ALL

SELECT '1048500', '20111013', '327815','0.5', '0' UNION ALL

SELECT '1048500', '20111020', '329610','0', '0' UNION ALL

SELECT '1048500', '20111020', '329703','0.5', '0' UNION ALL

SELECT '1048500', '20111027', '331562','0.25', '0' UNION ALL

SELECT '1048500', '20111109', '335789','0.25', '0' UNION ALL

SELECT '1048500', '20111130', '341207','0', '0' UNION ALL

SELECT '1067700', '20110811', '310582','0', '0' UNION ALL

SELECT '1067700', '20110913', '319090','0', '0' UNION ALL

SELECT '1067700', '20111110', '336253','0.25', '1' UNION ALL

SELECT '1067700', '20111110', '336281','0.5', '1' UNION ALL

SELECT '1067700', '20111110', '336283','0', '1' UNION ALL

SELECT '1067700', '20111111', '336683','4.25', '1' UNION ALL

SELECT '1067700', '20111114', '337191','1.25', '1' UNION ALL

SELECT '1067700', '20111115', '337403','0.25', '1' UNION ALL

SELECT '1067700', '20111116', '338093','1.25', '1' UNION ALL

SELECT '1067700', '20111117', '338408','0.25', '1' UNION ALL

SELECT '1067700', '20111118', '338859','4', '1' UNION ALL

SELECT '1067700', '20111118', '338866','0.75', '1' UNION ALL

SELECT '1067700', '20111117', '338930','7', '1' UNION ALL

SELECT '1067700', '20111118', '338932','4', '1' UNION ALL

SELECT '1067700', '20111123', '339729','0.25', '1' UNION ALL

SELECT '1067700', '20111121', '339940','8', '1' UNION ALL

SELECT '1067700', '20111122', '339941','8', '1' UNION ALL

SELECT '1067700', '20111123', '339943','8', '1' UNION ALL

SELECT '1067700', '20111129', '340827','1', '1' UNION ALL

SELECT '1067700', '20111128', '340830','8', '1' UNION ALL

SELECT '1067700', '20111129', '340831','8', '1' UNION ALL

SELECT '1067700', '20111130', '341049','1.25', '1' UNION ALL

SELECT '1067700', '20111130', '341202','0.25', '1' UNION ALL

SELECT '1067700', '20111130', '341316','4', '1' UNION ALL

SELECT '1067700', '20111201', '341525','0.25', '1' UNION ALL

SELECT '1067700', '20111201', '341625','1.25', '1' UNION ALL

SELECT '1067700', '20111201', '341650','0', '1' UNION ALL

SELECT '1067700', '20111201', '341825','4', '1' UNION ALL

SELECT '1067700', '20111205', '342509','0.25', '1' UNION ALL

SELECT '1067700', '20111205', '342604','1', '1' UNION ALL

SELECT '1067700', '20111206', '342631','0', '1' UNION ALL

SELECT '1067700', '20111206', '342695','2', '1' UNION ALL

SELECT '1067700', '20111205', '342779','2', '1' UNION ALL

SELECT '1067700', '20111206', '342897','0.25', '1' UNION ALL

SELECT '1067700', '20111207', '343386','1', '1' UNION ALL

SELECT '1067700', '20111209', '343920','0', '1' UNION ALL

SELECT '1067700', '20111215', '345698','0.25', '1' UNION ALL

SELECT '1067700', '20111216', '346037','0.75', '0' UNION ALL

SELECT '1067700', '20111219', '346399','0.5', '0' UNION ALL

SELECT '1076200', '20110705', '300586','0.25', '0'

USE Reporting

IF OBJECT_ID('Reporting.dbo.Sales','U') IS NOT NULL

DROP TABLE Reporting.dbo.Sales

CREATE TABLE dbo.Sales

(AcctId numeric(18,0), Com_Id numeric(18,0), Com_date numeric(18,0), Hrs numeric(18,2) NOT NULL)

INSERT INTO dbo.Sales

(AcctId, Com_Id, Com_date, Hrs)

SELECT

AcctId,

Com_Id,

Com_date,

Hrs

FROM ##TempRt

IF OBJECT_ID('TempDB..#CumeSales','U') IS NOT NULL

DROP TABLE #CumeSales;

WITH

ctePreAggregate AS

(SELECT AcctId, Com_Id, Com_date,

TotalMonth = SUM(Hrs)

FROM dbo.Sales

GROUP BY AcctId, Com_Id, Com_date)

SELECT

AcctId = ISNULL(AcctId ,0),

Com_date = ISNULL(Com_date ,0),

Com_Id = ISNULL(Com_Id ,0),

TotalMonth,

TotalCume = CAST(0 AS numeric(18,2))

INTO #CumeSales

FROM ctePreAggregate;

ALTER TABLE #CumeSales

ADD PRIMARY KEY CLUSTERED (AcctId,Com_date,Com_Id) WITH FILLFACTOR = 100;

DECLARE @PreviousBalance numeric(18,2),

@Anchor numeric(18,2),

@Counter numeric(18,2)

SELECT @PreviousBalance = 0,

@Counter = 1;

WITH

cteBaseData AS

(SELECT RowNum = ROW_NUMBER() OVER

(ORDER BY AcctId, Com_date, Com_Id),

TotalMonth,

TotalCume

FROM #CumeSales WITH (TABLOCKX) )

UPDATE tgt

SET @PreviousBalance = tgt.TotalCume = CASE

WHEN @Counter = tgt.RowNum

THEN tgt.TotalMonth + @PreviousBalance

ELSE 1/0 END,

@Anchor = tgt.RowNum,

@Counter = @Counter + 1

FROM cteBaseData tgt

OPTION (MAXDOP 1) ;

SELECT AcctId, Com_date, Com_Id, TotalMonth, TotalCume

FROM #CumeSales

ORDER BY AcctId, Com_date, Com_Id;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TedT Right there with Babe Points: 796 More actions · Answer 1

I'm not totally clear on what you're trying to achieve here. If you're after a running total, how about this:

;With CteCumulativeSales



 as		(	 select  *,

					 row_Number() over (partition by AcctId order by Com_Date) as SqNo

			 from   dbo.Sales t1

			)

	Select		t1.AcctId,

				t1.Com_id,

				t1.Com_Date,

				t1.Hrs,

				sum(t2.Hrs) as Cumulative_Hrs

	from 		cteCumulativeSales t1

	join		cteCumulativeSales t2 on t1.AcctId = t2.AcctId and t1.SqNo >= t2.SqNo

	group by	t1.AcctId,

				t1.Com_id,

				t1.Com_Date,

				t1.Hrs

	order by	t1.AcctId,

				t1.Com_id,

				t1.Com_Date

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

LutzM SSC Guru Points: 107049 More actions · Answer 2

You didn't really use the anchor in your code.

I've added AcctId to the code and assigned it to the anchor variable. Then I included the anchor to reset @PreviousBalance.

DECLARE



  @PreviousBalance NUMERIC(18, 2),

  @Anchor NUMERIC(18, 0),

  @Counter NUMERIC(18, 2)

SELECT

  @PreviousBalance = 0,

  @Counter = 1,

  @Anchor = 0 ;

WITH  cteBaseData

        AS ( SELECT

              RowNum = ROW_NUMBER() OVER ( ORDER BY AcctId, Com_date, Com_Id ),

              AcctId,

              TotalMonth,

              TotalCume

             FROM

              #CumeSales WITH ( TABLOCKX ))

  UPDATE

    tgt

  SET

    @PreviousBalance = tgt.TotalCume = CASE WHEN @Counter = tgt.RowNum

                                                 AND @Anchor = AcctId

                                            THEN tgt.TotalMonth

                                                 + @PreviousBalance

                                            WHEN @Counter = tgt.RowNum

                                                 AND @Anchor <> AcctId

                                            THEN tgt.TotalMonth

                                            ELSE 1 / 0

                                       END,

    @Anchor = AcctId,

    @Counter = @Counter + 1

  FROM

    cteBaseData tgt

  OPTION

    ( MAXDOP 1 ) ;

SELECT

  AcctId,

  Com_date,

  Com_Id,

  TotalMonth,

  TotalCume

FROM

  #CumeSales

ORDER BY

  AcctId,

  Com_date,

Com_Id ;

Edit: @TedT

The reason for using the "Quirky Update" method instead of a triangular join (seems like this is what you're using) is simply performance. See Jeffs great article[/url] for more details

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

texpic SSCertifiable Points: 5885 More actions · Answer 3

texpic

SSCertifiable

Points: 5885

December 26, 2011 at 3:41 pm

#1425539

Works perfect, thanks ... very fast also