Need help in refining the query

[devaprakash_b]

Hello Experts,

Need your help in refining the query further more.

table structure 

Mskey  Col A Col B

1   empno [20141127-20151128]1234

1   empno [20151201-99991231]232544

1   salutation [20141127-99991231]Mrs

1   salutation [20151127-99991231]Mr

2   empno [20141127-20151128]1234

2   empno [20151201-99991231]232544

2   salutation [20141127-99991231]Mrs

2   salutation [20151127-99991231]Mr

My requirement is to find the list of overlapping records based on the dates

user details may be varying from time to time as new data would be pushed through HR systems to identity store via an interface.

The job is getting failed whenever there is any overlapping with dates. So we proactively decided to schedule a job in this regards which runs weekly and would let us know for which and all the users there is overlapping dates are there, so that we can send the list to HR team for pushing new data.

Overlapping Issue Example:

The users employee id for an year it is 1234 and later he moved to another department and his employee id got changed and it became 2345 remaining all details are same. So the HR systems send the data for this user as empno - [20141127-20151128]1234 and empno - [20151201-99991231]232544

it means from 20141127 to 20151128 his employee no is 1234 and from 20151201 to 99991231 his employee would be 2345.

This is a correct case and the tool would accept this data.

the below cases are invald

Case 1: 1 salutation [20141127-99991231]Mrs 1 salutation [20151127-99991231]Mr

Case 2: 2 salutation [20141127-99991231]Mrs 2 salutation [20141127-99991231]Mr

So we wanted to find these overlapping records from tables.

My Query:

I am able to successfully write query for the case 2 type but unable to write for case1.

select id,colA

count(left(ColB,CHARINDEX(']',ColB))) as 'Count'

from tblA with (nolock)

where id in (Select distinct id from tblb with (nolock))

group by id, cola,left(ColB,CHARINDEX(']',ColB))

having count(left(ColB,CHARINDEX(']',ColB)))>1

[devaprakash_b · Accepted solution]

Finally got the required answer with the below query

WITH Cte AS
(
SELECT ID,ColA,ColB,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN,
CAST(SUBSTRING(ColB,2,CHARINDEX('-',ColB)-2) AS DATE) AS StartDT,
CAST(SUBSTRING(ColB,CHARINDEX('-',ColB)+1,8) AS DATE)  AS EndDT  FROM TblA
)
SELECT c1.ID, c1.ColA,c1.ColB
FROM Cte c1 JOIN Cte c2
ON c1.RN != c2.RN
AND c1.ID=c2.ID
AND c1.ColA=c2.ColA
AND (c1.StartDT BETWEEN c2.StartDT AND c2.EndDT OR c2.StartDT BETWEEN c1.StartDT AND c1.EndDT )

[Former Member]

Hi D P,

The only difference I can see between case 1 and case 2 is the second date which is underscored

Case 1: 1 salutation [20141127-99991231]Mrs 1 salutation [20151127-99991231]Mr

Case 2: 2 salutation [20141127-99991231]Mrs 2 salutation [20141127-99991231]Mr

Since you already have a solution to the Case 2: In addition to your select for case 2 you will need to compare the dates of the user that has changed id/department. Hope that helps.

Regards

Kiril

[devaprakash_b]

Hi Kiril,

Thanks for your reply. I am not getting the logic to compare it. so can you help me

[Former Member]

D P,

Well, I am not sure if your date format (20141127) will work or you will need to split it to something

like:  'YYYY-MM-DD' but my idea was For example in

Case 1: 1 salutation [20141127-99991231]Mrs 1 salutation [20151127-99991231]Mr

take the value of the first validFrom 20141127 and compare it (<) with the second validFrom 20151127.

It is a simple approach, but I am trying not to over complicate things.

Kiril.