I have summarize daily data using a basic query and each of them need to be calculated with value of last date from previous month. Example, if I select 3 February 2022, the value should be calculated with the value from 31 January 2022.

This is my current query

SELECT 
    TGL, SUM(A.NOM_IDR) AS TotValue
FROM(
    SELECT 
        B.DATE AS TGL
        , (A.NOMINAL_IDR * -1) as NOM_IDR
    FROM 
        MIS.FACT_LOAN A
    LEFT OUTER JOIN 
        MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
    WHERE 
        YEAR(B.DATE) = '2022'
    )
GROUP BY TGL;

and the result is something like this

TGL        | TotValue 
2022-01-31    300000      
2022-02-01    400000      
2022-02-02    200000 
.
.
.
2022-02-28    370000 
2022-03-01    250000

I already trying to get the last date from each month and their TotValue with this query

SELECT
        B.DATE AS LASTDAYPERMONTH
        , SUM(A.NOMINAL_IDR * -1)
     FROM MIS.FACT_LOAN A
     LEFT JOIN 
        MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
     INNER JOIN
        (SELECT MAX(B.DATE) AS MaxDatePerMonth
         FROM MIS.FACT_LOAN A
         LEFT JOIN 
            MIS.DIM_PERIOD B ON A.SK_PERIOD = B.SK_PERIOD
         WHERE YEAR(B.DATE) = '2022'
         GROUP BY MONTH(B.DATE)
        ) aa ON aa.MaxDatePerMonth = B.DATE
     WHERE YEAR(B.DATE) = '2022'
     GROUP BY B.DATE

But I dont know how to join it with my current query to achieve my desired result. Below is the example of my desired result

TGL        | TotValue | LastMonthValue
2022-01-31    300000         0
2022-02-01    400000      300000
2022-02-02    200000      300000
.
.
.
2022-02-28    370000      300000
2022-03-01    250000      370000

How can I achieve this result? Please help me to find the right query and sorry if the explanation is a bit awkward.

CodePudding user response：

You can first get the previous month by subtracting INTERVAL 1 MONTH from now() and then pass this as the parameter to LAST_DAY:

SELECT LAST_DAY(now() - INTERVAL 1 MONTH)

CodePudding user response：

You can do something like that:

1. Get current date as string using DATE_FORMAT()
2. Get first day of current month using RIGHT() and CONCAT()
3. Convert string to date with STR_TO_DATE()
4. And subtract one day with INTERVAL

`

SELECT STR_TO_DATE(CONCAT('01', RIGHT(DATE_FORMAT(NOW(), '%d/%m/%Y'), 8)),'%d/%m/%Y') - INTERVAL 1 DAY;