Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010

    Unanswered: SUM of each row added to the next?

    Hi Everyone!

    I have this code:

    distinct EndVal as Status
    , COUNT( datepart(DAY,Created)) as TotalStatusDayClaims
    --, convert(datetime,convert(char(10),Created,101)) as Created
    , DATEADD(DAY , 7-DATEPART(WEEKDAY,convert(datetime,convert(char(10),Created,101))),convert(datetime,convert(char(10),Created,101))) AS 'Last Day Of Week'
    from tblFieldAudit
    Where EndVal IN (select distinct Status from tblStatus)
    Group by EndVal
    , convert(datetime,convert(char(10),Created,101))
    order by 'Last Day Of Week' desc, Status

    Which yields a table like:

    STATUS / TotalStatusDayClaims / Last day Of Week
    Received 12 2010-11-27 00:00:00.000
    Received 397 2010-11-27 00:00:00.000
    Approved 23 2010-11-27 00:00:00.000
    Received 413 2010-11-20 00:00:00.000
    Received 25 2010-11-20 00:00:00.000
    Received 385 2010-11-20 00:00:00.000
    Approved 21 2010-11-20 00:00:00.000
    Approved 76 2010-11-20 00:00:00.000

    this goes into a report in SSRS and gives a table of all of the statuses and a snapshot of how many records were in which status that week.

    so it becomes a sequence of statuses each week, rather than a SUM. I would like it to SUM each row with the one prior. Is there any easy way to do this?

  2. #2
    Join Date
    Jun 2005
    you can easily get a running sum to return 1 row, but that is not what you want.

    1) Insert into a temp table w/ an identity column in the order that matters to you
    2) Write the query to get you the sum for all previous records and use that query as a derived table
    3) update your temp table from the derived table query you created in 2 joining on the identity column

  3. #3
    Join Date
    Aug 2010

    Thanks. This is how I went about doing it. Turns out though, that the business requirements were slightly different and it was not needed after all.

    Thanks for the reply.

Tags for this Thread

Posting Permissions

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