Running totals... in groups

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 24, 2010 at 3:21 am

  #387644

  Hi!

  EDIT: I rewrite the question and provide some sample data:

  I need to calculate movings and stock for each date for each brand and outlet.

  Sample table:

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

  DROP TABLE #mytable

  CREATE TABLE #mytable

  (

  DateCountDATETIME,

  Outletint,

  Brandnvarchar(14),

  quantINT

  )

  --===== Insert the test data into the test table

  INSERT INTO #mytable

  (DateCount, Outlet, Brand, quant)

  SELECT 'Jan 1 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1000','OOB820','18' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1000','OOB820','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1000','MMM004','36' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3000','MMM004','-80' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','2200','MMM004','2' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','2200','MMM004','2' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3002','MMM004','20' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1800','OOB820','19' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1800','MMM004','11' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','1000','OOB820','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3103','MMM004','8' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3103','MMM004','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3003','MMM004','96' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3009','MMM004','-74' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3004','MMM004','135' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3007','MMM004','48' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','3006','MMM004','-33' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','2200','MMM004','1' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','2200','MMM004','4' UNION ALL

  SELECT 'Jan 1 2010 12:00AM','2200','MMM004','4' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','OOB820','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','OOB820','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3000','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3003','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','3004','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','2200','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','2200','MMM004','1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','2200','MMM004','-1' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','2200','MMM004','9' UNION ALL

  SELECT 'Jan 2 2010 12:00AM','2200','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','OOB820','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3009','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3000','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','-3' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','5' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','5' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','2' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','2' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','-4' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','-9' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','2200','MMM004','3' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3002','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3002','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','3002','MMM004','-1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','MMM004','1' UNION ALL

  SELECT 'Jan 3 2010 12:00AM','1000','OOB820','1'

  I want the calculated result to be in the following form (not the real representation of the test table) (grouped by dateCount, outlet and brand):

  DateCount outlet brand movings stock

  ----------------------- ----------- -------------- ----------- -----------

  2010-01-01 00:00:00.000 1000 MMM004 135 135

  2010-01-02 00:00:00.000 1000 MMM004 -20 115

  2010-01-03 00:00:00.000 1000 MMM004 -19 96

  2010-01-04 00:00:00.000 1000 MMM004 -9 87

  2010-01-05 00:00:00.000 1000 MMM004 13 100

  2010-01-06 00:00:00.000 1000 MMM004 -2 98

  2010-01-07 00:00:00.000 1000 MMM004 12 110

  2010-01-08 00:00:00.000 1000 MMM004 -14 96

  ...

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 24, 2010 at 3:58 am

  #1210885

  Sample tables and data please.

  Read the link in my signature for how to best display this.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• WayneS

  SSC Guru

  Points: 95461

  More actions

  August 24, 2010 at 9:17 am

  #1211095

  You might want to check out this excellent article[/url] for how to handle your running totals.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 25, 2010 at 3:24 am

  #1211505

  That's a bit better 🙂

  OK, what do "movings" and "stock" represent? Is movings the sum of the "quant" per day for each brand/outlet? Then stock the running total of the movings?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 25, 2010 at 3:38 am

  #1211512

  skcadavre (8/25/2010)

  ---

  That's a bit better 🙂

  OK, what do "movings" and "stock" represent? Is movings the sum of the "quant" per day for each brand/outlet? Then stock the running total of the movings?

  Correct! Sorry for such a crappy presentation of the problem.. Am reading that "excellent article" ;).. I'm excited about the speed and the hacking spirit around it, but I'm afraid it's a bit too advanced for my skills and also doesn't really solve my problem. I'm considering using nested cursors. Doesn't sound to good but hey..

  Edit: nested "quirky update"?? huh..

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 25, 2010 at 4:19 am

  #1211520

  okkko (8/25/2010)

  ---

  skcadavre (8/25/2010)

  ---

  That's a bit better 🙂

  OK, what do "movings" and "stock" represent? Is movings the sum of the "quant" per day for each brand/outlet? Then stock the running total of the movings?

  Correct! Sorry for such a crappy presentation of the problem.. Am reading that "excellent article" ;).. I'm excited about the speed and the hacking spirit around it, but I'm afraid it's a bit too advanced for my skills and also doesn't really solve my problem. I'm considering using nested cursors. Doesn't sound to good but hey..

  Edit: nested "quirky update"?? huh..

  I'd go with something like this: -

  ALTER TABLE #mytable ADD id INT IDENTITY;

  SELECT t.datecount

  ,t.outlet

  ,t.brand

  ,SUM(t.quant)

  ,(SELECT SUM(t2.quant)

  FROM #mytable t2

  WHERE t2.outlet = t.outlet

  AND t2.brand = t.brand

  AND t2.id <= t.id) AS runningtotal

  FROM #mytable t

  GROUP BY datecount

  ,outlet

  ,brand

  ,id

  ORDER BY datecount

  ,id

  Warning, I've not checked that the results are correct!

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 25, 2010 at 6:22 am

  #1211581

  Correct! Sorry for such a crappy presentation of the problem.. Am reading that "excellent article" ;).. I'm excited about the speed and the hacking spirit around it, but I'm afraid it's a bit too advanced for my skills and also doesn't really solve my problem. I'm considering using nested cursors. Doesn't sound to good but hey..

  Edit: nested "quirky update"?? huh..

  just an idea using the quirky method....is the type of results you are looking for?

  ALTER TABLE #mytable ADD id INT IDENTITY

  GO

  SELECT DateCount, Outlet, Brand, quant, id, 0 AS Runtot, 0 AS DailyTot

  INTO #mytable2

  FROM #mytable

  CREATE UNIQUE CLUSTERED INDEX [UCI_Idx] ON [dbo].[#mytable2] ---- needed for quirky update

  ([Outlet] ASC, [Brand] ASC, [DateCount] ASC, [id] ASC )

  GO

  DECLARE @DateCount datetime

  DECLARE @Outlet int

  DECLARE @brand-2 nvarchar(14)

  DECLARE @RunTot int

  DECLARE @DailyTot int

  UPDATE #mytable2 ---- using quirky update

  SET @RunTot = RunTot = CASE WHEN Outlet = @Outlet AND Brand = @brand-2

  THEN @RunTot + quant

  ELSE quant

  END,

  @DailyTot = DailyTot = CASE WHEN Outlet = @Outlet AND Brand = @brand-2 AND DateCount = @DateCount

  THEN @DailyTot + quant

  ELSE quant

  END,

  @Outlet = Outlet,

  @brand-2 = Brand,

  @DateCount = DateCount

  FROM #mytable2 WITH (TABLOCKX)

  OPTION (MAXDOP 1)

  GO

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2 A INNER JOIN

  (SELECT MAX(id) AS MaxID

  FROM #mytable2

  GROUP BY Outlet, Brand, DateCount) B

  ON A.id = B.MaxID

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 25, 2010 at 8:08 am

  #1211654

  skcadavre (8/25/2010)

  ---

  okkko (8/25/2010)

  ---

  skcadavre (8/25/2010)

  ---

  That's a bit better 🙂

  OK, what do "movings" and "stock" represent? Is movings the sum of the "quant" per day for each brand/outlet? Then stock the running total of the movings?

  Correct! Sorry for such a crappy presentation of the problem.. Am reading that "excellent article" ;).. I'm excited about the speed and the hacking spirit around it, but I'm afraid it's a bit too advanced for my skills and also doesn't really solve my problem. I'm considering using nested cursors. Doesn't sound to good but hey..

  Edit: nested "quirky update"?? huh..

  I'd go with something like this: -

  ALTER TABLE #mytable ADD id INT IDENTITY;

  SELECT t.datecount

  ,t.outlet

  ,t.brand

  ,SUM(t.quant)

  ,(SELECT SUM(t2.quant)

  FROM #mytable t2

  WHERE t2.outlet = t.outlet

  AND t2.brand = t.brand

  AND t2.id <= t.id) AS runningtotal

  FROM #mytable t

  GROUP BY datecount

  ,outlet

  ,brand

  ,id

  ORDER BY datecount

  ,id

  Warning, I've not checked that the results are correct!

  I think this works but is slow on large database.. THANKS anyway.

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 25, 2010 at 8:13 am

  #1211658

  gah (8/25/2010)

  ---

  Correct! Sorry for such a crappy presentation of the problem.. Am reading that "excellent article" ;).. I'm excited about the speed and the hacking spirit around it, but I'm afraid it's a bit too advanced for my skills and also doesn't really solve my problem. I'm considering using nested cursors. Doesn't sound to good but hey..

  Edit: nested "quirky update"?? huh..

  just an idea using the quirky method....is the type of results you are looking for?

  ALTER TABLE #mytable ADD id INT IDENTITY

  GO

  SELECT DateCount, Outlet, Brand, quant, id, 0 AS Runtot, 0 AS DailyTot

  INTO #mytable2

  FROM #mytable

  CREATE UNIQUE CLUSTERED INDEX [UCI_Idx] ON [dbo].[#mytable2] ---- needed for quirky update

  ([Outlet] ASC, [Brand] ASC, [DateCount] ASC, [id] ASC )

  GO

  DECLARE @DateCount datetime

  DECLARE @Outlet int

  DECLARE @brand-2 nvarchar(14)

  DECLARE @RunTot int

  DECLARE @DailyTot int

  UPDATE #mytable2 ---- using quirky update

  SET @RunTot = RunTot = CASE WHEN Outlet = @Outlet AND Brand = @brand-2

  THEN @RunTot + quant

  ELSE quant

  END,

  @DailyTot = DailyTot = CASE WHEN Outlet = @Outlet AND Brand = @brand-2 AND DateCount = @DateCount

  THEN @DailyTot + quant

  ELSE quant

  END,

  @Outlet = Outlet,

  @brand-2 = Brand,

  @DateCount = DateCount

  FROM #mytable2 WITH (TABLOCKX)

  OPTION (MAXDOP 1)

  GO

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2 A INNER JOIN

  (SELECT MAX(id) AS MaxID

  FROM #mytable2

  GROUP BY Outlet, Brand, DateCount) B

  ON A.id = B.MaxID

  I think this is somewhat what I need... only that I need the results to be grouped by DateCount.. so instead of

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2 A INNER JOIN

  (SELECT MAX(id) AS MaxID

  FROM #mytable2

  GROUP BY Outlet, Brand, DateCount) B

  ON A.id = B.MaxID

  WHERE outlet='1000' and brand='MMM004'

  I could get the same result using

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2

  WHERE outlet='1000' and brand='MMM004'

  Thank you for awesome help so far!

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 25, 2010 at 8:47 am

  #1211685

  I realise that you have probably only given us some sample data....but is there any chance that you have a unique row identifier in your real data?

  the reason I ask is that when testing this method on a million row sample the time taken to create the identity field (which is needed for the method) is taking up the majority of the time.

  Kind regards Graham

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 25, 2010 at 8:52 am

  #1211690

  I think this is somewhat what I need... only that I need the results to be grouped by DateCount.. so instead of

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2 A INNER JOIN

  (SELECT MAX(id) AS MaxID

  FROM #mytable2

  GROUP BY Outlet, Brand, DateCount) B

  ON A.id = B.MaxID

  WHERE outlet='1000' and brand='MMM004'

  I could get the same result using

  SELECT A.DateCount, A.Outlet, A.Brand, A.DailyTot, A.Runtot

  FROM #mytable2

  WHERE outlet='1000' and brand='MMM004'

  Just to be really sure...can you provide the results you require based on the test data provided...thanks

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 26, 2010 at 1:54 am

  #1212118

  gah (8/25/2010)

  ---

  I realise that you have probably only given us some sample data....but is there any chance that you have a unique row identifier in your real data

  Yeah, agreed. If you have a unique row identifier, it's much easier to do. You'll notice that both myself and gah have had to add an identity column to make our solutions work.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 26, 2010 at 2:02 am

  #1212120

  skcadavre (8/26/2010)

  ---

  gah (8/25/2010)

  ---

  I realise that you have probably only given us some sample data....but is there any chance that you have a unique row identifier in your real data

  Yeah, agreed. If you have a unique row identifier, it's much easier to do. You'll notice that both myself and gah have had to add an identity column to make our solutions work.

  I noticed that, yes. Probably I can convince my boss to add that identity column I guess..

  Gah, I will post the expected results later, I have some other work to do right now. "I'll be back..." 😉

• okkko

  SSC-Addicted

  Points: 411

  More actions

  August 27, 2010 at 6:51 am

  #1212891

  okkko (8/26/2010)

  ---

  skcadavre (8/26/2010)

  ---

  gah (8/25/2010)

  ---

  I realise that you have probably only given us some sample data....but is there any chance that you have a unique row identifier in your real data

  Yeah, agreed. If you have a unique row identifier, it's much easier to do. You'll notice that both myself and gah have had to add an identity column to make our solutions work.

  I noticed that, yes. Probably I can convince my boss to add that identity column I guess..

  Gah, I will post the expected results later, I have some other work to do right now. "I'll be back..." 😉

  I apologize to all who helped me here, but as of now, I don't need the query. Maybe later, as I have tons of other stuff to do. Let's just say that I learned something new and thanks to both skcadavre and gah. See ya!

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  August 27, 2010 at 8:56 pm

  #1213366

  skcadavre (8/25/2010)

  ---

  I'd go with something like this: -

  ALTER TABLE #mytable ADD id INT IDENTITY;

  SELECT t.datecount

  ,t.outlet

  ,t.brand

  ,SUM(t.quant)

  ,(SELECT SUM(t2.quant)

  FROM #mytable t2

  WHERE t2.outlet = t.outlet

  AND t2.brand = t.brand

  AND t2.id <= t.id) AS runningtotal

  FROM #mytable t

  GROUP BY datecount

  ,outlet

  ,brand

  ,id

  ORDER BY datecount

  ,id

  Please read the following article and find out why even a cursor is a better way to calculate a running total...

  http://www.sqlservercentral.com/articles/T-SQL/61539/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)