CODEWITHSUNDEEP
sqloracle-database
ex-patriot
ex-patriot

Reputation: 97

SQL Oracle/Aggregation query

I'm trying to run a query on Oracle. I've a table of settled payments for accounts, I have a query which pulls through the last three settled amounts, plus any amount which was written off, for any account I need this info for.

However, some of the accounts are weekly based, and for these I would like to aggregate their weekly settlements into their monthly groups. Here is the code I have so far:

SELECT   *
FROM     (
    SELECT *
    FROM   (
            SELECT   gwod.account_id,
                     gwod.charge_period_start,
                     SUM(gwod.total_due_on_charge) total_due_on_charge,
                     SUM(gwod.amount_written_off) amount_written_off,
                     DENSE_RANK() over (PARTITION BY gwod.account_id 
                                        ORDER BY charge_period_start DESC) rownumber
            FROM     report.accounts_write_off gwod
            WHERE    account_id IN (‘account_number’)
            GROUP BY gwod.account_id,
                     gwod.charge_period_start
            HAVING   SUM (gwod.total_due_on_charge) <> 0) t1
    WHERE  t1.rownumber <=3) 
    PIVOT (MAX(charge_period_start) charge_period, 
           MAX(total_due_on_charge) total_due_on_charge, 
           MAX(amount_written_off) amount_written_off 
           FOR rownumber IN (1,2,3))
ORDER BY account_id.*

This works perfectly but for the weekly based accounts, so rather than pulling through the last three weekly amounts which were settled, i.e. 25-09-17, 18-09-17, 11-09-2017, I'd like to pull through the aggregated payments for September, August, and July.

I hope all this makes sense.

Upvotes: 2

Views: 68

Answers (1)

Parfait
Parfait

Reputation: 107567

Simply change your aggregation from current unit level (i.e., weekly) to month level with EXTRACT(month ...) in inner query's SELECT and GROUP BY as well as PARTITION and PIVOT clauses:

SELECT   *
FROM     (
    SELECT *
    FROM   (
            SELECT   gwod.account_id,
                     EXTRACT(month FROM gwod.charge_period_start) charge_period_month,
                     SUM(gwod.total_due_on_charge) total_due_on_charge,
                     SUM(gwod.amount_written_off) amount_written_off,
                     DENSE_RANK() over (PARTITION BY gwod.account_id 
                                        ORDER BY EXTRACT(month FROM gwod.charge_period_start) DESC) rownumber
            FROM     report.accounts_write_off gwod
            WHERE    account_id IN ('account_number')
            GROUP BY gwod.account_id,
                     EXTRACT(month FROM gwod.charge_period_start)
            HAVING   SUM (gwod.total_due_on_charge) <> 0) t1
    WHERE  t1.rownumber <=3) 
    PIVOT (MAX(charge_period_month) charge_period, 
           MAX(total_due_on_charge) total_due_on_charge, 
           MAX(amount_written_off) amount_written_off 
           FOR rownumber IN (1,2,3))
ORDER BY account_id.*

DEMO (with random data):

http://rextester.com/UJK84858

Upvotes: 0

Related Questions