0

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

Output

Desired result Final Resualt

0

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',
...
  • For the last 3 weeks, you'll probably need a separate query – James Z Feb 1 '15 at 14:59
  • Thanks for the code JamesZ, that is exactly how I wanted. I need away to query the data for end of each quarter of last year without doing a separate query. and another query for the last 3 weeks – Yousef Feb 3 '15 at 4:56
  • I need to modify the code to return the result of end of each quarter. – Yousef Feb 5 '15 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.