Pivot by Cumulative Sum

Database
Enthusiast

Pivot by Cumulative Sum

I need to pivot a table based on month and cumulative sum of the quantity. I have given the below table and expected result.  There may be few Months missing for a given date and state_Cd in the data. While pivoting, I should take a cumulative sum of the quantity across months. Can you please help me with the Teradata SQL query? I am using TD 14.

 

DateState_CDMonthQuantity
1/11/161234010
1/11/161234120
1/11/161234230
1/11/161234310
1/11/161234450
1/11/161234520
11/26/185556110
11/26/185556320

 

Expected Result:

datestate_cdmonth 0month 1month 2month 3month 4month 5
1/11/16123410306070120140
11/26/18555601010303030

Accepted Solutions
Highlighted
Junior Contributor

Re: Pivot by Cumulative Sum

Hi @Waldar, maybe I didn't understand the question correctly, but a simple Pivot query returns the same result without any additional step:

SELECT TheDate, State_Cd
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 0 THEN Quantity ELSE 0 end) AS Month_0
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 1 THEN Quantity ELSE 0 end) AS Month_1
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 2 THEN Quantity ELSE 0 end) AS Month_2
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 3 THEN Quantity ELSE 0 end) AS Month_3
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 4 THEN Quantity ELSE 0 end) AS Month_4
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 5 THEN Quantity ELSE 0 end) AS Month_5
FROM mvt_datas
GROUP BY TheDate, State_Cd

 

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Pivot by Cumulative Sum

Hi vinosql,

 

I've done your query using several techniques.

The first one is to create a complete dataset of Date, State and Month.

The second one is to compute the cumulative sum.

The last one is the pivot.

 

Data

create multiset volatile table mvt_datas, no log
( TheDate       date format 'yyyy-mm-dd'
, State_Cd      integer
, Month_Id      byteint
, Quantity      smallint
)
primary index (TheDate, State_Cd)
on commit preserve rows;

insert into mvt_datas values (date '2016-01-11', 1234, 0, 10);
insert into mvt_datas values (date '2016-01-11', 1234, 1, 20);
insert into mvt_datas values (date '2016-01-11', 1234, 2, 30);
insert into mvt_datas values (date '2016-01-11', 1234, 3, 10);
insert into mvt_datas values (date '2016-01-11', 1234, 4, 50);
insert into mvt_datas values (date '2016-01-11', 1234, 5, 20);
insert into mvt_datas values (date '2018-11-26', 5556, 1, 10);
insert into mvt_datas values (date '2018-11-26', 5556, 3, 20);

Query 

with cte_cumumative_sum (TheDate, State_Cd, Month_Id, Quantity) as
(
    select cte.TheDate, cte.State_Cd, cte.Month_Id
         , sum(coalesce(mvt.Quantity, 0)) over(partition by cte.TheDate, cte.State_Cd
                                                   order by cte.Month_Id asc rows between unbounded preceding and current row)
      from cte_expand as cte
 left join mvt_datas  as mvt  on mvt.TheDate  = cte.TheDate
                             and mvt.State_Cd = cte.State_Cd
                             and mvt.Month_Id = cte.Month_Id
)
  ,  cte_expand (TheDate, State_Cd, Month_Id) as
(
select TheDate
     , State_Cd
     , begin(prd) - current_date
  from cte_distinct_axes
expand on period(current_date, current_date + 6) as prd by interval '1' day
)
  ,  cte_distinct_axes (TheDate, State_Cd) as
(
  select TheDate, State_Cd
    from mvt_datas
group by TheDate, State_Cd
)
  select TheDate, State_Cd
       , max(case Month_Id when 0 then Quantity else 0 end) as Month_0
       , max(case Month_Id when 1 then Quantity else 0 end) as Month_1
       , max(case Month_Id when 2 then Quantity else 0 end) as Month_2
       , max(case Month_Id when 3 then Quantity else 0 end) as Month_3
       , max(case Month_Id when 4 then Quantity else 0 end) as Month_4
       , max(case Month_Id when 5 then Quantity else 0 end) as Month_5
    from cte_cumumative_sum
group by TheDate, State_Cd;

 

 

Enthusiast

Re: Pivot by Cumulative Sum

I do not the get the expected result from the query and I do not want to expand the date field as well.

Highlighted
Junior Contributor

Re: Pivot by Cumulative Sum

Hi @Waldar, maybe I didn't understand the question correctly, but a simple Pivot query returns the same result without any additional step:

SELECT TheDate, State_Cd
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 0 THEN Quantity ELSE 0 end) AS Month_0
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 1 THEN Quantity ELSE 0 end) AS Month_1
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 2 THEN Quantity ELSE 0 end) AS Month_2
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 3 THEN Quantity ELSE 0 end) AS Month_3
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 4 THEN Quantity ELSE 0 end) AS Month_4
  ,Sum(CASE WHEN Month_Id BETWEEN 0 AND 5 THEN Quantity ELSE 0 end) AS Month_5
FROM mvt_datas
GROUP BY TheDate, State_Cd

 

Teradata Employee

Re: Pivot by Cumulative Sum

Way much better :)