Skip to Content
0
Jan 17, 2008 at 02:24 PM

Query Design Problem

17 Views

I have the following requirement:

Cube A has CHAR1, CHAR2, CHAR3, VALUE1, VALUE2, CALDAY

For a query I want:

Rows: CHAR1, CHAR2

Columns: VALUE1, VALUE2, X

X should be equal to the number of different occurences of CHAR3 (for each CHAR1, CHAR2 combination). I can have for the same CHAR1,CHAR2,CHAR3 combination a lot of records (with diferent CALDAY), but I only to count each different CHAR1,CHAR2,CHAR3 combination. For example with facts table:

A, B, C, 20, 10, 01.01.2008

A, B, D, 10, 20, 02.01.2008

D, E, F, 5 , 15, 01.01.2008

D, E, F, 10 , 20, 02.01.2008

G, H, I, 5, 20, 01.01.2008

The output should be:

CHAR1 CHAR2 VALUE1 VALUE2 X

A B 30 30 2

D E 15 35 1

G H 5 20 1

Can I do this with a query?

Tks