Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    19

    Unanswered: Friday Fun - RunningTotalsAnyone?

    If I put this in crystal reports I can manipulate it ok, but actually translating it to a command isn't so easy for me. I'm terrible at cursors and need some help here, pretty pretty please.

    I have a table...

    CREATE TABLE [dbo].[StagePayment] (
    [PaymentID] [uniqueidentifier] NOT NULL ,
    [JobID] [uniqueidentifier] NOT NULL ,
    [Paid] [bit] NOT NULL ,
    [Sequence] [smallint] NOT NULL ,
    [Amount] [decimal](10, 2) NOT NULL ,
    [Activity] [varchar] (30) NULL ,
    [ActivityID] [uniqueidentifier] NULL ,
    [DueDate] [datetime] NULL ,
    [InvoiceNumber] [varchar] (50) NULL ,
    [DueNext] [bit] NOT NULL
    ) ON [PRIMARY]
    GO

    Simple example of the data I need to work with...

    PayID JobID Paid Sequence Amount DueNext

    6094 12 1 1 0.00 0
    6B1E 18 1 1 0.00 0
    297C 25 1 1 0.00 0
    E175 30 1 1 4390.00 0
    8CF9 30 1 2 4390.00 0
    9168 39 1 1 4400.00 0
    59F4 39 1 2 6632.00 0
    BCAF 54 1 1 417.50 0
    5922 54 0 2 417.50 0
    8DA0 68 1 1 2988.00 0
    76C5 68 1 2 9958.00 0
    0C13 68 1 3 7566.00 0
    F3F4 68 1 4 2390.00 0

    What I need to accomplish is flagging the DueNext field if the amount due has not been paid. I cannot rely on the Paid field as it's currently user controlled. I need to compare what's due with the total already paid (stored in another table).

    So for instance,
    Job #68 - the total amount due would be $22902.00

    If the customer has paid $15000.00 then Sequence 3, PayID 0C13, should be flagged as DueNext.

    Anyone have a very simple example of a running total cursor that I can reference or a better idea of how to work with this? I appreciate any and all suggestions!
    Thanks!
    abbra_doo

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can do running totals in SQLSVR without cursors, but is it better to handle them in your reporting tool (Crystal Reports, in your case).
    Include the total already paid from the other table as a column in your result set. Have Crystal keep the running total and flag records that exceed the total paid.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2003
    Posts
    60
    You could create function and bind it to the DueNext field. Inside the function you can compare the totals and have the function return either a 1 or a 0 (zero)

    Here's an example from BOL

    CREATE FUNCTION CubicVolume
    -- Input dimensions in centimeters
    (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
    RETURNS decimal(12,3) -- Cubic Centimeters.
    AS
    BEGIN
    RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
    END

    This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:

    CREATE TABLE Bricks
    (
    BrickPartNmbr int PRIMARY KEY,
    BrickColor nchar(20),
    BrickHeight decimal(4,1),
    BrickLength decimal(4,1),
    BrickWidth decimal(4,1),
    BrickVolume AS
    (
    dbo.CubicVolume(BrickHeight,
    BrickLength, BrickWidth)
    )
    )

    Obviously your logic will differ from this function but essentially it should work the same way you'll just have to have some If...then logic to determine what you want to return.

    Best regards

  4. #4
    Join Date
    Sep 2005
    Posts
    19
    Thanks blindman but our developers are revamping the application for our users and the DueNext field is new and needs to be populated within the database. Otherwise I'd be real happy as the report was alreay in place and working. Eventually this change is supposed to make life on the payment side of the fence much more intuitive and reports should become easier overall - fingers crossed!
    abbra_doo

  5. #5
    Join Date
    Sep 2005
    Posts
    19
    Thanks mkal -

    I'll read up more on this and see if it'll help.

    abbra_doo

  6. #6
    Join Date
    Sep 2005
    Posts
    19

    Brainstorming...

    Would it be easier to add a temporary field called RunningTotal to the StagePayment table and insert the tabulated running total for that job then I would only have to compare and flag DueNext as necessary? Sort of like this...

    PayID JobID Paid Sequence Amount RunningTotal DueNext

    6094 12 1 1 0.00 0.00 0
    6B1E 18 1 1 0.00 0.00 0
    297C 25 1 1 0.00 0.00 0
    E175 30 1 1 4390.00 4390.00 0
    8CF9 30 1 2 4390.00 8780.00 0
    9168 39 1 1 4400.00 4400.00 0
    59F4 39 1 2 6632.00 11032.00 0
    BCAF 54 1 1 417.50 417.50 0
    5922 54 0 2 417.50 835.00 0
    8DA0 68 1 1 2988.00 2988.00 0
    76C5 68 1 2 9958.00 12946.00 0
    0C13 68 1 3 7566.00 20512.00 0
    F3F4 68 1 4 2390.00 22902.00 0


    I'm still sort of stuck on getting the runnig total to tally right though so if anyone has thoughts I would love to hear them!
    abbra_doo

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wouldn't use a function for this.

    In your case, you need to calculate a running total, which requires a scan of the table. A user-defined function would execute once for every record in your resultset (or more), so 1000 records means 1000 function calls and 1000 scans.

    Here is one method of doing this without a cursor:
    Code:
    select	StagePayment.JobID,
    	StagePayment.Sequence,
    	StagePayment.Amount,
    	sum(RunningSums.Amount) as RunningSum
    from	StagePayment
    	inner join StagePaymen RunningSums
    		on StagePayment.JobID = RunningSums.JobID
    		and StagePayment.Sequence >= RunningSums.Sequence
    group by StagePayment.JobID,
    	StagePayment.Sequence,
    	StagePayment.Amount
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, excellent as usual

    next he's gonna ask you for the update statement...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2005
    Posts
    19

    Thanks!

    Sorry, I've been out and I'm just getting back to this.

    Thanks a bunch blindman! I was using a <= in my query and getting exactly the same as the Amount and once switched to >= I got the running total! So thanks, thanks, thanks! I was the blind-one in that query! Hahaha!

    I ended up creating a temp table and I put all the data from the StagePayment table, plus a running total column, plus the total amount paid column for each job. Then I just updated the temp table and selected the minimum sequence for a job where the amount due (running total field) was less than the amount paid. Spot checked some of the jobs and updated the actual StagePayment table and !!Voila!!, no cursor needed - I really dislike them! Everything looks like it's correct so I think I'm a happy camper!

    Thanks again!
    Hope you all have a terrific 2007 (Lunar Year of the Fire Pig)!
    abbra_doo

Posting Permissions

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