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