Derive or modify a few columns using an existing column in SQL database

Question

I want to add three columns LDate, TCount and LApprover by using Date, Count, id columns which are present in the SQL database table.

The process involves specific SQL queries to extract relevant information for each of the new columns.

The initial step is to calculate the total count, denoted as TCount, by executing the following SQL query

SELECT SUM(Count) AS TCount 
FROM db 
GROUP BY id; 

I want to determine the maximum date, labeled as LDate using the following SQL query:

SELECT MAX(Date) AS LDate 
FROM db 
GROUP BY id;

The logic for deriving the LApprover column involves selecting the maximum approver based on conditions related to the LDate and Date columns. The SQL query is as follows:

SELECT
    MAX(CASE 
            WHEN [LDate] = [Date] OR [LDate] IS NULL 
                THEN [Approver] 
                ELSE NULL 
        END) AS [LApprover] 
FROM db 
GROUP BY id; 

Current table:

Date	Count	id	Approver
2022-04-13 14:49:15.0000000	1	E3	Sourav
2020-04-13 17:49:15.0000000	1	E3	Soumyajit
2019-05-15 19:49:15.0000000	1	E3	Raju

Expected result:

LDate	Count	TCount	Approver	LApprover	Date	id
2022-04-13 14:49:15.0000000	1	3	Sourav	Sourav	2022-04-13 14:49:15.0000000	E3
2022-04-13 14:49:15.0000000	1	3	Soumyajit	Sourav	2020-04-13 17:49:15.0000000	E3
2022-04-13 14:49:15.0000000	1	3	Raju	Sourav	2019-05-15 19:49:15.0000000	E3

I have tried this query, but I am not getting the expected result:

WITH CombinedCTE AS 
(
    SELECT 
        q1.id, q2.Count, 
        q1.[TCount], q2.Date, q1.LDate, q2.Approver 
    FROM 
        (SELECT 
             id, COUNT(Count) AS [TCount], MAX([Date] AS LDate 
         FROM 
             db 
         GROUP BY 
             id) q1    
    JOIN 
        (SELECT id, Count, [Date], Approver 
         FROM db) q2  ON q1.id = q2.id 
    WHERE 
        q2.id = 'E3' 
)
SELECT 
    id, Approver, Count, TCount, Date, LDate,
    MAX(CASE WHEN [LDate] IS NULL OR [LDate] = [Date] THEN [Approver] ELSE NULL END) AS [LApprover] 
FROM 
    (SELECT * FROM CombinedCTE) SubQuery
GROUP BY 
    id, Approver, Count, TCount, Date, LDate 

The result that I am getting is this:

LDate	Count	TCount	Approver	LApprover	LDate	id
2022-04-13 14:49:15.0000000	1	3	Sourav	Sourav	2022-04-13 14:49:15.0000000	E3
2022-04-13 14:49:15.0000000	1	3	Soumyajit	NULL	2020-04-13 17:49:15.0000000	E3
2022-04-13 14:49:15.0000000	1	3	Raju	NULL	2019-05-15 19:49:15.0000000	E3

Answer 1

There is no need for such complex joining. You can just use window functions for this.

SELECT *,
  SUM(Count) OVER (PARTITION BY Id) AS TCount,
  MAX(Date) OVER (PARTITION BY Id) AS LDate,
  FIRST_VALUE(Approver) OVER
    (PARTITION BY Id ORDER BY Date DESC ROWS UNBOUNDED PRECEDING) AS LApprover
FROM db;

db<>fiddle