Xrhrft

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks.

Also, if it is possible, I want to have the type as a separate column with the type as the header.

This is the query I am using right now which is giving the result for last month.

Select 

  to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') 

    || '-'

    || to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,



  case

     when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and

          AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then

      'AUM'

     when (AP.ASSET_TYPE like 'CFTD%') then

      'Depostis'

     when (AP.ASSET_TYPE like 'CASH%') then

      'Cash'

     when (AP.ASSET_TYPE = 'FTL') then

      'Loan'

   end as Type,



   sum(ABS(AP.Eval_Market_Value)) as Total



from (select p.account,

           p.open_date    as Open_Date,

           ac.description as RM,

           s.*



      FROM k$portfolio p, k$client k

      LEFT JOIN k$client_role cr

        ON cr.client = k.id

       AND cr.role = 136

      LEFT JOIN k$actors ac

        ON cr.actor = ac.id,

     table(gtw$reporting.Statement(p.id,

                                      2,

                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),

                                      trunc(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1)),

                                      1,

                                      1002,

                                      1,

                                      'USD')) s

     wHERE s.line_type = 'P'

       and k.id = p.client

       and p.id = s.portfolio

       and p.portfolio_type = 'C'

       and p.status = 1



    ) ap





group by to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY'),



      case

        when (ap.asset = 2 or AP.ASSET_TYPE not like 'CFTD%' and

             AP.ASSET_TYPE not like 'CASH%' and AP.ASSET_TYPE != 'FTL') then

         'AUM'

        when (AP.ASSET_TYPE like 'CFTD%') then

         'Depostis'

        when (AP.ASSET_TYPE like 'CASH%')  then

         'Cash'

     when (AP.ASSET_TYPE = 'FTL') then

         'Loan'

      end

=====================

Current output

Desired result

I'm not sure if I understood the problem correctly, but maybe you're looking for something like this?

Select 

to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'MM') ||'-'|| to_char(add_months(last_day(TO_DATE(&DTE,'YYYYMMDD')),-1), 'YYYY') AS Month,

sum(case when (AP.ASSET_TYPE like 'CFTD%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Depostis',

sum(case when (AP.ASSET_TYPE like 'CASH%') then ABS(AP.Eval_Market_Value) else 0 end) as 'Cash',

sum(case when (AP.ASSET_TYPE = 'FTL') then ABS(AP.Eval_Market_Value) else 0 end) as 'Loan',

...