CODEWITHSUNDEEP
sqlpostgresql
Sidhu177
Sidhu177

Reputation: 647

Is there a way to get Last Month to Date and Last Month Total SUM

I am trying to get Month To Date(MTD), Last month to Date (LMTD) and Last month (LM) computed from a table that looks like this. Date here is stored as an integer.

+----------+----------+-------+
| Category | Date     | Amount|
+----------+----------+-------+
| A        | 20210608 | 2000  |
+----------+----------+-------+
| A        | 20210607 | 4000  |
+----------+----------+-------+
| A        | 20210606 | 6000  |
+----------+----------+-------+
| A        | 20210605 | 8000  |
+----------+----------+-------+
| B        | 20210608 | 1000  |
+----------+----------+-------+
| B        | 20210607 | 5000  |
+----------+----------+-------+
| B        | 20210606 | 5000  |
+----------+----------+-------+
| B        | 20210605 | 5000  |
+----------+----------+-------+
| C        | 20210608 | 5000  |
+----------+----------+-------+
| C        | 20210607 | 5000  |
+----------+----------+-------+

Now I am able to compute MTD values using the following syntax

SELECT Category 
    ,  date  
    ,  Amount
    ,  SUM(Amount) OVER(PARTITION BY to_CHAR(date, 'YYYY-MM'),Category ORDER BY date DESC) MTD 
FROM table

But I am lost when trying to compute LMTD and LM values. LMTD is the window sum(Amount) from May 1st to May 10th, and LM is the sum for entire May. LMTD is useful to compare with MTD to see if MTD is doing better or not. The challenge is that it has to sum for previous month till exactly from may 1st to may 10th. Is there a way to compute LMTD and LM values using sql ?

+----------+----------+-------+-------+-------+-------+
| Category | Date     | Amount| MTD   | LMTD  | LM    |
+----------+----------+-------+-------+-------+-------+
| A        | 20210608 | 2000  | 20000 | 16000 | 50000 |
+----------+----------+-------+-------+-------+-------+
| A        | 20210607 | 4000  | 18000 | 14000 | 50000 |
+----------+----------+-------+-------+-------+-------+
| A        | 20210606 | 6000  | 14000 | 12000 | 50000 |
+----------+----------+-------+-------+-------+-------+
| A        | 20210605 | 8000  | 8000  | 10000 | 50000 |
+----------+----------+-------+-------+-------+-------+
| B        | 20210608 | 1000  | 16000 | 24000 | 60000 |
+----------+----------+-------+-------+-------+-------+
| B        | 20210607 | 5000  | 15000 | 18000 | 60000 |
+----------+----------+-------+-------+-------+-------+
| B        | 20210606 | 5000  | 10000 | 12000 | 60000 |
+----------+----------+-------+-------+-------+-------+
| B        | 20210605 | 5000  | 5000  | 6000  | 60000 |
+----------+----------+-------+-------+-------+-------+
| C        | 20210608 | 5000  | 10000 | 12000 | 40000 |
+----------+----------+-------+-------+-------+-------+
| C        | 20210607 | 5000  | 5000  | 6000  | 40000 |
+----------+----------+-------+-------+-------+-------+

Upvotes: 0

Views: 538

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

The simplest method might be a lateral join:

select t.*, t2.*
from t left join lateral
     (select sum(t2.amount) as lmon,
             sum(t2.amount) filter (where extract(day from t2.date) <= extract(day from t.date)) as lmtd
      from t t2
      where t2.date < date_trunc('month', t.date) and
            t2.date >= date_trunc('month', t.date) - interval '1 month'
     ) t2
     on 1=1;

Upvotes: 1

Related Questions