November 12, 2001 at 10:09 am
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!
November 12, 2001 at 10:12 am
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
November 12, 2001 at 10:28 am
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.
November 12, 2001 at 11:03 am
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
November 12, 2001 at 12:02 pm
102010200.002.00
113021500.002.38
124033900.002.73
1350461400.003.04
185641450.002.27
228861530.001.78
15101011630.001.61
2061211690.001.40
35121561810.001.16
11301672110.001.26
12401792510.001.40
13501923013.851.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!
November 12, 2001 at 1:22 pm
You are welcome. I assume you can enhance my script for the other columns.
POst again if you have more questions.
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply