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.
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.
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.
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!
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"
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.
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
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.
If I read this suggestion correctly, that would end up creating additional columns that would be blank, so that would not satify my requirement.