Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
greenstone90
Starting Member
8 Posts |
Posted - 2013-01-14 : 17:07:00
|
Hi,I have two tables:EMPLOYEE_SERVICE tableIDFROM_DATETO_DATEEMPLOYEE_SERVICE_DETAILS tableIDEMPLOYEE_SERVICE_ID (fk to EMPLOYEE_SERVICE.ID)FROM_DATETO_DATEEMPLOYEE_SERVICE:EMPLOYEE_SERVICE_DETAILS is 1:m relationshipMy program expects, if there is at one or more EMPLOYEE_SERVICE_DETAIL records for an EMPLOYEE_SERVICE record, then one EMPLOYEE_SERVICE_DETAIL record will have the same FROM_DATE as its parent EMPLOYEE_SERVICE.FROM_DATE.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).This didn't seem to work:select ES.ID, ES.EMPLOYEE_ID, ES.FROM_DATE, ESD.FROM_DATE from GRB_ASSIST.EMP_SERVICE_DETAILS ESD INNER JOIN GRB_ASSIST.EMP_SERVICE ES on ESD.EMPLOYEE_SERVICE_ID = ES.IDGROUP BY ESD.EMPLOYEE_SERVICE_ID, ESD.FROM_DATE, ES.FROM_DATE, ES.EMPLOYEE_ID, ES.IDHAVING ESD.FROM_DATE <> ES.FROM_DATE AND COUNT(ESD.EMPLOYEE_SERVICE_ID) > 1thoughts how to make this query?Thanks! |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 17:42:16
|
SELECT ID,FROM_DATE,TO_DATEFROM EMPLOYEE_SERVICE esWHERE NOT EXISTS (select * from EMPLOYEE_SERVICE_DETAILS esd where es.id = esd.EMPLOYEE_SERVICE_ID and es.FROM_DATE = esd.FROM_DATE )JimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 00:05:16
|
quote: Originally posted by jimf SELECT ID,FROM_DATE,TO_DATEFROM EMPLOYEE_SERVICE esWHERE NOT EXISTS (select * from EMPLOYEE_SERVICE_DETAILS esd where es.id = esd.EMPLOYEE_SERVICE_ID and es.FROM_DATE = esd.FROM_DATE )JimEveryday I learn something that somebody else already knew
This will even return employees who dont have a detail record at allseedeclare @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_SERVICEVALUES (1, GETDATE()-23,GETDATE()-16), (7, GETDATE()-123,GETDATE()-96), (9, GETDATE()-55,GETDATE()-43)--this doesnt have detail record INSERT @EMPLOYEE_SERVICE_DETAILSVALUES (1,1, GETDATE()-23,GETDATE()-10), (2,7, GETDATE()-116,GETDATE()-33)-- this has wront from date SELECT ID,FROM_DATE,TO_DATEFROM @EMPLOYEE_SERVICE esWHERE NOT EXISTS (select *from @EMPLOYEE_SERVICE_DETAILS esdwhere 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-119 2012-11-21 2012-12-03 Reading thisI 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_DATEThat being case i think it should beSELECT ID,FROM_DATE,TO_DATEFROM(SELECT es.*,COUNT(CASE WHEN es.FROM_DATE = esd.FROM_DATE THEN 1 END) OVER (PARTITION BY es.ID) AS CntFROM @EMPLOYEE_SERVICE esINNER JOIN @EMPLOYEE_SERVICE_DETAILS esdON es.id = esd.EMPLOYEE_SERVICE_ID)tWHERE Cnt=0Output-------------------------------------ID FROM_DATE TO_DATE-------------------------------------7 2012-09-14 2012-10-11 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
greenstone90
Starting Member
8 Posts |
Posted - 2013-01-15 : 14:56:22
|
Hi Jim and Visakh16,Thanks for the help!Visakh, you were exactly right...and your script worked well.Regards,Greenstone |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-16 : 22:47:47
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|