All Forums Database
gmoney 23 posts Joined 12/10
08 Jul 2015
Pivot based on date

Good day all,
I have data that consists of 3 months of charges for each line item.
Bill_No                Bill_Month                     Charge
00121       4/10/2015 12:00:00 AM           1300.00
00121       5/10/2015 12:00:00 AM           1300.00
00121      6/10/2015 12:00:00 AM            1400.00
 
I would like to pivot the data so that my result would be as such:
Bill_No     April     May   June
00121     1300     1300  1400 
 
Any assistance suggestions would be appreciated.
 

gmoney 23 posts Joined 12/10
08 Jul 2015

i do understand that I will need to use cast((cast Bill_Month as format 'mmm')) as char(3)) as Bill_Month to do the conversion for that part.
I am guessing that case may be needed with rank for the actual pivioting function, but I am just not sure.
Thanks for looking.

manib0907 61 posts Joined 04/15
08 Jul 2015

Yes case with rank and max function will work.. But do you have the max limit of the rank?

Cheers,
Mani

gmoney 23 posts Joined 12/10
09 Jul 2015

Not really unless the month its self would be used. Of course that would change every month as well since I need to capture the current and pst 2 months. I also have no idea what the count of Bill_No would be as those change monthly as well.

gmoney 23 posts Joined 12/10
09 Jul 2015

Any suggestions on the script to use to pivot these dates would be appreciated. Teradata is not my strong suit to any degree.
 
Thanks for looking
 

gmoney 23 posts Joined 12/10
09 Jul 2015
select BILL_NO,
  max(case when Bill_Month ='Apr' then MNTH_MRC end) as April,
  max(case when Bill_Month ='May' then MNTH_MRC end) as May,
  max(case when Bill_Month ='Jun' then MNTH_MRC end) as June,
  max(case when Bill_Month ='Jul' then MNTH_MRC end) as July

I have tried the above then I would have to hardcode the months in. I do not want to do that as I will only display 3 months at a time in the pivot and it needs to be dynamic based on the month the query is run going back the previous 3 months to capture the Charges.
Any assistance would be appreciated - Thanks!
G
 
 

gmoney 23 posts Joined 12/10
09 Jul 2015
select BILL_NO, CIRCUIT_NO,CYCLE_NO,
  max(case when Bill_Month = cast ((cast(ADD_MONTHS (CURRENT_DATE, -3)as format 'mmm'))as char(3))
    then MNTH_MRC end) as "3rd_Mnth",
  max(case when Bill_Month = cast ((cast(ADD_MONTHS (CURRENT_DATE, -2)as format 'mmm'))as char(3))
    then MNTH_MRC end) as "2nd_Mnth",
  max(case when Bill_Month = cast ((cast(ADD_MONTHS (CURRENT_DATE, -1)as format 'mmm'))as char(3))
    then MNTH_MRC end) as "1st_Mnth"
from

Well no responses yet - I am a little closer on this. I am guessing within teradata I cannot use a subquery to determine the actual month name after each case statement. I attempted to use something like
(select cast...  )as above to get the actual name but Teradata did not like that much. Everyone must be vacationing - slow on getting any responses.
 
 

gmoney 23 posts Joined 12/10
10 Jul 2015

Can anyone give me some direction on how to get the dates (months) to be dynamic in this?
I could really use some assistance.
 
Thanks

manib0907 61 posts Joined 04/15
10 Jul 2015

There can be atmost 12 unique entries.. i.e if you take the case of 1 entry per month.
Probably what u can do is u can assign values based upon the month i.e from 1 to 12
use that in the case when and get values
SELECT BILL_NO,
MAX(CASE WHEN MONTH_NUM=1 THEN BILL_AMT ELSE NULL) END AS JANUARY,
MAX(CASE WHEN MONTH_NUM=2 THEN BILL_AMT ELSE NULL) END AS FEBRUARY, and so on from (
(SELECT BILL_NO ,MONTH_NUM,MAX(BILL_AMT) AS BILL_AMT FROM
 (SEL BILL_NO , EXTRACT (MONTH FROM BILL_MONTH) AS MONTH_NUM, BILL_AMT FROM TABLE) b
GROUP BY BILL_NO,MONTH_NUM)) a
 
Try this , I am not sure whether i understood ur requirement. But try like this.

Cheers,
Mani

gmoney 23 posts Joined 12/10
15 Jul 2015

If I read this suggestion correctly, that would end up creating additional columns that would be blank, so that would not satify my requirement.

manib0907 61 posts Joined 04/15
15 Jul 2015

so how would you accomodate if you have july august and september entry for another bill no along with ur existing records??.. that will clarify your requirement better

Cheers,
Mani

dnoeth 4628 posts Joined 11/04
15 Jul 2015

Your last query was as good as possible, there's no way to get the column names dynamically (you must use Dynamic SQL in a Stored Procedure). 
Assuming that Bill_Month is a DATE or TIMESTAMP you should avoid the cast to a string. And if you don't want to mix the same month from different years you can use this: 

SELECT BILL_NO,
  MAX(CASE WHEN Bill_Month BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE, -3)) 
                               AND LAST_DAY(ADD_MONTHS(CURRENT_DATE, -3))
           THEN BILL_AMT
      END) AS "3rd_Mnth",
  MAX(CASE WHEN Bill_Month BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE, -2)) 
                               AND LAST_DAY(ADD_MONTHS(CURRENT_DATE, -2))
           THEN BILL_AMT
      END) AS "2nd_Mnth",
  MAX(CASE WHEN Bill_Month BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE, -1)) 
                               AND LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1))
           THEN BILL_AMT
      END) AS "1st_Mnth"
FROM ...
WHERE Bill_Month BETWEEN LAST_DAY(ADD_MONTHS (CURRENT_DATE, -4) + 1
                     AND LAST_DAY(ADD_MONTHS (CURRENT_DATE, -1)

 

Dieter

You must sign in to leave a comment.