Getting sum result for last year divided quarterly

0 votes
1 answer
255 views
                          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


                        
Asked by Yousef (13 rep)
Feb 1, 2015, 07:43 AM
Last activity: May 18, 2025, 11:06 PM
Getting sum result for last year divided quarterly

Related Questions
