View Single Post
 
Old March 26th, 2007, 10:29 AM
tnzabo tnzabo is offline
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've found a way to solve this. Here is the code that worked...

CREATE OR REPLACE VIEW FRT_SALES AS
SELECT CUSTOMER,
        PROJECT,
        SUM(CASE WHEN P.Month = 1 THEN P.Amount ELSE 0 END) AS OCT,
        SUM(CASE WHEN P.Month = 2 THEN P.Amount ELSE 0 END) AS NOV,
        SUM(CASE WHEN P.Month = 3 THEN P.Amount ELSE 0 END) AS DEC,
        SUM(CASE WHEN P.Month = 4 THEN P.Amount ELSE 0 END) AS JAN,
        SUM(CASE WHEN P.Month = 5 THEN P.Amount ELSE 0 END) AS FEB,
        SUM(CASE WHEN P.Month = 6 THEN P.Amount ELSE 0 END) AS MAR,
        SUM(CASE WHEN P.Month = 7 THEN P.Amount ELSE 0 END) AS APR,
        SUM(CASE WHEN P.Month = 8 THEN P.Amount ELSE 0 END) AS MAY,
        SUM(CASE WHEN P.Month = 9 THEN P.Amount ELSE 0 END) AS JUN,
        SUM(CASE WHEN P.Month = 10 THEN P.Amount ELSE 0 END) AS JUL,
        SUM(CASE WHEN P.Month = 11 THEN P.Amount ELSE 0 END) AS AUG,
        SUM(CASE WHEN P.Month = 12 THEN P.Amount ELSE 0 END) AS SEP
        FROM (SELECT CUSTOMER,
                        PROJECT,
                        CAST(AMOUNT AS float) AS AMOUNT,
                        TO_CHAR(INVOICEDATE, 'MM') as MONTH
                FROM (SELECT T$CUNO as CUSTOMER,
                                T$CPRJ as PROJECT,
                                T$INVD as INVOICEDATE,
                                T$INVN as INVOICENUMBER,
                                T$AMNT as AMOUNT,
                                T$ITEM as ITEM
                           FROM TTDSLS045400
                        WHERE T$ITEM <> 'FRTSALES'
                        AND T$INVN > 0
                        AND T$AMNT > 0
                        AND T$INVD BETWEEN '2006-10-01'
                        AND '2007-09-30')) P
GROUP BY Project, CUSTOMER