CODEWITHSUNDEEP
sqloracle-databasedategroup-by
flightless13wings
flightless13wings

Reputation: 109

Group by Date and Num for Merged Data to Create new Column

I am a beginner in SQL and would like to group by date/num for joined data to create a new column. My data looks like the following after I pulled it: 

select
    items.A,
    items.B,
    items.C,
    items.NUM,
    items.E,
    source.QTY,
    dates.DATE
from original_metric source
inner join grocery items
    on items.id = source.item_id
inner join day_dates dates
    on dates.date_id = source.date_id;

    A        B     C   NUM   E    QTY    DATE       
Vegetable carrot  John  1  Main   14  26-APR-11
Vegetable carrot  John  1  Main   35  27-APR-11
Vegetable carrot  John  1  Main    2  21-SEP-15
Vegetable carrot  John  1  Main   11  23-APR-17
Vegetable carrot  John  1  Main   25  22-MAY-17
Vegetable carrot  John  1  Main   20  20-APR-18
Vegetable onion   John  2  Extra  23  02-AUG-16
Vegetable onion   John  2  Extra  32  07-AUG-16
Meat      pork    Jane  3  Main   10  02-AUG-16
Meat      pork    Jane  3  Main   60  19-JAN-17
Meat      pork    Jane  3  Main   12  25-DEC-17

What I would like it to look like: 

   A         B      C  NUM   E    QTY         DATE           QTR      WEEK_FILL    CURRENT_FILL 
Vegetable carrot  John  1  Main   49  24-APR-11:30-APR-11  2011Q4   none         100%
Vegetable carrot  John  1  Main    2  20-SEP-15:15-SEP-15  2015Q2   none         100% 
Vegetable carrot  John  1  Main   11  23-APR-17:29-APR-17  2017Q4   22.4%        73.4%
Vegetable carrot  John  1  Main   25  21-MAY-17:27-MAY-17  2017Q4   51%          73.4% 
Vegetable carrot  John  1  Main   20  15-APR-18:21-APR-18  2018Q4   80%          80% 
Vegetable onion   John  2  Extra  55  31-JUL-16:07-AUG-16  2016Q1   none         100%
Meat      pork    Jane  3  Main   10  31-JUL-16:07-AUG-16  2016Q1   none         100%
Meat      pork    Jane  3  Main   60  15-JAN-17:21-JAN-17  2017Q3   none         100%
Meat      pork    Jane  3  Main   12  24-DEC-17:30-DEC-17  2017Q3   none         100%

I would like to group QTY by week by NUM. Then based on the DATE, show which QTR it belongs in.

Then, for WEEK_FILL I am trying to incorporate the following logic: QTY (in that week) divide by the sum (all QTY of the same quarter from the previous year) by NUM. If no previous year, go further back until there is a matching quarter. Else, if there are no prior years, then leave it as 'none'.

Then, for CURRENT_FILL, I am trying to incorporate a similar logic: sum(all QTY in that quarter) divide by the sum(all QTY in same quarter from the previous year) by NUM. If no previous year, go further back until there is a matching quarter. Else, if there are no prior years, then leave it as 100%.

(For reference, fiscal calendar Q1: JUN, JUL, AUG; Q2: SEP, OCT, NOV; Q3: DEC, JAN, FEB; Q4: MAR, APR, MAY)

Upvotes: 0

Views: 71

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try this query

WITH dataCTE AS(
  SELECT
    q.*,
    DENSE_RANK()OVER(PARTITION BY q.NUM,q.Q ORDER BY q.Y) Y_ORDER
  FROM
    (
      SELECT
        d.*,
        EXTRACT(YEAR FROM DT) Y,
        -- Q1: JUN, JUL, AUG; Q2: SEP, OCT, NOV; Q3: DEC, JAN, FEB; Q4: MAR, APR, MAY
        DECODE(EXTRACT(MONTH FROM DT),6,1,7,1,8,1,9,2,10,2,11,2,12,3,1,3,2,3,3,4,4,4,5,4) Q
      FROM TEST_DATA d
    ) q
)    
SELECT
  d.*,
  Y||'Q'||Q QTR,
  NVL(TO_CHAR(100*d.QTY/
    (
      SELECT SUM(p.QTY)
      FROM dataCTE p
      WHERE p.Y_ORDER=d.Y_ORDER-1
        AND p.Q=d.Q
        AND p.NUM=d.NUM
    ),'9999.99'),'none') WEEK_FILL,
  NVL(100*SUM(d.QTY)OVER(PARTITION BY d.Y,d.Q ORDER BY d.DT RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)/
    (
      SELECT SUM(p.QTY)
      FROM dataCTE p
      WHERE p.Y_ORDER=d.Y_ORDER-1
        AND p.Q=d.Q
        AND p.NUM=d.NUM
    ),100) CURRENT_FILL
FROM dataCTE d

The second variant

WITH dataCTE AS(
  SELECT
    q.*,
    DENSE_RANK()OVER(PARTITION BY q.NUM,q.Q ORDER BY q.Y) Y_ORDER
  FROM
    (
      SELECT
        d.*,
        EXTRACT(YEAR FROM DT) Y,
        -- Q1: JUN, JUL, AUG; Q2: SEP, OCT, NOV; Q3: DEC, JAN, FEB; Q4: MAR, APR, MAY
        DECODE(EXTRACT(MONTH FROM DT),6,1,7,1,8,1,9,2,10,2,11,2,12,3,1,3,2,3,3,4,4,4,5,4) Q
      FROM TEST_DATA d
    ) q
)
SELECT
  d.*,
  d.Y||'Q'||d.Q QTR,
  NVL(TO_CHAR(100*d.QTY/p.PREV_QTY,'9999.99'),'none') WEEK_FILL,
  NVL(100*SUM(d.QTY)OVER(PARTITION BY d.Y,d.Q ORDER BY d.DT RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)/
      p.PREV_QTY,100) CURRENT_FILL
FROM dataCTE d
LEFT JOIN
  (
    SELECT Y_ORDER,Q,NUM,SUM(QTY) PREV_QTY
    FROM dataCTE
    GROUP BY Y_ORDER,Q,NUM
  ) p
ON p.Y_ORDER=d.Y_ORDER-1 AND p.Q=d.Q AND p.NUM=d.NUM

TEST_DATA is your query

CREATE TABLE TEST_DATA(
  A varchar2(20),
  B varchar2(20),
  C varchar2(20),
  NUM number,
  E varchar2(20),
  QTY number,
  DT date
);

INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main',14,'26-APR-11');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main',35,'27-APR-11');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main', 2,'21-SEP-15');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main',11,'23-APR-17');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main',25,'22-MAY-17');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','carrot','John',1,'Main',20,'20-APR-18');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','onion','John',2,'Extra',23,'02-AUG-16');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Vegetable','onion','John',2,'Extra',32,'07-AUG-16');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Meat','pork','Jane',3,'Main',10,'02-AUG-16');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Meat','pork','Jane',3,'Main',60,'19-JAN-17');
INSERT INTO TEST_DATA(A,B,C,NUM,E,QTY,DT)VALUES('Meat','pork','Jane',3,'Main',12,'25-DEC-17');

SQL Fiddle: 1 - http://sqlfiddle.com/#!4/e7eaa/49 2 - http://sqlfiddle.com/#!4/e7eaa/51

But I think that 80% for 2018Q4 is mistake because 20/(11+25)=55.56

Upvotes: 1

Related Questions