I want to create a crosstab with months across and company names down.
I have created the following query:
SELECT
SD.DW_CUSTOMER_DIM.CUSTOMER_NAME,
round(SD.DW_SALES_FACT.MONTH_YR/100,0),
SD.DW_SALES_FACT.MONTH_YR,
NVL(SD.DW_SALES_FACT.DOLLAR_AMOUNT, 0.0) FROM
SD.DW_CUSTOMER_DIM,
SD.DW_SALES_FACT
WHERE
(
SD.DW_CUSTOMER_DIM.ACCOUNT_NUMBER=SD.DW_SALES_FACT.ACCOUN T_NUMBER and
SD.DW_CUSTOMER_DIM.SUB_ACCOUNT_NUMBER=SD.DW_SALES_FACT.SU B_ACCOUNT_NUMBER )
AND (
SD.DW_CUSTOMER_DIM.CUSTOMER_NAME LIKE ‘A%’ )
This returns rows where DOLLAR_AMOUNTs for months in which there is an entry. E.G., in
this particular small query, it returns 199801, 199802, 199905, 19906, 199907. Columns
do not exist for other month/year combinations. I would like to show all months, even if
there is no corresponding dollar_amount. In that case, I would like the column to contain
a zero.
I created an Excel spreadsheet containing all possible values for month_yr and linked the 2
data providers on month_yr. Then I unlinked any other links with a third data provider, so
the above link was the only one. I created a crosstab and used customer_name, month_yr and
dollar_amount in the table. I only saw columns for dates that has a corresponding dollar
amount.
Any suggestions?
–
Michael Malone
Senior Consultant
WCI Consulting
Listserv Archives (BOB member since 2002-06-25)