CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Eliseo Jr
Eliseo Jr

Reputation: 141

Return unique record from MSSQL table which date is lesser than or equal the current date

I have EmpDept table which keep the history of employee assignment to different department. 

RECNO       EMPNO       DEPTNO      EFFECTIVEDATE
2051        J000012     1           2011-09-16
7322        J000012     3           2018-09-07
2055        J000016     1           2013-05-16
7312        J000016     3           2018-07-09
7320        J000016     3           2018-09-07
7313        J000016     1           2018-09-09
7316        J000016     1           2018-09-15
7390        J000040     2           2017-05-10
7391        J000040     1           2018-09-09
7392        J000040     5           2018-09-10
8912        J000039     1           2018-09-11

If today's date is '2018-09-12', Parameter @DeptNo = 1, Expected Output:

RECNO       EMPNO       DEPTNO      EFFECTIVEDATE
8912        J000039     1           2018-09-11
7313        J000016     1           2018-09-09

Because only EMPNO = J000039 and J000016 having EFFECTIVE DATE less than or equal to '2018-09-12', other records which date less than or equal to today's date are belong to other DeptNo.

From the given record of the table there are 4 distinct EMPNO having DEPTNO=1 these are (J000012, J000016, J000039, J000040). If a query has to be done to display its unique record where EFFECTIVEDATE < = GETDATE() below are the results:

SELECT TOP 1 * 
FROM EMPDEPT
WHERE EMPNO='J000012' AND
EFFECTIVEDATE <=GETDATE()
ORDER BY EFFECTIVEDATE DESC

SELECT TOP 1 * 
FROM EMPDEPT
WHERE EMPNO='J000016' AND
EFFECTIVEDATE <=GETDATE()
ORDER BY EFFECTIVEDATE DESC

SELECT TOP 1 * 
FROM EMPDEPT
WHERE EMPNO='J000039' AND
EFFECTIVEDATE <=GETDATE()
ORDER BY EFFECTIVEDATE DESC

SELECT TOP 1 * 
FROM EMPDEPT
WHERE EMPNO='J000040' AND
EFFECTIVEDATE <=GETDATE()
ORDER BY EFFECTIVEDATE DESC

The output are respectively:

RECNO   EMPNO   DEPTNO  EFFECTIVEDATE
7322    J000012 3       2018-09-07

RECNO   EMPNO   DEPTNO  EFFECTIVEDATE
7313    J000016 1       2018-09-09

RECNO   EMPNO   DEPTNO  EFFECTIVEDATE
8912    J000039 1       2018-09-11

RECNO   EMPNO   DEPTNO  EFFECTIVEDATE
7392    J000040 5       2018-09-10

Based on that results, going back to the requirements, Below are the output because these are only records belong to DEPTNO = 1 which EFFECTIVEDATE is < = GETDATE(), other EMPNO the lates DEPTNO is not equal to 1.

RECNO   EMPNO   DEPTNO  EFFECTIVEDATE
7313    J000016 1       2018-09-09
8912    J000039 1       2018-09-11

Upvotes: 1

Views: 71

Answers (2)

S3S
S3S

Reputation: 25152

I would you WITH TIES and a window function to be easy....

select top 1 * with ties
from yourTable
where DEPTNO = @DeptNo and EFFECTIVEDATE < getdate()
order by row_number() over (partition by EmpNo order by EFFECTIVEDATE desc)

For larger data-sets, then I'd use the same logic in a CTE

;with cte as (
    select
       *
       ,RN = row_number() over (partition by EmpNo order by EFFECTIVEDATE desc)
    from yourTable
    where EFFECTIVEDATE < getdate())

select * 
from cte
where RN = 1   And DEPTNO = @DeptNo

Upvotes: 1

Tyler Gannon
Tyler Gannon

Reputation: 1012

You need to group by employee number and department number. Something like this should do it.

declare @DeptNo as INTEGER
declare @Date as DATE

SET @DeptNo = 1
set @Date = '2018-09-12'

SELECT RECNO, EMPNO, DEPTNO, MAX(EFFECTIVEDATE) 
FROM EmpDept
WHERE EFFECTIVEDATE <= @Date
AND DEPTNO = @DeptNo
GROUP BY RECNO, EMPNO, DEPTNO

Results:

2051    J000012 1   2011-09-16
2055    J000016 1   2013-05-16
7313    J000016 1   2018-09-09
8912    J000039 1   2018-09-11
7391    J000040 1   2018-09-09

Upvotes: 0

Related Questions