CODEWITHSUNDEEP
sqloracle-databasedatesubquery
quinsard99
quinsard99

Reputation: 63

Get current data and last month data for same column

I'm comparing values for AMOUNT for PROJECT_ID for November(11) and October(10) in Oracle SQL.

I have tried sub query but AMOUNT_LAST_MONTH shows the same result.

select 
    PROJECT_ID, 
    sum(AMOUNT), 
    ( 
        select sum(amount) 
        from APPS.pa_draft_revenue_items
        where 
            to_char(LAST_UPDATE_DATE,'MM')='10' 
            AND to_char(LAST_UPDATE_DATE,'YYYY')='2019'
    ) AMOUNT_LAST_MONTH
from 
    APPS.pa_draft_revenue_items 
where 
    PROJECT_ID IN (
        select PROJECT_ID 
        from APPS.pa_draft_revenue_items
        where 
            to_char(LAST_UPDATE_DATE,'MM')='11' 
            AND to_char(LAST_UPDATE_DATE,'YYYY')='2019' 
    ) 
GROUP by PROJECT_ID, amount ;

I expect AMOUNT_LAST_MONTH to be to have different values.

enter image description here

Upvotes: 0

Views: 100

Answers (3)

Popeye
Popeye

Reputation: 35900

I would insist you to use sysdate as it will become generic then, as following:

select 
    project_id, 
    sum(case when trunc(last_update_date,'month') = trunc(add_months(sysdate, -1),'month') then amount end) amount_last_month, 
    sum(case when trunc(last_update_date,'month') = trunc(sysdate,'month') then amount end) amount_this_month
from apps.pa_draft_revenue_items
where 
    last_update_date >= trunc(add_months(sysdate, -1),'month')
    and last_update_date < trunc(add_months(sysdate, 1),'month')
group by project_id

Cheers!!

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

You must link the result set of subquery with a row of main query, the link is represented by project_id

Your changed query:

select 
    PROJECT_ID, 
    sum(AMOUNT), 
    ( 
        select sum(amount) 
        from APPS.pa_draft_revenue_items lastmonth
        where to_char(LAST_UPDATE_DATE,'MM')='10' 
        AND to_char(LAST_UPDATE_DATE,'YYYY')='2019'
        AND lastmonth.project_id = main.project_id
    ) AMOUNT_LAST_MONTH
from APPS.pa_draft_revenue_items main
where PROJECT_ID IN (
    select PROJECT_ID 
    from APPS.pa_draft_revenue_items
    where 
        to_char(LAST_UPDATE_DATE,'MM')='11' 
        AND to_char(LAST_UPDATE_DATE,'YYYY')='2019' 
) 
GROUP by PROJECT_ID, amount ;

Upvotes: 0

GMB
GMB

Reputation: 222402

If I followd you correctly, you query can be greatly simplified by using conditional aggregation:

select 
    project_id, 
    sum(case when extract(month from last_update_date) = 10 then amount end) amount_last_month, 
    sum(case when extract(month from last_update_date) = 11 then amount end) amount_this_month
from apps.pa_draft_revenue_items
where 
    last_update_date >= to_date('2019-10-01', 'yyyy-mm-dd')
    and last_update_date < to_date('2019-12-01', 'yyyy-mm-dd')
group by project_id

Upvotes: 1

Related Questions