Pivot based on date

[gmoney]

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]

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]

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

[gmoney]

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]

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]

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]

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]

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]

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.

[gmoney]

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