SELECT data from another table with max date

Question

I need help with a SELECT query in which I need to join to another table and get record with max date. I have created sample to demonstrate. My last SELECT is incorrect and I need guidance to fix it or if there is a better way in Sql server 2014

CREATE TABLE #EmpTable
(
    EmpNum INT,
    colA VARCHAR(5) NULL,
    colB VARCHAR(5) NULL
)

CREATE TABLE #EmpDetailTbl
(
    EmpNum INT,
    Name VARCHAR(10),
    Department VARCHAR(10) NULL,
    ReportDate DATETIME NOT NULL
)

INSERT INTO #EmpTable 
VALUES (101, 'val11', 'Val21'), (102, 'val12', 'Val21'), (103, 'val13', 'Val23');

INSERT INTO #EmpDetailTbl 
VALUES (101, 'emp101', 'Dept1', '05/01/2018'), (101, 'emp101', 'Dept2', '06/01/2018'),
       (101, 'emp101', 'Dept1', '05/01/2017'), (102, 'emp102', 'Dept3', '04/01/2018'),
       (102, 'emp102', 'Dept1', '05/01/2018')

--select * from  #EmpDetailTbl
--select * from  #EmpTable

SELECT 
    a.EmpNum, Name, ColA, ColB, Department
FROM 
    #EmpTable a
LEFT OUTER JOIN
    #EmpDetailTbl b ON a.EmpNum = b.EmpNum 
                    AND ReportDate = (SELECT MAX(ReportDate) 
                                      FROM #EmpDetailTbl 
                                      a.EmpNum = b.EmpNum)

one rec per EmpNum with max(ReportDate) – SilverFish Jun 18 '18 at 19:54 — SilverFish, Jun 18 '18 at 19:54

score 2 · Answer 1 · answered Jun 18 '18 at 20:38

Use rank() analytic function to enumerate rows based on their report dates and then pick only the first for each employee:

SELECT EmpNum, Name, ColA, ColB, Department
FROM (
  SELECT 
    a.EmpNum, b.Name, a.ColA, a.ColB, b.Department,
    rank() over (partition by a.EmpNum order by b.ReportDate desc) as rn
  FROM #EmpTable a
  LEFT JOIN #EmpDetailTbl b ON 
    a.EmpNum = b.EmpNum
  ) t
WHERE rn = 1;

This query will take care of ties: in case there is more than 1 record for employee with the same date which also happens to be maximum it will show them all.

D-Shih · Answer 2 · 2018-06-18T19:59:17.670

1

If I understand correctly you can try this to get MaxDate row.

using a exists to get MAX(b1.ReportDate) by EmpNum

select a.EmpNum, Name, ColA, ColB, Department
FROM #EmpTable a
LEFT JOIN #EmpDetailTbl b on a.EmpNum = b.EmpNum 
WHERE exists (
    SELECT 1
    FROM #EmpDetailTbl b1
    WHERE b1.EmpNum = b.EmpNum
    GROUP BY b1.EmpNum
    HAVING MAX(b1.ReportDate) = b.ReportDate
)

sqlfiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a3deade95dbd25d9cadadee37e16d9c6

Result

EmpNum  Name    ColA    ColB    Department
101    emp101   val11   Val21   Dept2
102    emp102   val12   Val21   Dept1
103    emp103   val13   Val23   Dept1

edited Jun 18 '18 at 19:59

answered Jun 18 '18 at 19:54

D-Shih

44,943
6
31
51

The sample works as expected, however, in my real query I am not getting all the rows from #EmpTable even though using LEFT JOIN or LEFT OUTER JOIN. Can you think of a reason? – SilverFish Jun 18 '18 at 20:07
It should be getting all rows from ` #EmpTable` because `LEFT JOIN`, if not could you provide some data and now result set and your expected result? @SilverFish – D-Shih Jun 18 '18 at 20:13
I think it is due to the WHERE clause in the inner SQL which acts as INNER JOIN. I tried replacing with Right Join but still inconsistent result. I will try to create test data to mimic the issue – SilverFish Jun 18 '18 at 20:18
Please see updated example. I removed emp103 from #EmpDetailTbl to demonstrate left join not working. Now it will return only EmpNum 101, 102 but not 103 eventhough using left join – SilverFish Jun 18 '18 at 20:26
From your expect result you need to use `UNION ALL` to combine Maxdate result and No associated EMPID result set https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d4795ea26d3463160e8ec34cccc45937 @SilverFish – D-Shih Jun 18 '18 at 20:36
@SilverFish You can mark this answer if that help you :) – D-Shih Jun 18 '18 at 20:47

score 0 · Answer 3 · answered Jun 18 '18 at 19:56

0

ReportDate SHOULD BE a.ReportDate

answered Jun 18 '18 at 19:56

maddy

50
1
1
10

1

a has no column as ReportDate – SilverFish Jun 18 '18 at 20:09
Ya but b has report date then write b.reportdate.My point is give table alias to the correspondig column – maddy Jun 18 '18 at 20:27

SELECT data from another table with max date

3 Answers3