April 30, 2012 at 10:21 pm
I have table with below structure
Create table Employee
(ID int,
Division int,
DateofBirth Datetime,
SSN nvarchar(200),
EmployeeCode nvarchar(20))
Insert into Employee Values(1,10,'01/01/1982',111-11-1111,'XYZABC9')
Insert into Employee Values(2,10,'01/01/1982',111-11-1111,Null)
Insert into Employee Values(3,10,'01/01/1981',222-22-2222,'XYZABC9')
Insert into Employee Values(4,20,'01/01/1984',444-44-4444,'HSHDKSKK')
Insert into Employee Values(5,20,'01/01/1986',555-55-5555,'YEEURURJ')
It Should return the records which has the
(SameDateofBirth) AND (Same SSN or Same EmployeeCode) for each division
From the above data it should return the below records
1,10,'01/01/1982',111-11-1111,'XYZABC9'
2,10,'01/01/1982',111-11-1111,Null
the Above 2 records will be returned because they has the same (DateofBirth) ANd(SSN or EmployeeCode) related to division 10
Could someone please help me with the SQL query.
April 30, 2012 at 10:46 pm
Hi,
I'm sorry i get confused your doubt please post your DDL or your desired output. if i'm not mistaken how you think about this..???
from your table
group by <your columns>
having count(1) > 1;
Ganesh
April 30, 2012 at 11:11 pm
Try:
with
CTE_SSN as
(
select Division, DateofBirth, SSN
from Employee
group by Division, DateofBirth, SSN
having count(*) > 1
),
CTE_Code as
(
select Division, DateofBirth, EmployeeCode
from Employee
group by Division, DateofBirth, EmployeeCode
having count(*) > 1
)
select e.ID, e.Division, e.DateofBirth, e.SSN, e.EmployeeCode
from CTE_SSN as c
join Employee as e on (e.Division = c.Division) and
(e.DateofBirth = c.DateofBirth) and
(e.SSN = c.SSN)
union all
select e.ID, e.Division, e.DateofBirth, e.SSN, e.EmployeeCode
from CTE_Code as c
join Employee as e on (e.Division = c.Division) and
(e.DateofBirth = c.DateofBirth) and
(e.EmployeeCode = c.EmployeeCode)
Hope this helps.
May 1, 2012 at 2:11 am
Have also posted the query in your original thread. Here it is again:
;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By DateOfBirth, SSN Order By ID) As Rownum From Employee)
Select b.* From CTE as a
JOIN Employee As b ON a.DateofBirth = b.DateofBirth AND a.SSN = b.SSN
Where a.Rownum > 1
Hope it helps you.
May 1, 2012 at 2:19 am
select ID,Division,DateofBirth,SSN,EmployeeCode from Employee as a
where a.Division=(select Division from Employee b where a.Division=b.Division
group by Division having COUNT(*)>1)
and DateofBirth=(select DateofBirth from Employee b where a.DateofBirth=b.DateofBirth
group by DateofBirth having COUNT(*)>1 )
with c as
(
select a.ID,a.Division,a.DateofBirth,a.SSN,a.EmployeeCode from Employee a
inner join Employee b on a.Division=b.Division and a.DateofBirth=a.DateofBirth and a.SSN=b.SSN
group by a.ID,a.Division,a.DateofBirth,a.SSN,a.EmployeeCode having COUNT(*)>1
)
select * from c
May 1, 2012 at 2:24 am
Sorry missed the group by Division part in my last post...here is the edited query:
;With CTE
As
(Select *, ROW_NUMBER() Over (Partition By DateOfBirth, SSN Order By ID) As Rownum From Employee)
Select b.Id, b.Division, MAX(b.DateofBirth) As DateofBirth, MAX(b.SSN) As SSN, b.EmployeeCode From CTE as a
JOIN Employee As b ON a.DateofBirth = b.DateofBirth AND a.SSN = b.SSN
Where a.Rownum > 1
Group By b.Division, b.EmployeeCode, b.ID
Order By b.Division, b.EmployeeCode DESC
May 1, 2012 at 4:43 am
CREATE TABLE dbo.Employee
(
ID integer PRIMARY KEY,
Division integer NOT NULL,
DateofBirth datetime NULL,
SSN varchar(12) NULL,
EmployeeCode varchar(10) NULL
);
INSERT dbo.Employee VALUES (1,10,'01/01/1982',111-11-1111,'XYZABC9');
INSERT dbo.Employee VALUES (2,10,'01/01/1982',111-11-1111,NULL);
INSERT dbo.Employee VALUES (3,10,'01/01/1981',222-22-2222,'XYZABC9');
INSERT dbo.Employee VALUES (4,20,'01/01/1984',444-44-4444,'HSHDKSKK');
INSERT dbo.Employee VALUES (5,20,'01/01/1986',555-55-5555,'YEEURURJ');
SELECT
e.ID,
e.Division,
e.DateofBirth,
e.SSN,
e.EmployeeCode
FROM dbo.Employee AS e
JOIN dbo.Employee AS e2 ON
e2.Division = e.Division
AND e2.DateofBirth = e.DateofBirth
AND
(
e2.EmployeeCode = e.EmployeeCode
OR e2.SSN = e.SSN
)
AND e2.ID <> e.ID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 2, 2012 at 4:27 am
Try this ..
SELECT A.* FROM Employee A ,Employee B WHERE A.DateofBirth = B.DateofBirth AND A.id <>B.id AND (A.SSN = B.SSN OR A.EmployeeCode = B.EmployeeCode)AND A.Division = B.Division
May 2, 2012 at 9:25 am
Thanks so much everyone... i got the result.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply