sqlsql-serverjoinazure-data-studio

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


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

Solution

  • 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