Complicated Sum

[pawlikj]

The table I'm working in has 4 columns that I am worried about: Program, direct, df_date and class.

What I need to do is a sum of direct with a group_by program and df_date where class = 'AC'. Simple enough. However, the values I need to pull for the sum need to be a sum of all items on that date and all previous dates.

So sum(direct at Date1) = sum(direct at Date 1)
sum(direct at Date2) = sum(direct at Date 2) + sum(direct at Date 1)
etc.

Any tips would be very helpful.

John

 

[rac2]

Is this a situation where it would be useful to create a secondary table for reporting purposes? Calculate the accumulated sums one time, store them in the reporting table, then use that table for reports.

Because as the days pass it gets more and more expensive to accumulate the values for all of the dates prior to every date.

 

[pawlikj]

No, it won't. It is possible for projects (users) to fix past data. While this has caused much consternation for those trying to build reports it is allowed to happen.

At any one run of the query it would only be getting data for one Program. So there will probably be another part of the Where - Program = "PID". Right now I'm just trying to see if this is a concept I should try and pursue (sum on the fly) or if I should try and find a different way.

 

[pawlikj]

Also, this is going to be part of a coldfusion page, which is why I would like to do it with one query.

 

[r937]

select t1.Program
     , t1.df_date
     , sum(t2.direct) as running_sum
  from daTable as t1
inner
  join daTable as t2
    on t2.Program = t1.Program
   and t2.class   = t1.class
   and t2.df_date <= t1.df_date
 where t1.Program = 'PID'
   and t1.class = 'AC'
group
    by t1.Program
     , t1.df_date

r937.com | rudy.ca

 

[pawlikj]

The data I get back when I run

SELECT program, df_date, SUM(direct) AS acwp FROM opp.tphase WHERE class = 'AC' AND program = 'E01043' GROUP BY program, df_date

is:
Program DF_Date ACWP
E01043 10/30/2005 1204.55
E01043 11/27/2005 711.08
E01043 12/25/2005 26.71

When I run your query, it should spit out:
Program DF_Date ACWP
E01043 10/30/2005 1204.55
E01043 11/27/2005 1915.63 <-----1204.55 + 711.08
E01043 12/25/2005 1942.34 <-----1204.55 + 711.08 + 26.71

What I am getting though is:
Program DF_Date ACWP
E01043 10/30/2005 3613.65
E01043 11/27/2005 5746.89
E01043 12/25/2005 5827.02

It is giving me 3 times the value it should be.

 

[r937]

okay, i think i understand why you're getting inflated results

my query will work if the table contains one row per Program/class

does your database system allow views?

CREATE VIEW daTable AS
SELECT program, df_date, SUM(direct) AS acwp
FROM opp.tphase WHERE class = 'AC' AND program = 'E01043'
GROUP BY program, df_date

then replace direct in my query with acwp


r937.com | rudy.ca

 

[pawlikj]

That worked. Instead of doing a view though, I dropped that select in as a sub-query. Its kinda nasty, and I'll try and clean it up, but it works.