CODEWITHSUNDEEP
sqlsql-server
JBlack
JBlack

Reputation: 29

SQL Group By issues with derived table

I'm attempting to create a table of all the latest payments made for an employee. The original table has all the payments made to an employee since they started. I created a derived table to give me only the records with the latest date in them.

I do still have some duplicates where the payment date is the same, in this case I want to add these payment together so they appear on one row instead.

Below is my working code;

SELECT        T1.EmployeeCode
    , T2.Staff_Number
    , T2.Firstname + ' ' + T2.Surname   AS Name
    , T1.PaymentDate
    , T1.p1
    , T1.p2
    , T1.p3
    FROM DB1.dbo.PARTIFPSNI AS T1




--This section is supposed to return only the latest date 

INNER JOIN (
            SELECT EmployeeCode, MAX(PaymentDate) as MaxDate
            FROM DB1.dbo.PARTIFPSNI
            GROUP BY EmployeeCode               
        )   T1A ON T1.EmployeeCode = T1A.EmployeeCode and T1.PaymentDate = T1A.MaxDate



LEFT JOIN DB2.dbo.Personnel_Records AS T2 ON (T1.EmployeeCode = T2.Staff_Number)

This returns the below;

enter image description here

I seem to have issues summing together p1, p2 & p3. I think this because I am trying to use the GROUP BY function twice.

Upvotes: 0

Views: 49

Answers (1)

LS_85
LS_85

Reputation: 248

SELECT  T1.EmployeeCode ,
        T2.Staff_Number ,
        T2.Firstname + ' ' + T2.Surname AS Name ,
        T1.PaymentDate ,
        SUM(T1.p1) ,
        SUM(T1.p2) ,
        SUM(T1.p3)
FROM    DB1.dbo.PARTIFPSNI AS T1
        INNER JOIN ( SELECT EmployeeCode ,
                            MAX(PaymentDate) AS MaxDate
                     FROM   DB1.dbo.PARTIFPSNI
                     GROUP BY EmployeeCode
                   ) T1A ON T1.EmployeeCode = T1A.EmployeeCode
                            AND T1.PaymentDate = T1A.MaxDate
        LEFT JOIN DB2.dbo.Personnel_Records AS T2 ON ( T1.EmployeeCode = T2.Staff_Number )
GROUP BY T1.EmployeeCode ,
        T2.Staff_Number ,
        T2.Firstname + ' ' + T2.Surname ,
        T1.PaymentDate

Upvotes: 2

Related Questions