Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sunil Kumar.

Asked: March 13, 2020 - 11:18 pm UTC

Answered by: Chris Saxon - Last updated: March 19, 2020 - 4:01 pm UTC

Category: SQL - Version: 12c

Viewed 1000+ times

You Asked

with t1 as (select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, null period4, 50 period5 from dual 
union all 
select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, null period5 from dual 
union all 
select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual 
union all 
select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, null period3, null period4, 50 period5 from dual 
union all 
select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, null period3, 4 period4, 5 period5 from dual 
union all 
select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual) 
select * from t1



DFU TYPE STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1 01-Jan-2020 10 20 30 - 50
dfu1 4 01-Jan-2020 1 2 3 4 -
dfu1 6 01-Jan-2020 1 1 1 1 1
dfu2 1 01-Jan-2020 40 50 - - 50
dfu2 4 02-Jan-2020 1 2 - 4 5
dfu2 6 03-Jan-2020 1 1 1 1 1


If I take a dfu1 -> it has three records type1, type4, type6

for reach type record -> quantity for 4 periods, period1, period2, period3, period4

based on startdt column, for dfu1:type1:period1 indicates quantity for 01-Jan-2020,
for dfu1:type1:period2 indicates quantity for 01-Jan-2020 + 1 -> which is 02-Jan-2020,
for dfu1:type1:period3 indicates quantity for 01-Jan-2020 + 2 -> which is 03-Jan-2020,
for dfu1:type1:period4 indicates quantity for 01-Jan-2020 + 3 -> which is 04-Jan-2020

and so on... for dfu1:type4, dfu1:type6

now for a dfu1 -> I want to get a sum(qty) group by type,

This can be achieved easily as startdate for a dfu1 is same for all three types.

Whereas as for dfu2

type1, type4 and type6 have three different start dates.

so we cannot just sum (period1) to get period1 value.

for dfu2:type1 -> based on startdt column indicates quantity for 01-Jan-2020, for type 4 and type 6 there is no value for 01-Jan-2020, since type 4 and type 6 startdates and are starting from 02-Jan-2020 and 03-Jan-2020
so dfu2 sum of period 1 will be 40 only

for dfu2 -> period2 value is for 02-Jan-2020 and should calculated as SUM(dfu2:TYPE1:PERIOD2, dfu2:TYPE4:PERIOD1) there is no value for dfu2:type6 for 02-Jan-2020

for dfu2 -> period3 value should be SUM(TYPE1:PERIOD3,TYPE2:PERIOD2,TYPE3:PERIOD1)
for dfu2 -> period4 value should be SUM(TYPE1:PERIOD4,TYPE2:PERIOD3,TYPE3:PERIOD2)
for dfu2 -> period5 value should be SUM(TYPE1:PERIOD5,TYPE2:PERIOD4,TYPE3:PERIOD3)

and so on...

DFU STARTDT PERIOD1 PERIOD2 PERIOD3 PERIOD4 PERIOD5
dfu1 1-Jan-20 12 23 34 5 51      
dfu2 1-Jan-20 40 51 3 1 55


Considerations,

startdate should min(startdate)
if there's no value for a period, should be considered as zero.


I was able to get the output by unpivot query, but looking if there's a possibility to achieve the output without unpivot as in my case, am unpivoting, doing the sum and pivoting again



with LiveSQL Test Case:

and we said...

I'm confused about what exactly is going on here. It feels the table mixes up row and column values... Showing us the pivot/unpivot solution you have would help!

Anyway, to get the output you asked for given the data shown, you could:

- Sum up the values by dfu & startdt
- Use lead to look forward to the next values for each period

Which gives the following rather convoluted query:

with t1 as (
  select 'dfu1' dfu, 1 type, '01-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, 0 period4, 50 period5 from dual 
  union all 
  select 'dfu1' dfu, 4 type, '01-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, 0 period5 from dual 
  union all 
  select 'dfu1' dfu, 6 type, '01-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual 
  union all 
  select 'dfu2' dfu, 1 type, '01-Jan-2020' startdt, 40 period1, 50 period2, 0 period3, 0 period4, 50 period5 from dual 
  union all 
  select 'dfu2' dfu, 4 type, '02-Jan-2020' startdt, 1 period1, 2 period2, 0 period3, 4 period4, 5 period5 from dual 
  union all 
  select 'dfu2' dfu, 6 type, '03-Jan-2020' startdt, 1 period1, 1 period2, 1 period3, 1 period4, 1 period5 from dual
), rws as (
  select dfu, startdt, 
         sum ( period1 ) period1, 
         sum ( period2 ) period2, 
         sum ( period3 ) period3, 
         sum ( period4 ) period4, 
         sum ( period5 ) period5
  from   t1
  group  by dfu, startdt
), tots as (
  select dfu, startdt,
         period1 p1,
         period2 + lead ( 
           period1, 1, 0
         ) over (
           partition by dfu
           order by startdt
         ) p2,
         period3 + lead ( 
           period2, 1, 0
         ) over (
           partition by dfu
           order by startdt
         )  + lead ( 
           period1, 2, 0
         ) over (
           partition by dfu
           order by startdt
         ) p3,
         period4 + lead ( 
           period3, 1, 0
         ) over (
           partition by dfu
           order by startdt
         )  + lead ( 
           period2, 2, 0
         ) over (
           partition by dfu
           order by startdt
         ) p4,
         period5 + lead ( 
           period4, 1, 0
         ) over (
           partition by dfu
           order by startdt
         )  + lead ( 
           period3, 2, 0
         ) over (
           partition by dfu
           order by startdt
         ) p5,
         min ( startdt ) over (
           partition by dfu
         ) mn_dt
  from   rws
)
  select * from tots
  where  startdt = mn_dt;
  
DFU     STARTDT        P1    P2    P3    P4    P5    MN_DT         
dfu1    01-Jan-2020       12    23    34     5    51 01-Jan-2020    
dfu2    01-Jan-2020       40    51     3     1    55 01-Jan-2020  


I'm not convinced this solution meets the business requirement (whatever that is) though. And I'm sure it's more complex than the pivot/unpivot method you have.

Give us more background and we may be able to help better.

and you rated our response

  (3 ratings)

Reviews

March 17, 2020 - 11:59 am UTC

Reviewer: Sunil Kumar Noothi from Australia

       select * 
  FROM (SELECT dfu,
               startdt,
             min_der_startdt,
               sum_qty,
               min_der_startdt - startdt + 1     period
          FROM (  SELECT dfu,
                         startdt,
                         MIN (der_startdt)     min_der_startdt,
                         SUM (qty)             sum_qty
                    FROM (  SELECT dfu,
                                   TYPE,
                                   startdt,
                                   SUBSTR (week, 7, 1)
                                       num,
                                   startdt + TO_NUMBER (SUBSTR (week, 7, 1)) - 1
                                       der_startdt,
                                   qty
                              FROM scpomgr.test_unpivot
                                   UNPIVOT (QTY
                                           FOR WEEK
                                           IN (PERIOD1,
                                              PERIOD2,
                                              PERIOD3,
                                              PERIOD4,
                                              PERIOD5))
                          ORDER BY dfu, TYPE, der_startdt)
                GROUP BY dfu, startdt, der_startdt
                ORDER BY dfu, 2))
;



Above is the unpivot query I prepared and on the top of it I have to pivot the query again by dfu.

https://docs.google.com/spreadsheets/d/1aqyBIGuYdiHjqNV8wo4GTZG-KTayZ0IHImFVXUQwnd4/edit?usp=sharing

I have also shared example in google docs as I can't paste screen shot.
Chris Saxon

Followup  

March 17, 2020 - 3:48 pm UTC

Does the query I gave give the result you want? If not, why not?

March 18, 2020 - 2:56 am UTC

Reviewer: Sunil Kumar Noothi from Australia

for the given data set, your query works perfectly fine, based on the example I have given, query you having gvien assumes startdates will be in one day incremental pattern between type1 type4 and type 6

when I added below new combination - to existing result set, for dfu3 results aren't as expected.

  select 'dfu3' dfu, 1 type, '03-Jan-2020' startdt, 10 period1, 20 period2, 30 period3, 40 period4, 50 period5 from dual
      union all
    select 'dfu3' dfu, 4 type, '06-Jan-2020' startdt, 1 period1, 2 period2, 3 period3, 4 period4, 5 period5 from dual


Current output for dfu3 is

dfu         start_dt                p1      p2     p3   p4   p5    min_dt
dfu3 03-Jan-2020 10 21 32 43 54 03-Jan-2020


but expected output for dfu3 is -

dfu start_dt p1 p2 p3 p4 p5 min_dt
dfu3 03-Jan-2020 10 20 30 41 52 03-Jan-2020

p1 -> value will be coming from dfu3:type1 :p1(03-Jan-2020)
p2 -> value will be coming from dfu3:type1 :p2(04-Jan-2020)
p3 -> value will be coming from dfu3:type1 :p3(05-Jan-2020)
p4 -> value will be coming from dfu3:type1:p4 + dfu3:type4:p1 (06-Jan-2020)
p5 -> value will be coming from dfu3:type1:p5+dfu3:type4:p2(07-Jan-2020)

basically between type1, type4, type 6 periods that should be sum is whatever dates matching based on startdates.

My Apologies if am still not able to explain clearly.


Chris Saxon

Followup  

March 18, 2020 - 11:29 am UTC

What exactly do the PERIODN columns represent?

It seems like they're really referring to dates after the start date. Is that the case?

If so, it's much better to store these as rows, not columns. At the very least, unpivoting as in your solution is the logical first step.

And... if the unpivot query works, why are you trying to change the query?

March 18, 2020 - 11:40 am UTC

Reviewer: Sunil Kumar Noothi from Australia

Yes PeriodN represents date.


I have data in Pivot format,
unpivot
achieve result
and I need data again back in Pivot format.


So, am just seeing if I can avoid unpivot and achieve required functionality.

Chris Saxon

Followup  

March 19, 2020 - 4:01 pm UTC

So, am just seeing if I can avoid unpivot and achieve required functionality.

You probably can. But it's likely to be something highly convoluted, like the query in my original answer. I'd stick with unpivot/pivot.

Or - even better - see if you can get the unpivot data to start with!

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.