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