March 17, 2014 at 10:56 am
I have a series of records based on empid where I want to identify the empid that may have discrepancies listed. I have some empids that are listed more than once and have different DOB's. In the example I am trying to Create a DOB_ERROR column and either say yes if the DOB doesn't match the other records in the file with the same empid.
SELECT
Empid,
DOB,
CASE WHEN DOB = DOB THEN 'No' ELSE 'Yes' END AS DOB_ERROR,
City,
St,
Gender
FROM Emp
WHERE EMPID IN
('12335', '23456', '545432','231245')
March 17, 2014 at 12:36 pm
You can identify the empids of the problem records, by grouping by empid and then counting distinct values of DOB.
Something like this:
SELECT
EmpID
FROM
Emp
WHERE
EmpID IN ('12335', '23456', '545432','231245')
GROUP BY
EmpID
HAVING
Count(DISTINCT DOB) > 1
You can then join the results back to your emp table to select the full record along with your DOB_ERROR column.
Something like this:
SELECT
Empoyee.EmpID,
Empoyee.DOB,
Empoyee.City,
Empoyee.St,
Empoyee.Gender,
CASE WHEN Error_Record.EmpID IS Null THEN 'No' ELSE 'Yes' AS DOB_ERROR
FROM
Emp Empoyee
Left Join
(
SELECT
EmpID
FROM
Emp
WHERE
EmpID IN ('12335', '23456', '545432','231245')
GROUP BY
EmpID
HAVING
Count(DISTINCT DOB) > 1
) Error_Record
ON
Employee.EmpID = Error_Record.EmpID
March 17, 2014 at 6:48 pm
Duplicate post. I responded to this question here:
http://www.sqlservercentral.com/Forums/Topic1551846-3077-1.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 18, 2014 at 12:10 am
You can try this
create table dbo.emp(EmpId int,DOB datetime,City varchar(30),Gender char(1))
insert into dbo.emp(empid,DOB,City,Gender)
select 12335,'1jan2014','Gurgaon','M'
union all
select 23456,'10jan2014','Gurgaon','M'
union all
select 23456,'1jan2014','Gurgaon','M'
union all
select 545432,'1jan2014','Gurgaon','M'
union all
select 231245,'1jan2014','Gurgaon','M'
union all
select 231245,'12jan2014','Gurgaon','M'
with cte as
(
select distinct a.Empid
from dbo.emp a join
dbo.emp b
on a.EmpId=b.EmpId
where a.DOB<>b.DOB
)
select a.Empid,
a.DOB,
CASE WHEN a.empid not in(select empid from cte) THEN 'No' ELSE 'Yes' END AS DOB_ERROR,
a.City,
a.Gender from dbo.emp a
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply