To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME
to Month/yy
Here is a example of the script, and screenshots of the output file
This is what I am trying to accomplish.
Any ideas on how to approach this?
SQL SCRIPT:
SELECT A.NAME
COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END)) AS OCT11_ORDERS
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END) AS OCT11_LINES
, COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END)) AS OCT11_INV_COUNT
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END) AS OCT11_INV_LINES
-- NOV 11
, COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END)) AS NOV11_ORDERS
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END) AS NOV11_LINES
, COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END)) AS NOV11_INV_COUNT
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END) AS NOV11_INV_LINES
-- DEC 11
, COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END)) AS DEC11_ORDERS
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END) AS DEC11_LINES
, COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END)) AS DEC11_INV_COUNT
, COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END) AS DEC11_INV_LINES
FROM test.test1 A
WHERE TO_DATE(A.INVDATE) BETWEEN TO_DATE('01-OCT-11') AND TO_DATE('30-SEP-12')
AND COMPANY = '30'
AND CUST_GRP IN ('3','4')
GROUP BY A.NAME
For the desired output, all of the columns should be counts
Thanks!
Best Answer
I figured it out, had to
group by trucn(invdate,'mm')
andcount
the separate columns