Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Cummulative Total

    I'm trying to show a running sum on a set of records ordered by
    Date and Order Number to prioritize shipments by first-in-first-out.

    Code:
    CREATE TABLE #TMP (
    	DUE_DATE DATETIME,
    	ORD_NUM CHAR(10),
    	PRODUCT CHAR(3),
    	TONS  REAL)
    
    INSERT INTO #TMP
    SELECT '2/23/07', '07026.0030', 'ABC', 3.375
    UNION ALL
    SELECT '2/23/07', '07047.0059', 'ABC', 3.375
    UNION ALL
    SELECT '2/23/07', '07053.0080', 'ABC', 3.375
    UNION ALL
    SELECT '2/24/07', '07045.0030', 'ABC', 2.25
    UNION ALL
    SELECT '2/25/07','07045.0027','ABC',1.125
    UNION ALL
    SELECT '2/25/07','07046.0070','ABC',6.75
    
    SELECT T1.PRODUCT, T1.DUE_DATE, T1.ORD_NUM, T1.TONS, 
    'TTL_TONS'=(SELECT SUM(TONS) AS CUMM
    FROM #TMP T2
    WHERE T1.DUE_DATE>=T2.DUE_DATE AND T1.ORD_NUM>=T2.ORD_NUM )
    FROM #TMP T1
    
    DROP TABLE #TMP
    The first 3 records returned show the right numbers in the ttl_tons column,
    but then it falls apart after that?

    Any hints?
    Last edited by RedNeckGeek; 02-23-07 at 10:53.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Maybe

    Code:
    
    CREATETABLE #TMP (
    DUE_DATE DATETIME,
    ORD_NUM CHAR(10),
    PRODUCT CHAR(3),
    TONS REAL)
    INSERTINTO #TMP
    SELECT'20070223','07026.0030','ABC', 3.375
    UNIONALL
    SELECT'20070223','07047.0059','ABC', 3.375
    UNIONALL
    SELECT'20070223','07053.0080','ABC', 3.375
    UNIONALL
    SELECT'20070224','07045.0030','ABC', 2.25
    UNIONALL
    SELECT'20070225','07045.0027','ABC',1.125
    UNIONALL
    SELECT'20070225','07046.0070','ABC',6.75
    SELECT T1.PRODUCT
    , T1.DUE_DATE
    , T1.ORD_NUM
    , T1.TONS
    ,'TTL_TONS'=(SELECTSUM(TONS)AS CUMM
    FROM #TMP T2
    WHERE T1.DUE_DATE>=T2.DUE_DATE 
    AND T1.ORD_NUM >=CASE 
    WHEN T1.DUE_DATE=T2.DUE_DATE THEN 
    T2.ORD_NUM 
    ELSE 
    T1.ORD_NUM 
    END)
    FROM #TMP T1
    ORDERBY 
    due_date
    , ord_num
    DROPTABLE #TMP
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I assume you want to avoid a cursor....but, since the dattime vaalue is not unique, it still would be random
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's what I was looking for.

    Thanks
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Brett Kaiser
    I assume you want to avoid a cursor....but, since the dattime vaalue is not unique, it still would be random
    Yeah, I'm actually rewriting an older sproc that uses a cursor. I'm trying to get away from them whenever I can.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Interesting reading. Don't know if he ever sorted out his summary\ conclusion though - it bears little relation to information in the article. Anyhoo -

    http://www.sql-server-performance.co...ly_problem.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm thinking along the lines of an IDENTITY

    Code:
    SELECT T1.PRODUCT, T1.DUE_DATE, T1.ORD_NUM, T1.TONS, 
    'TTL_TONS'=(  SELECT SUM(TONS) AS CUMM
    		FROM #TMP T2
    	       WHERE T1.DUE_DATE > T2.DUE_DATE 
    --  		 AND T1.ORD_NUM  > T2.ORD_NUM
    		 AND T1.RowID    > T2.RowId)
      FROM #TMP T1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you not feel a little dirty altering a table schema to meet the derived-data requirment of a query? More efficient but... well.... naughty.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I was actually on the verge of adding an identity column before posting this question. I really wanted to see if it could be done without one, because this method will make the code more understandable when I have to come back to it in 6 months.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Do you not feel a little dirty altering a table schema to meet the derived-data requirment of a query? More efficient but... well.... naughty.

    Listen, if you want to use SQL to do ostuff that should be a presentation issue, then all bets are off.

    Besides, I like being naughty
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by Brett Kaiser
    Listen, if you want to use SQL to do ostuff that should be a presentation issue, then all bets are off.

    ...
    Why do it at the presentaton level, when it's so much easier to do it in SQL?
    Inspiration Through Fermentation

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    performance, performance, performance.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    pootle,

    Not sure this is what you meant, but...

    Yes, it's all about performance: I imagine it would take me a few weeks to rewrite the 3 reports that hit the recordset my sproc supplies. Once that was done, my guess is that it would take 5 minutes for the biggest report to generate "on demand". My users would throw a fit over a report taking that long. This sproc takes 6 seconds now, (it took 6 minutes when I was using a cursor), and the report comes up instantly.

    So why not use the most efficient tool for the job?
    Inspiration Through Fermentation

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You don't think iot would be fatser on the fron end?

    What reporting tool are you using?

    Go Rangers!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm using Actuate report writer. Since I can use a sproc to supply data to the report, and pass parameters back and forth between the two, I guess I've always viewed the sproc as just a part of the report.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •