Detail and Running Totals w/calculation

  • robinlostutter

    SSC Rookie

    Points: 41

    I have two detail columns that needs a column which accumulates for each and one that adds the last 12 months, also a column that takes those two totals and does a calculation. Can I do this in a sql query? Thank you!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    Can you post more information, samples? Not completely sure what you awnt.

    You can use a computed comlumn to compute these on the fly. Or a trigger that performs the update.

    Steve Jones

    steve@dkranch.net

  • robinlostutter

    SSC Rookie

    Points: 41

    I'm not sure how to post samples. What I have are 2 detail columns, columns which accumulate each of these, then a column which takes the cumulative total of one and divides by the cumulative total of the other and another column that takes the sum of the previous 12 months of one and divides by the sum of the previous 12 months of the other. Including the date column - a total of 7. I need to graph on the two calculated columns. Does that make sense?

    I would like to display this in an asp page but am unsure how to write the sql. Thanks for your help.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    Let me try to understand. It's easier to have you post the DDL to create the table and some sample data.

    I think you have this (sample post):

    -- drop table MyTest

    create table MyTest

    ( EntryDate datetime

    , Detail1 int

    , Detail2 int

    , Detail1Sum int

    , Detail2Sum int

    , TwelveMonthDiff numeric

    , CummDiff numeric

    )

    go

    insert MyTest select '01/01/00', 10, 20, 0, 0, 0, 0

    insert MyTest select '02/01/00', 11, 30, 0, 0, 0, 0

    insert MyTest select '03/01/00', 12, 40, 0, 0, 0, 0

    insert MyTest select '04/01/00', 13, 50, 0, 0, 0, 0

    insert MyTest select '05/01/00', 14, 60, 0, 0, 0, 0

    insert MyTest select '06/01/00', 15, 70, 0, 0, 0, 0

    insert MyTest select '07/01/00', 16, 80, 0, 0, 0, 0

    insert MyTest select '08/01/00', 17, 90, 0, 0, 0, 0

    insert MyTest select '09/01/00', 18, 100, 0, 0, 0, 0

    insert MyTest select '10/01/00', 19, 110, 0, 0, 0, 0

    insert MyTest select '11/01/00', 20, 120, 0, 0, 0, 0

    insert MyTest select '12/01/00', 21, 130, 0, 0, 0, 0

    insert MyTest select '01/01/01', 10, 20, 0, 0, 0, 0

    insert MyTest select '02/01/01', 11, 30, 0, 0, 0, 0

    insert MyTest select '03/01/01', 12, 40, 0, 0, 0, 0

    insert MyTest select '04/01/01', 13, 50, 0, 0, 0, 0

    insert MyTest select '05/01/01', 14, 60, 0, 0, 0, 0

    insert MyTest select '06/01/01', 15, 70, 0, 0, 0, 0

    insert MyTest select '07/01/01', 16, 80, 0, 0, 0, 0

    insert MyTest select '08/01/01', 17, 90, 0, 0, 0, 0

    insert MyTest select '09/01/01', 18, 100, 0, 0, 0, 0

    go

    select * from MyTest

    go

    update Mytest

    set detail1sum = b.MySum

    from (

    select

    a.entrydate

    , sum( b.detail1) 'MySum'

    from MyTest a, MyTest b

    where b.entrydate <= a.entrydate

    -- and c.entrydate <= a.entrydate

    group by a.entrydate

    ) b

    where MyTest.entrydate = b.EntryDate

    update Mytest

    set detail2sum = b.MySum

    from (

    select

    a.entrydate

    , sum( b.detail2) 'MySum'

    from MyTest a, MyTest b

    where b.entrydate <= a.entrydate

    -- and c.entrydate <= a.entrydate

    group by a.entrydate

    ) b

    where MyTest.entrydate = b.EntryDate

    go

    select * from MyTest

    go

    --- Sample Output

    EntryDate Detail1 Detail2 Detail1Sum Detail2Sum TwelveMonthDiff CummDiff

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

    2000-01-01 10 20 10 20 0 0

    2000-02-01 11 30 21 50 0 0

    2000-03-01 12 40 33 90 0 0

    2000-04-01 13 50 46 140 0 0

    Is this correct? what values should we see for the other columns?

    Steve Jones

    steve@dkranch.net

  • robinlostutter

    SSC Rookie

    Points: 41

    10 20 10 20 0.00 2.00

    11 30 21 50 0.00 2.38

    12 40 33 90 0.00 2.73

    13 50 46 140 0.00 3.04

    18 5 64 145 0.00 2.27

    22 8 86 153 0.00 1.78

    15 10 101 163 0.00 1.61

    20 6 121 169 0.00 1.40

    35 12 156 181 0.00 1.16

    11 30 167 211 0.00 1.26

    12 40 179 251 0.00 1.40

    13 50 192 301 3.85 1.57

    Steve,

    Your calculations were correct. The 5th column is based on an annual calculation so is blank until there are 12 months of data then sums the current and last 11 months of data for column 2 and divides by the sum of the current and last 11 months of data for column 1, the sixth column just divides column 4 by column 3. Thank you!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    You are welcome. I assume you can enhance my script for the other columns.

    POst again if you have more questions.

    Steve Jones

    steve@dkranch.net

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply