lundi 13 juin 2016

Oracle SQL To Select Month-Wise Products Count For Last 6 Months

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