I have a couple of tables that looks similar to this one with this dummy data:

CREATE TABLE ClientTable (
    ClientId int,
    SupervisorId int,
    ManagerId int,
    Date datetime,
    OthersNonRelevantColumns varchar
)

CREATE TABLE ClientValues (
    ClientId int,
    SupervisorId int,
    ManagerId int,
    ValueDate date,
    Value float,
    OthersNonRelevantColumns varchar
)

INSERT INTO dbo.ClientTable
VALUES
( 1,1,1, '2021-01-31', ''),
( 2,1,1, '2021-02-28', ''),
( 3,1,1, '2021-02-28', ''),
( 4,2,1, '2021-03-31', ''),
( 5,2,1, '2021-03-31', ''),
( 6,3,2, '2021-01-31', ''),
( 7,3,2, '2021-03-31', '')

INSERT INTO dbo.ClientValues
VALUES
( 1,1,1, '2021-01-31', 100.0, ''),
( 1,1,1, '2021-02-28', 90.0, ''),
( 1,1,1, '2021-03-31', 110.0, ''),
( 2,1,1, '2021-02-28', 50.0, '' ),
( 2,1,1, '2021-03-31', 75.0, '' ),
( 3,1,1, '2021-02-28', 100.0, ''),
( 3,1,1, '2021-03-31', 101.0, ''),
( 4,2,1, '2021-03-31', 90.0, ''),
( 5,2,1, '2021-01-31', 70.0, ''),

( 6,3,2, '2021-01-31', 10.0, ''),
( 6,3,2, '2021-02-28', 15.0, ''),
( 6,3,2, '2021-03-31', 30.0, ''),
( 7,3,2, '2021-03-31', 100.0, '')

and I will need to get the number of supervisors, clients, value and change from previous month results grouped by end of month date and managers, so for example the expected result for the previous data could be something like this. Bear in mind the data is cumulative for supervisors and clients in ClientsTable

EOMDate     ManagerId   Supervisors     Clients         Value       ChangeValue     ChgPerc
2021-01-31  1           1               1               170         
2021-01-31  2           1               1               10          
2021-02-28  1           1               3               240         70              41%
2021-02-28  2           1               1               15          5               50%
2021-03-31  1           2               5               376         136             57%
2021-03-31  2           1               2               130         115             866%

What I have so far is this query but I don´t know how to get the change from previous month and also I miss the february record for manager 2

SELECT
    EOMONTH(ct.[Date]) AS EoMDate,
    ct.ManagerId,
    (SELECT count(DISTINCT ct2.SupervisorId) AS AccHolders
        FROM ClientTable ct2
        WHERE ct2.ManagerId= ct.ManagerId
        AND EOMONTH(ct2.[Date]) <= EOMONTH(ct.[Date])) AS AccountHolders,
    (SELECT count(ct3.ClientId) AS Clients
        FROM ClientTable ct3 
        WHERE ct3.ManagerId = ct.ManagerId
        AND EOMONTH(ct3.[Date]) <= EOMONTH(ct.[Date])) AS NumberOfClients,
    (SELECT SUM(cv.[Value]) AS Net
        FROM ClientValues cv
        WHERE cv.ManagerId= ct.ManagerId
        AND EOMONTH(cv.ValueDate) <= EOMONTH(ct.[Date])) NetPosition
FROM ClientTable ct
GROUP BY ct.ManagerId, EOMONTH(ct.[Date])
ORDER BY EoMDate, ct.ManagerId

CodePudding user response：

You may try the following which uses window functions to extract your net and net position values from a subquery similar to your initial attempt but using data from the ClientValue table.

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])<=EOMONTH(cv.[ValueDate]) AND ct.ManagerId = cv.ManagerId
        )  as Supervisors,
        (
            SELECT COUNT(DISTINCT ct.ClientId)
            FROM ClientTable ct
            WHERE EOMONTH(ct.[Date])<=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

View Demo on Db Fiddle