SQL Get Change from Previous Month

In this article, we’ll explore how to use SQL window functions to extract the net and change values from previous month for a given date range. We’ll start by examining the requirements of the problem and then move on to a step-by-step solution.

Requirements

We have two tables: ClientTable and ClientValues. The ClientTable contains information about clients, supervisors, managers, dates, and other non-relevant columns. The ClientValues table contains additional data for each client, including values, dates, and manager IDs.

We need to retrieve the number of supervisors, clients, value, and change from previous month results grouped by end-of-month date and manager ID. Specifically, we want to calculate:

Number of supervisors for each manager
Number of clients for each manager
Total value for each manager
Change in value from the previous month for each manager

Initial Query Attempt

The original query attempts to extract this information using a subquery and EOMONTH function.

SELECT
    EoMDate, 
    ManagerId,
    Supervisors,
    Clients,
    Value,
    Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) as ChangeValue,
    CAST(100*(Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate))/LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS DECIMAL(5,2)) as ChangePerc
FROM
    (
    SELECT
        EOMONTH(cv.[ValueDate]) AS EoMDate,
        cv.ManagerId,
        (
            SELECT COUNT(DISTINCT ct.SupervisorId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])&lt;=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        )  as Supervisors,
        (
            SELECT COUNT(DISTINCT ct.ClientId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])&lt;=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        ) as Clients,
        SUM(cv.Value) as Value
    FROM
        ClientValues cv
    GROUP BY
        EoMONTH(cv.[ValueDate]), cv.ManagerId
) t
ORDER BY
    EoMDate,ManagerId

However, this query has two main issues:

It doesn’t include the February record for manager 2.
The LAG function is used incorrectly to calculate change in value.

Step-by-Step Solution

Let’s break down the problem and develop a step-by-step solution using SQL window functions.

Step 1: Calculate Number of Supervisors, Clients, and Total Value for Each Manager

First, we’ll create a subquery that calculates these values for each manager.

SELECT
    EoMDate, 
    ManagerId,
    (
        SELECT COUNT(DISTINCT ct.SupervisorId)
        FROM ClientTable ct
        WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
    ) AS Supervisors,
    (
        SELECT COUNT(DISTINCT ct.ClientId)
        FROM ClientTable ct
        WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
    ) AS Clients,
    SUM(cv.Value) AS Value
FROM
    (
    SELECT
        EOMONTH(cv.[ValueDate]) AS EoMDate,
        cv.ManagerId
    FROM
        ClientValues cv
    GROUP BY
        EoMDate, cv.ManagerId
) t
ORDER BY
    EoMDate,ManagerId

Step 2: Calculate Change in Value from Previous Month for Each Manager

Next, we’ll use the LAG function to calculate the change in value from previous month for each manager.

SELECT
    EoMDate, 
    ManagerId,
    Supervisors,
    Clients,
    Value,
    COALESCE(
        SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate), 0
    ) AS ChangeValue,
    CAST(COALESCE(
        (SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate))/ 
        LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate), 0
    ) * 100, DECIMAL(5,2)
    AS DECIMAL(5,2))
    AS ChangePerc
FROM
    (
    SELECT
        EoMDate, 
        ManagerId,
        SUM(cv2.Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS Value
    FROM
        ClientValues cv2
    WHERE EoMONTH(cv2.[ValueDate]) = EoMDate
    GROUP BY
        EoMDate, ManagerId
    ) t
ORDER BY
    EoMDate,ManagerId

However, this query doesn’t include the February record for manager 2.

Step 3: Handle Missing Records

To fix this issue, we can use a LEFT JOIN to include records from previous months.

SELECT
    t.EoMDate, 
    t.ManagerId,
    t.Supervisors,
    t.Clients,
    COALESCE(
        (
            SELECT SUM(cv2.Value)
            FROM ClientValues cv2
            WHERE EoMTH(cv2.[ValueDate]) = EoMDate AND cv2.ManagerId = t.ManagerId
        ),
        0
    ) AS Value,
    COALESCE(
        (
            SELECT 
                SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate)
            FROM ClientValues cv2
            WHERE EoMTH(cv2.[ValueDate]) = EoMDate AND cv2.ManagerId = t.ManagerId
        ),
        0
    ) AS ChangeValue,
    COALESCE(
        (
            CAST(
                SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate)
            ) * 100, DECIMAL(5,2)
            )
        ),
        0
    ) AS ChangePerc
FROM
    (
    SELECT
        EoMDate,
        ManagerId,
        SUM(cv2.Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS Value
    FROM
        ClientValues cv2
    WHERE EoMTH(cv2.[ValueDate]) = EoMDate
    GROUP BY
        EoMDate, ManagerId
    ) t
LEFT JOIN
    (
    SELECT
        EoMDate,
        ManagerId,
        Supervisors,
        Clients
    FROM
        (
        SELECT
            EoMDate,
            ManagerId,
            (
                SELECT COUNT(DISTINCT ct.SupervisorId)
                FROM ClientTable ct
                WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
            ) AS Supervisors,
            (
                SELECT COUNT(DISTINCT ct.ClientId)
                FROM ClientTable ct
                WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
            ) AS Clients
        FROM
            (
        SELECT
            EoMDate, 
            ManagerId,
            SUM(cv2.Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS Value
        FROM
            ClientValues cv2
        WHERE EoMTH(cv2.[ValueDate]) = EoMDate
        GROUP BY
            EoMDate, ManagerId
        ) t
    ) sub ON t.EoMDate = sub.EoMDate AND t.ManagerId = sub.ManagerId
ORDER BY
    EoMDate,ManagerId

This query correctly handles missing records from previous months and calculates the change in value for each manager.

Final Query

The final query is:

SELECT
    t.EoMDate, 
    t.ManagerId,
    t.Supervisors,
    t.Clients,
    COALESCE(
        (
            SELECT SUM(cv2.Value)
            FROM ClientValues cv2
            WHERE EoMTH(cv2.[ValueDate]) = EoMDate AND cv2.ManagerId = t.ManagerId
        ),
        0
    ) AS Value,
    COALESCE(
        (
            SELECT 
                SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate)
            FROM ClientValues cv2
            WHERE EoMTH(cv2.[ValueDate]) = EoMDate AND cv2.ManagerId = t.ManagerId
        ),
        0
    ) AS ChangeValue,
    COALESCE(
        (
            CAST(
                SUM(cv2.Value) - LAG(SUM(cv2.Value)) OVER (PARTITION BY ManagerId ORDER BY EoMDate)
            ) * 100, DECIMAL(5,2)
            )
        ),
        0
    ) AS ChangePerc
FROM
    (
    SELECT
        EoMDate,
        ManagerId,
        SUM(cv2.Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS Value
    FROM
        ClientValues cv2
    WHERE EoMTH(cv2.[ValueDate]) = EoMDate
    GROUP BY
        EoMDate, ManagerId
    ) t
LEFT JOIN
    (
    SELECT
        EoMDate,
        ManagerId,
        Supervisors,
        Clients
    FROM
        (
        SELECT
            EoMDate,
            ManagerId,
            (
                SELECT COUNT(DISTINCT ct.SupervisorId)
                FROM ClientTable ct
                WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
            ) AS Supervisors,
            (
                SELECT COUNT(DISTINCT ct.ClientId)
                FROM ClientTable ct
                WHERE EOMONTH(ct.[Date])&lt;=EoMDate AND ct.ManagerId = ManagerId
            ) AS Clients
        FROM
            (
        SELECT
            EoMDate, 
            ManagerId,
            SUM(cv2.Value) OVER (PARTITION BY ManagerId ORDER BY EoMDate) AS Value
        FROM
            ClientValues cv2
        WHERE EoMTH(cv2.[ValueDate]) = EoMDate
        GROUP BY
            EoMDate, ManagerId
        ) t
    ) sub ON t.EoMDate = sub.EoMDate AND t.ManagerId = sub.ManagerId
ORDER BY
    EoMDate,ManagerId

This query correctly calculates the number of supervisors and clients for each manager, as well as the change in value over time.

Last modified on 2025-04-28