May 24, 2017 at 6:05 am
The table has employee data. EmployeeID, HireDate, TermDate. Some records have the same EmployeeID and the Hire and Term dates are the same but, in different columns. How can I identify these records?
create table #T(EmployeeID varchar(30),HireDate datetime,TermDate datetime)
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE001739','10/14/2016','10/19/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE012553','10/04/2016','10/07/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE028536','09/30/2016','10/05/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE053099','10/09/2016','10/11/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE053827','10/03/2016','10/05/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE087455','10/08/2016','10/16/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE087961','10/13/2016','10/17/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE087961','10/28/2016','10/30/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE097561','10/17/2016','10/21/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE102776','10/08/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/09/2016','10/11/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/11/2016','10/16/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/16/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE106335','10/07/2016','10/10/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE119442','10/11/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE125172','10/10/2016','10/27/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE127783','10/14/2016','10/17/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE131536','10/18/2016','10/26/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE137695','10/13/2016','10/14/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE143823','10/01/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE147897','09/28/2016','10/08/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE150813','09/28/2016','10/03/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE150813','10/03/2016','10/07/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE155573','10/13/2016','10/18/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE160781','09/30/2016','10/07/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE162757','09/27/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE162962','10/16/2016','10/21/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE164856','10/11/2016','10/18/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE168420','10/09/2016','10/13/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE176149','10/21/2016','10/23/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE185977','09/30/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE189943','10/03/2016','10/07/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE193159','10/25/2016','10/27/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE193936','10/03/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE201338','10/08/2016','10/14/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE203056','10/25/2016','10/31/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE207156','10/20/2016','10/24/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE221687','10/06/2016','10/10/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE229951','10/05/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE229951','10/06/2016','10/13/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE245420','09/29/2016','10/03/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE253527','09/28/2016','10/11/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE254096','09/27/2016','10/05/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE261994','10/19/2016','10/24/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE262558','10/29/2016','10/31/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE266736','10/10/2016','10/18/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE268446','10/25/2016','10/30/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE284477','10/03/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE288800','10/10/2016','10/13/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE304714','09/25/2016','10/16/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE310123','10/10/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE311196','10/20/2016','10/28/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE323875','10/19/2016','10/25/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE327798','10/14/2016','10/24/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE330561','10/04/2016','10/07/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE338214','10/12/2016','10/14/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE341815','10/24/2016','10/29/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE363421','09/29/2016','10/02/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE369518','10/16/2016','10/19/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE372026','10/12/2016','10/16/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE384358','10/04/2016','10/09/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE429409','08/21/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE432792','10/24/2016','10/27/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/16/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/22/2016','10/23/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/25/2016','10/31/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE445612','10/07/2016','10/11/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE457149','09/28/2016','10/08/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE459752','10/26/2016','10/28/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE467930','10/02/2016','10/06/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE470010','10/11/2016','10/21/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE470481','10/19/2016','10/25/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE492953','10/09/2016','10/10/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE504179','10/18/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE510041','10/21/2016','10/28/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE533859','10/11/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE552860','10/19/2016','10/23/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE554770','10/11/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE561482','10/26/2016','10/27/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE565222','09/22/2016','10/04/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE566096','09/27/2016','10/04/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE574562','10/18/2016','10/24/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE594251','10/10/2016','10/13/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE606609','09/29/2016','10/04/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE609349','10/01/2016','10/02/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE609521','10/01/2016','10/05/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE616560','09/15/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE629674','10/13/2016','10/16/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE632849','10/17/2016','10/19/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE639566','09/29/2016','10/12/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE665358','10/29/2016','10/30/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE667503','10/19/2016','10/24/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE683699','10/14/2016','10/17/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE687457','10/13/2016','10/22/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE736379','10/11/2016','10/13/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE776392','10/20/2016','10/23/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE792795','10/21/2016','10/25/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE810305','10/20/2016','10/22/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE818410','10/17/2016','10/20/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE822166','10/12/2016','10/15/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE822166','10/22/2016','10/23/2016') |
| insert into #T(EmployeeID, HireDate, TermDate) values('ATE825291','10/14/2016','10/17/2016') |
May 24, 2017 at 6:33 am
What should the query return, based on that sample data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2017 at 6:46 am
If you look at EmployeeID ATE104905, I would like to return the EmployeeID's with the same HireDate and TermDate for the same employee. This employee has 3 records HireDate 10/9/16 and TermDate 10/11/16, then HireDate 10/11/16 and TermDate 10/16/16 then, HireDate 10/16/16 and TermDate 10/20/16. So, the query should return his EmployeeID.
May 24, 2017 at 6:51 am
Is this what you are looking for:
SELECT EmployeeID
, HireDate
, TermDate
FROM #T
WHERE EmployeeID IN ( SELECT T.EmployeeID
FROM #T T
JOIN #T T2
ON T.EmployeeID = T2.EmployeeID
AND T.TermDate = T2.HireDate
GROUP BY T.EmployeeID );
May 24, 2017 at 6:59 am
Yes. Thanx.
May 24, 2017 at 7:13 am
Is this what you're looking for?
SELECT
t.EmployeeID,
t.HireDate,
t.TermDate,
NewOrExisting = CASE
WHEN t.HireDate = LAG(t.TermDate, 1) OVER (PARTITION BY t.EmployeeID ORDER BY t.HireDate) THEN 'Existing Cont.'
WHEN t.EmployeeID = LAG(t.EmployeeID, 1) OVER (PARTITION BY t.EmployeeID ORDER BY t.HireDate) THEN 'Existing w/ Gap'
ELSE 'New'
END
FROM
#T t;
May 25, 2017 at 11:08 am
NineIron - Wednesday, May 24, 2017 6:05 AMThe table has employee data. EmployeeID, HireDate, TermDate. Some records have the same EmployeeID and the Hire and Term dates are the same but, in different columns. How can I identify these records?
create table #T(EmployeeID varchar(30),HireDate datetime,TermDate datetime)
insert into #T(EmployeeID, HireDate, TermDate) values('ATE001739','10/14/2016','10/19/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE012553','10/04/2016','10/07/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE028536','09/30/2016','10/05/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE053099','10/09/2016','10/11/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE053827','10/03/2016','10/05/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE087455','10/08/2016','10/16/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE087961','10/13/2016','10/17/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE087961','10/28/2016','10/30/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE097561','10/17/2016','10/21/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE102776','10/08/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/09/2016','10/11/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/11/2016','10/16/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE104905','10/16/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE106335','10/07/2016','10/10/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE119442','10/11/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE125172','10/10/2016','10/27/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE127783','10/14/2016','10/17/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE131536','10/18/2016','10/26/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE137695','10/13/2016','10/14/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE143823','10/01/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE147897','09/28/2016','10/08/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE150813','09/28/2016','10/03/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE150813','10/03/2016','10/07/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE155573','10/13/2016','10/18/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE160781','09/30/2016','10/07/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE162757','09/27/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE162962','10/16/2016','10/21/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE164856','10/11/2016','10/18/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE168420','10/09/2016','10/13/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE176149','10/21/2016','10/23/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE185977','09/30/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE189943','10/03/2016','10/07/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE193159','10/25/2016','10/27/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE193936','10/03/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE201338','10/08/2016','10/14/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE203056','10/25/2016','10/31/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE207156','10/20/2016','10/24/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE221687','10/06/2016','10/10/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE229951','10/05/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE229951','10/06/2016','10/13/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE245420','09/29/2016','10/03/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE253527','09/28/2016','10/11/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE254096','09/27/2016','10/05/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE261994','10/19/2016','10/24/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE262558','10/29/2016','10/31/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE266736','10/10/2016','10/18/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE268446','10/25/2016','10/30/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE284477','10/03/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE288800','10/10/2016','10/13/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE304714','09/25/2016','10/16/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE310123','10/10/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE311196','10/20/2016','10/28/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE323875','10/19/2016','10/25/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE327798','10/14/2016','10/24/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE330561','10/04/2016','10/07/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE338214','10/12/2016','10/14/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE341815','10/24/2016','10/29/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE363421','09/29/2016','10/02/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE369518','10/16/2016','10/19/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE372026','10/12/2016','10/16/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE384358','10/04/2016','10/09/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE429409','08/21/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE432792','10/24/2016','10/27/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/16/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/22/2016','10/23/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE434649','10/25/2016','10/31/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE445612','10/07/2016','10/11/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE457149','09/28/2016','10/08/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE459752','10/26/2016','10/28/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE467930','10/02/2016','10/06/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE470010','10/11/2016','10/21/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE470481','10/19/2016','10/25/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE492953','10/09/2016','10/10/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE504179','10/18/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE510041','10/21/2016','10/28/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE533859','10/11/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE552860','10/19/2016','10/23/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE554770','10/11/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE561482','10/26/2016','10/27/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE565222','09/22/2016','10/04/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE566096','09/27/2016','10/04/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE574562','10/18/2016','10/24/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE594251','10/10/2016','10/13/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE606609','09/29/2016','10/04/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE609349','10/01/2016','10/02/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE609521','10/01/2016','10/05/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE616560','09/15/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE629674','10/13/2016','10/16/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE632849','10/17/2016','10/19/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE639566','09/29/2016','10/12/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE665358','10/29/2016','10/30/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE667503','10/19/2016','10/24/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE683699','10/14/2016','10/17/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE687457','10/13/2016','10/22/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE736379','10/11/2016','10/13/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE776392','10/20/2016','10/23/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE792795','10/21/2016','10/25/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE810305','10/20/2016','10/22/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE818410','10/17/2016','10/20/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE822166','10/12/2016','10/15/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE822166','10/22/2016','10/23/2016') insert into #T(EmployeeID, HireDate, TermDate) values('ATE825291','10/14/2016','10/17/2016')
Please post DDL and follow ANSI/ISO standards when asking for help.
May 25, 2017 at 11:09 am
>> How can I identify these records [sic]? <<
Wrong question! You ask how you can prevent them instead. What you posted is a deck of punch cards written in SQL, and not a table at all. By definition, a table has key, but your post has no key and no way to ever have a key. If you don’t correct your DDL, will be doing this over and over and over. Would you hire a carpenter who builds a house and tells you don’t worry about the leaky roof; you can just put buckets under the holes!
Let’s make an attempt at correcting the DDL. We need add a key. We need to make sure that we got a decent size and data type for the columns. Why did you oversize the employee ID and not put a constraint on its regular expression? Again, you’re inviting bad data. We have a DATE data type now. We also know that you can’t fire somebody before you hire them, so we can add a constraint. Get a copy of the Rick Snodgrass book from the University of Arizona on temporal queries in SQL. It’s a free PDF download. We use a null for the ending date of an interval to tell us that the event is not over (i.e., we haven’t fired the bum yet). Finally, you don’t seem to know that the only format allowed in ANSI/ISO standard SQL is based on ISO 8601 two you keep using some local dialect.
CREATE TABLE Personnel
(emp_id CHAR(30) NOT NULL PRIMARY KEY
CHECK (emp_id LIKE 'ATE[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
hire_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
termination_date DATE,
CHECK (hire_date <= termination_date));
You also are still using the old “one punch card at a time†insertion syntax that was part of the original Sybase product. We have had the ANSI/ISO standard set constructor syntax for some time. I transcribed and corrected your raw data, but I did not remove the bad data that you would put in it. I also change the name of the table to something meaningful.
INSERT INTO Personnel
VALUES
('ATE012553', '2016-10-04', '2016-10-07'),
('ATE028536', '2016-09-30', '2016-10-05'),
('ATE053099', '2016-10-09', '2016-10-11'),
('ATE053827', '2016-10-03', '2016-10-05'),
('ATE087455', '2016-10-08', '2016-10-16'),
('ATE087961', '2016-10-13', '2016-10-17'),
('ATE087961', '2016-10-28', '2016-10-30'), -– error!
('ATE097561', '2016-10-17', '2016-10-21'),
('ATE102776', '2016-10-08', '2016-10-12'),
('ATE104905', '2016-10-09', '2016-10-11'),
('ATE104905', '2016-10-11', '2016-10-16'),-- error!
('ATE104905', '2016-10-16', '2016-10-20'),-– error!
--('ATE104905', '2016-10-09', '2016-10-20')???
('ATE106335', '2016-10-07', '2016-10-10'),
('ATE119442', '2016-10-11', '2016-10-12'),
('ATE125172', '2016-10-10', '2016-10-27'),
('ATE127783', '2016-10-14', '2016-10-17'),
('ATE131536', '2016-10-18', '2016-10-26'),
('ATE001739', '2016-10-14', '2016-10-19');
See how having a key prevents duplicate and/or erroneous rows? Having a key is one of the many, many ways that rows are not records. You might want to do this by writing a stored procedure to extend the employment of someone by updating their termination date.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply