quote:
Originally posted by jimf
SELECT ID,FROM_DATE,TO_DATE
FROM EMPLOYEE_SERVICE es
WHERE NOT EXISTS (select *
from EMPLOYEE_SERVICE_DETAILS esd
where es.id = esd.EMPLOYEE_SERVICE_ID and es.FROM_DATE = esd.FROM_DATE
)
Jim
Everyday I learn something that somebody else already knew
This will even return employees who dont have a detail record at all
see
declare @EMPLOYEE_SERVICE table
(
ID int,
FROM_DATE date,
TO_DATE date
)
declare @EMPLOYEE_SERVICE_DETAILS table
(
ID int,
EMPLOYEE_SERVICE_ID int ,
FROM_DATE date,
TO_DATE date
)
INSERT @EMPLOYEE_SERVICE
VALUES (1, GETDATE()-23,GETDATE()-16),
(7, GETDATE()-123,GETDATE()-96),
(9, GETDATE()-55,GETDATE()-43)--this doesnt have detail record
INSERT @EMPLOYEE_SERVICE_DETAILS
VALUES (1,1, GETDATE()-23,GETDATE()-10),
(2,7, GETDATE()-116,GETDATE()-33)-- this has wront from date
SELECT ID,FROM_DATE,TO_DATE
FROM @EMPLOYEE_SERVICE es
WHERE NOT EXISTS (select *
from @EMPLOYEE_SERVICE_DETAILS esd
where es.id = esd.EMPLOYEE_SERVICE_ID and es.FROM_DATE = esd.FROM_DATE
)
output
-----------------------------------
ID FROM_DATE TO_DATE
-----------------------------------
7 2012-09-14 2012-10-11
9 2012-11-21 2012-12-03
Reading this
I think I have some bad data: I need to get a list of all EMPLOYEE_SERVICE records that have EMPLOYEE_SERVICE_DETAILS records, but don't have a
EMPLOYEE_SERVICE_DETAILS record with a matching FROM_DATE (note: an EMPLOYEE_SERVICE record doesn't have to have any EMPLOYEE_SERVICE_DETAILS).
I feel OP is looking only for cases where employee has a service detail record but with a mismatched FROM_DATE
That being case i think it should be
SELECT ID,FROM_DATE,TO_DATE
FROM(
SELECT es.*,COUNT(CASE WHEN es.FROM_DATE = esd.FROM_DATE THEN 1 END) OVER (PARTITION BY es.ID) AS Cnt
FROM @EMPLOYEE_SERVICE es
INNER JOIN @EMPLOYEE_SERVICE_DETAILS esd
ON es.id = esd.EMPLOYEE_SERVICE_ID
)t
WHERE Cnt=0
Output
-------------------------------------
ID FROM_DATE TO_DATE
-------------------------------------
7 2012-09-14 2012-10-11
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
SQL Query against parent-child tables
Posted - 01/14/2013 : 17:07:00
