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.
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
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.