Pivot based on date

Database
Enthusiast

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.

Tags (4)
11 REPLIES
Enthusiast

Re: Pivot based on date

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.

Enthusiast

Re: Pivot based on date

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

Re: Pivot based on date

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.

Enthusiast

Re: Pivot based on date

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

Enthusiast

Re: Pivot based on date

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

Enthusiast

Re: Pivot based on date

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.

Enthusiast

Re: Pivot based on date

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

Enthusiast

Re: Pivot based on date

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.

Enthusiast

Re: Pivot based on date

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