Requirement
How can I select/get Month-Year (MON-YY) wise result for every product? I tried to write a query as:
SELECT * FROM
(
SELECT PRODUCT_CODE AS PRODUCT
, EXTRACT (MONTH FROM (UPDATED_DATE)) AS month_num
, PRODUCT_CODE
FROM Test_Pivot
)
PIVOT ( COUNT (PRODUCT_CODE)
FOR month_num IN ( 1 AS jan, 2 AS feb, 3 AS mar, 4 As apr, 5 as may, 6 AS jun,
7 AS jul, 8 AS aug, 9 AS sep, 10 As oct, 11 as nov, 12 AS dec
)
);
I am getting results, but it gives complete count irrespective of year. I am not sure how to modify SQL to restrict counts till last 6 months from current month. I want Months columns to be dynamically displayed as 'MON-YY' instead of just 'MON' as described in sample format below
PRODUCT JAN-16 FEB-16 MAR-16 APR-16 MAY-16 JUN-16
-------------------------------------------------------------
D 1 0 1 0 0 2
A 1 0 2 0 4 1
B 1 0 2 1 1 0
C 0 0 1 0 4 1
- data in above sample is only for description, (June-16 being the current Month)
Sample Table
CREATE TABLE Test_Pivot (
id NUMBER,
customer_id NUMBER,
product_code VARCHAR2(5),
quantity NUMBER,
Updated_date date
);
Sample Data
INSERT INTO Test_Pivot VALUES (1, 1, 'A', 10, sysdate);
INSERT INTO Test_Pivot VALUES (2, 1, 'B', 20, sysdate-50);
INSERT INTO Test_Pivot VALUES (3, 1, 'C', 30, sysdate-90);
INSERT INTO Test_Pivot VALUES (4, 2, 'A', 40, sysdate-75);
INSERT INTO Test_Pivot VALUES (5, 2, 'C', 50, sysdate-25);
INSERT INTO Test_Pivot VALUES (6, 3, 'A', 60, sysdate-20);
INSERT INTO Test_Pivot VALUES (7, 3, 'B', 70, sysdate-80);
INSERT INTO Test_Pivot VALUES (8, 3, 'C', 80, sysdate-40);
INSERT INTO Test_Pivot VALUES (9, 3, 'D', 90, sysdate-5);
INSERT INTO Test_Pivot VALUES (10, 4, 'A', 100, sysdate-35);
INSERT INTO Test_Pivot VALUES (11, 2, 'A', 40, sysdate-75);
INSERT INTO Test_Pivot VALUES (12, 2, 'C', 50, sysdate-25);
INSERT INTO Test_Pivot VALUES (13, 3, 'A', 60, sysdate-20);
INSERT INTO Test_Pivot VALUES (14, 3, 'B', 70, sysdate-80);
INSERT INTO Test_Pivot VALUES (15, 3, 'C', 80, sysdate-40);
INSERT INTO Test_Pivot VALUES (16, 3, 'D', 90, sysdate-5);
INSERT INTO Test_Pivot VALUES (17, 4, 'A', 100, sysdate-35);
INSERT INTO Test_Pivot VALUES (18, 3, 'B', 60, sysdate-400);
INSERT INTO Test_Pivot VALUES (19, 3, 'C', 70, sysdate-365);
INSERT INTO Test_Pivot VALUES (20, 3, 'D', 80, sysdate-450);
INSERT INTO Test_Pivot VALUES (21, 3, 'A', 90, sysdate-500);
INSERT INTO Test_Pivot VALUES (22, 4, 'A', 100, sysdate-555);
INSERT INTO Test_Pivot VALUES (23, 2, 'B', 40, sysdate-543);
INSERT INTO Test_Pivot VALUES (24, 2, 'B', 50, sysdate-150);
INSERT INTO Test_Pivot VALUES (25, 3, 'D', 60, sysdate-151);
Aucun commentaire:
Enregistrer un commentaire