Datewise Cummulative total for particular customer

• chintalagiriprakash

  SSC-Addicted

  Points: 434

  More actions

  February 13, 2011 at 10:27 pm

  #231820

  Hi Sql Experts i need help in this query .My requirement is i want datewise and customer wise cummulative balance for a particular particular selection dates

  My query is

  SELECT a.DocNum,Convert(varchar(10),a.DocDate,103)'DocDate',a.cardcode,a.cardname, a.Doctotal,

  (Select SUM(Doctotal) from ORCT where cardcode='C000732'

  and DocNum <=a.DocNum ) AS RunningTotal

  FROM ORct a where a.cardcode='C000732'

  GROUP BY a.DocNum ,a.DocDate, a.Doctotal,a.cardcode,a.cardname

  ORDER BY a.DocNum , a.Doctotal

  For this query my output is like this

  DocNum Docdate CardCode CardName DocTotal Cummulative balance

  11 15/04/2009 C000732 GOKALDAS EXPORTS 246088.000000 246088.000000

  21 28/04/2009 C000732 GOKALDAS EXPORTS 172700.000000 418788.000000

  68 18/06/2009 C000732 GOKALDAS EXPORTS 388300.000000 807088.000000

  112 28/10/2009 C000732 GOKALDAS EXPORTS 985.000000 808073.000000

  113 28/10/2009 C000732 GOKALDAS EXPORTS 98250.000000 906323.000000

  137 24/07/2009 C000732 GOKALDAS EXPORTS 326560.000000 1232883.000000

  147 26/08/2009 C000732 GOKALDAS EXPORTS 48600.000000 1281483.000000

  161 11/07/2009 C000732 GOKALDAS EXPORTS 13500.000000 1294983.000000

  182 04/12/2009 C000732 GOKALDAS EXPORTS 1500000.000000 2794983.000000

  494 08/06/2009 C000732 GOKALDAS EXPORTS 1788094.000000 4583077.000000

  507 06/01/2010 C000732 GOKALDAS EXPORTS 197020.000000 4780097.000000

  518 20/01/2010 C000732 GOKALDAS EXPORTS 116660.000000 4896757.000000

  522 27/01/2010 C000732 GOKALDAS EXPORTS 207580.000000 5104337.000000

  565 13/02/2010 C000732 GOKALDAS EXPORTS 166336.000000 5270673.000000

  589 05/08/2009 C000732 GOKALDAS EXPORTS 326560.000000 5597233.000000

  642 24/02/2010 C000732 GOKALDAS EXPORTS 1930.000000 5599163.000000

  643 24/02/2010 C000732 GOKALDAS EXPORTS 538468.000000 6137631.000000

  1000136 08/06/2010 C000732 GOKALDAS EXPORTS 10701.600000 6148332.600000

  1000137 08/06/2010 C000732 GOKALDAS EXPORTS 390580.000000 6538912.600000

  1000196 10/07/2010 C000732 GOKALDAS EXPORTS 137820.000000 6676732.600000

  1000224 03/08/2010 C000732 GOKALDAS EXPORTS 2500000.000000 9176732.600000

  1000233 05/08/2010 C000732 GOKALDAS EXPORTS 147540.000000 9324272.600000

  1000237 12/08/2010 C000732 GOKALDAS EXPORTS 1000000.000000 10324272.600000

  1000453 14/12/2010 C000732 GOKALDAS EXPORTS 137820.000000 10462092.600000

  1000501 04/01/2011 C000732 GOKALDAS EXPORTS 161280.000000 10623372.600000

  1000504 08/01/2011 C000732 GOKALDAS EXPORTS 1400000.000000 12023372.600000

  1000505 08/01/2011 C000732 GOKALDAS EXPORTS 47948.000000 12071320.600000

  1000537 04/01/2011 C000732 GOKALDAS EXPORTS 161260.000000 12232580.600000

  for this query iam getting correct output when i conditon as cardcode but when i give condition as date cummulative balance is coming wrong so when i give date as condition then also it shud show the same result

  SELECT a.DocNum,Convert(varchar(10),a.DocDate,103)'DocDate',a.cardcode,a.cardname, a.Doctotal,

  (Select SUM(Doctotal) from ORCT where cardcode='C000732'

  and DocNum <=a.DocNum ) AS RunningTotal

  FROM ORct a where a.cardcode='C000732' AND a.DocDate>='2010/04/01' AND a.DocDate<='2011/02/14

  GROUP BY a.DocNum ,a.DocDate, a.Doctotal,a.cardcode,a.cardname

  ORDER BY a.DocNum , a.Doctotal

  this is what when i give condition iam getting

  DocNum Docdate CardCode CardName DocTotal Cummulative balance

  1000136 08/06/2010 C000732 GOKALDAS EXPORTS 10701.600000 6137631.000000

  1000137 08/06/2010 C000732 GOKALDAS EXPORTS 390580.000000 6137631.000000

  1000196 10/07/2010 C000732 GOKALDAS EXPORTS 137820.000000 6538912.600000

  1000224 03/08/2010 C000732 GOKALDAS EXPORTS 2500000.000000 6676732.600000

  1000233 05/08/2010 C000732 GOKALDAS EXPORTS 147540.000000 9176732.600000

  1000237 12/08/2010 C000732 GOKALDAS EXPORTS 1000000.000000 9324272.600000

  1000453 14/12/2010 C000732 GOKALDAS EXPORTS 137820.000000 10324272.600000

  1000501 04/01/2011 C000732 GOKALDAS EXPORTS 161280.000000 10462092.600000

  1000504 08/01/2011 C000732 GOKALDAS EXPORTS 1400000.000000 10623372.600000

  1000505 08/01/2011 C000732 GOKALDAS EXPORTS 47948.000000 10623372.600000

  1000537 04/01/2011 C000732 GOKALDAS EXPORTS 161260.000000 10462092.600000

  for this my output shuld be like this

  DocNum Docdate CardCode CardName DocTotal Cummulative balance

  1000136 08/06/2010 C000732 GOKALDAS EXPORTS 10701.600000 10701.000000

  1000137 08/06/2010 C000732 GOKALDAS EXPORTS 390580.000000 401281.000000 (sum of 10701 &390580)

  1000196 10/07/2010 C000732 GOKALDAS EXPORTS 137820.000000 6538912.600000

  1000224 03/08/2010 C000732 GOKALDAS EXPORTS 2500000.000000 6676732.600000

  1000233 05/08/2010 C000732 GOKALDAS EXPORTS 147540.000000 9176732.600000

  1000237 12/08/2010 C000732 GOKALDAS EXPORTS 1000000.000000 9324272.600000

  1000453 14/12/2010 C000732 GOKALDAS EXPORTS 137820.000000 10324272.600000

  1000501 04/01/2011 C000732 GOKALDAS EXPORTS 161280.000000 10462092.600000

  1000504 08/01/2011 C000732 GOKALDAS EXPORTS 1400000.000000 10623372.600000

  1000505 08/01/2011 C000732 GOKALDAS EXPORTS 47948.000000 10623372.600000

  1000537 04/01/2011 C000732 GOKALDAS EXPORTS 161260.000000 10462092.600000

  similarly for rest of the data

  Regards

  Prakash

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  February 13, 2011 at 10:44 pm

  #1286270

  It's because in this statement:

  SELECT a.DocNum,Convert(varchar(10),a.DocDate,103)'DocDate',a.cardcode,a.cardname, a.Doctotal,

  (Select SUM(Doctotal) from ORCT where cardcode='C000732'

  and DocNum <=a.DocNum ) AS RunningTotal

  FROM ORct a where a.cardcode='C000732' AND a.DocDate>='2010/04/01' AND a.DocDate<='2011/02/14

  GROUP BY a.DocNum ,a.DocDate, a.Doctotal,a.cardcode,a.cardname

  ORDER BY a.DocNum , a.Doctotal

  You need to include the where clause in both the correllated sub-query as you do in the main query. Your SUM() is operating on a different set of conditions.

  You might also look into what's called the Running Totals query. There's some examples you can google that utilize the serial updating mechanic that's undocumented.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• chintalagiriprakash

  SSC-Addicted

  Points: 434

  More actions

  February 13, 2011 at 11:04 pm

  #1286275

  hello carines

  i have searched in google but i couldnot get any such kind of query with datewise so u please kindly write a query and send me .

  thanks & regards

  prakash

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  February 13, 2011 at 11:16 pm

  #1286279

  Try this:

  SELECT a.DocNum,Convert(varchar(10),a.DocDate,103)'DocDate',a.cardcode,a.cardname, a.Doctotal,

  (Select SUM(Doctotal) from ORCT where a.cardcode='C000732' AND a.DocDate>='2010/04/01' AND a.DocDate<='2011/02/14' and DocNum <=a.DocNum ) AS RunningTotal

  FROM ORct a where a.cardcode='C000732' AND a.DocDate>='2010/04/01' AND a.DocDate<='2011/02/14'

  GROUP BY a.DocNum ,a.DocDate, a.Doctotal,a.cardcode,a.cardname

  There's a way to use a serial update but I can't find an easy reference even knowing what I'm looking for. I'd have to build it out and it requires an understanding of a set of what seems to be arbitrary rules at first. I'll see if I can get a chance to tackle it tomorrow if someone can't step in first.

  Do notice, however, what is above and how the where clause matches in the subquery in the select portion, and the where clause that contains the outer query.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  February 13, 2011 at 11:24 pm

  #1286282

  Now be careful folks. You've both built Triangular Joins which can be literally millions of times worse than a cursor. If you don't want to use the "Quirky Update" method, then use a Cursor or While Loop instead of the Triangular Joins you're using.

  Please see the following article as to why Triangular Joins can be so bad...

  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)
  

• chintalagiriprakash

  SSC-Addicted

  Points: 434

  More actions

  February 14, 2011 at 12:27 am

  #1286306

  hi buddy

  i have already tried this one but its not working please provide some other solutions ..

  thanks & regards

  Prakash

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  February 14, 2011 at 2:35 am

  #1286341

  Jeff Moden (2/13/2011)

  ---

  Now be careful folks. You've both built Triangular Joins which can be literally millions of times worse than a cursor. If you don't want to use the "Quirky Update" method, then use a Cursor or While Loop instead of the Triangular Joins you're using.

  Please see the following article as to why Triangular Joins can be so bad...

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

  Yeaaah, I know. :pinch: I figured we'd try the simple fix first, though. 🙂

  Prakash, can you tell me how or why the most recent query I posted does not work?

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  February 14, 2011 at 6:41 am

  #1286431

  chintalagiriprakash (2/14/2011)

  ---

  hi buddy

  i have already tried this one but its not working please provide some other solutions ..

  thanks & regards

  Prakash

  If you'll provide the data in a readily consumable format (see the first link in my signature line below), I'll be happy to show you a very high speed method for doing this type of thing.

  --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)
  

• chintalagiriprakash

  SSC-Addicted

  Points: 434

  More actions

  February 14, 2011 at 11:25 pm

  #1286870

  hi Jeff

  i donot know why it is not working i wrote the query as u mention in the link but i could not get the result

  thanks & regards

  Giri Prakash

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  February 15, 2011 at 12:11 am

  #1286878

  chintalagiriprakash (2/14/2011)

  ---

  hi Jeff

  i donot know why it is not working i wrote the query as u mention in the link but i could not get the result

  thanks & regards

  Giri Prakash

  Is it giving an error or what? Post the query so we can take a look at it for you.

  --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)
  

• chintalagiriprakash

  SSC-Addicted

  Points: 434

  More actions

  February 15, 2011 at 12:23 am

  #1286880

  Hi

  Jeff

  i placed query including output And how i need an output everything on my question u just look in to my first question ...

  Regards

  Prakash

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  February 15, 2011 at 12:28 pm

  #1287266

  chintalagiriprakash (2/15/2011)

  ---

  Hi

  Jeff

  i placed query including output And how i need an output everything on my question u just look in to my first question ...

  Regards

  Prakash

  Nope... the data in your first post isn't in a readily consumable format. Please read the article on how to do that correctly at the first link in my signature line below. I just don't have the time to do it for you.

  --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)