How to get cumulative total for previous month and upto this month?

Question

ID  pcID    contractor  approver    claimed
-------------------------------------------
1   1       one          1000         900
2   1       two          200          100
3   1       three        1000        1000
4   1       six          100          11
5   2       six          100          22
6   3       six          120           1
7   4       three        102          10

From the above table, I need to get cumulative amount for upto this month and previous month of approver and claimed and also current month approver, claimed amount based on the contractor. Like below table.

ID  contractor  approver    claimed  uptothisMTApprover   uptothisMTClaimed  previousMTApprover previousMTClaimed 
-----------------------------------------------------------------------------------------------------------------
1    one          1000       900     1000                 900                0                   0
2    two          200        100     200                  100                0                   0
3    three        102        10      1102                 1010               1000                1000
4    six          120        1       320                  34                 200                 33

Thanks in advance..

Answer 1

You seem to want the latest row per contractor, as defined by pcID, and a cumulative sum of all previous months.

You can use window functions:

select contractor, approver, claimed, 
    total_approver            as uptothisMTApprover, 
    total_claimed             as uptothisMTClaimed,
    total_approver - approver as previousMTApprover, 
    total_claimed  - claimed  as previousMTClaimed
from (
    select t.*,
        row_number()  over(partition by contractor order by pcID desc) rn,
        sum(approver) over(partition by contractor) total_approver,
        sum(claimed)  over(partition by contractor) total_claimed
    from mytable t
) t
where rn = 1