Partial Sum based on conditions

[snyderbr]

Hello,

I posted this in another topic but realize it might fit better here.  I am using Report Studio to create a report based on DB2 data in z/OS.

First, I appreciate any help in advance.  I am trying to get a conditional total for one column of my database based on another.  Essentially, if I have two columns, COL1 and COL2, I want to sum up all the COL2 values for all rows where their COL1 value is greater than the current row.  In my non-SQL mind it looks something like this:

sum(COL2 for all other rows where COL1 > this row's COL1)

There is a second part of this, and that is my database has a date/time component, every row has a date and time as key columns, and I only want to do the addition for all rows with the same date/time as the current row.

So basically I want 6 columns: select date, time, row_name, COL1, COL2, conditional_sum(listed above)

I have tried using CASE and IFs with TOTAL, but just cannot get the query to return the proper results.  My next step would be to filter the results so that I only pick certain row_names, but this should not affect the conditional sum.  I believe I have to set the filters to 'after auto aggregation' in Cognos for this.

Thank you!

Brad

[snyderbr]

I don't know if this helps clarify, but I got the base query running on my z/OS DB2 database, I just can't get it translated to COGNOS.  The DB2 query looks like this:

SELECT "TABLE_1"."DATE" "DATE1"                                     
, "TABLE_1"."TIME" "TIME2", "TABLE_1"."KEYCOL1"       
"KEYCOL1_NAME", "TABLE_1"."KEYCOL2" "KEYCOL2_NAME"           
, "TABLE_1"."MEASURE_1" "MEASURE_1"                           
, "TABLE_1"."TIME_USED" "TIME_USED"                                       
,(SELECT SUM(TIME_USED) FROM DSNX.TABLE_1 X WHERE X.DATE =         
TABLE_1.DATE AND X.TIME = TABLE_1.TIME AND X.MEASURE_1 >
TABLE_1.MEASURE_1) AS SUMMED_TIME_USED_GT                               
FROM DSNX."TABLE_1" "TABLE_1"                           
FOR FETCH ONLY               

So my question is, how do I create the column named 'SUMMED_TIME+USED_GT' in COGNOS that does the same thing?

Any help would be appreciated!

Brad