Running Total - Split By Acct

• texpic

  SSCertifiable

  Points: 5887

  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;

• TedT

  Right there with Babe

  Points: 796

  More actions

  December 23, 2011 at 4:47 am

  #1425098

  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

  )

  Selectt1.AcctId,

  t1.Com_id,

  t1.Com_Date,

  t1.Hrs,

  sum(t2.Hrs) as Cumulative_Hrs

  from cteCumulativeSales t1

  joincteCumulativeSales t2 on t1.AcctId = t2.AcctId and t1.SqNo >= t2.SqNo

  group byt1.AcctId,

  t1.Com_id,

  t1.Com_Date,

  t1.Hrs

  order byt1.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

  December 23, 2011 at 5:59 am

  #1425118

  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: 5887

  More actions

  December 26, 2011 at 3:41 pm

  #1425539

  Works perfect, thanks ... very fast also