Searching for multiple records per Person per Date with Overlap

SSAS and SSRS
1

Hello,

Relative SQL newbie here. I do a lot of work for my non-profit in reporting. I'm hitting the upper boundary of my limited sql skillset with this query:

Payroll office wants to find those individuals who have multiple records of work per day whose time overlaps:

To simplify, record of work has fields for Date, Department Code, Person ID, Start Time, End Time, plus other fields not important for this query

For example, given this table of data
DATE | DEPT| ID | Start | End
1/1/2017 | 1015 | 55 | 9:00AM | 10:00AM
1/1/2017 | 1015 | 55 | 10:00AM | 2:00PM
1/2/2017 | 1015 | 40 | 8:00AM | 9:00AM
1/2/2017 | 1040 | 40 | 8:30AM | 10:00AM
1/2/2017 | 1015 | 20 | 9:00AM | 12:00PM

I want the query to return:

1/2/2017 | 1015 | 40 | 8:00AM | 9:00AM
1/2/2017 | 1040 | 40 | 8:30AM | 10:00AM

These two records are on the same day, for the same person, but there is a time overlap.

I have no idea where to start. Help would be much appreciated

Note: In the database the data type for the start and end time fields is nvarchar. They are not connected with the date field.

2

Something like:

Query 
select a.[date]
      ,a.dept
      ,a.id
      ,a.[start]
      ,a.[end]
  from yourtable as a
 where exists (select 1
                 from yourtable as b
                where b.id=a.id
                  and not (b.[date]=a.[date]
                  and      b.dept=a.dept
                  and      b.[start]=a.[start]
                  and      b.[end]=a.[end]
                          )
                  and b.[date]=a.[date]
                  and b.[start]<a.[end]
                  and b.[end]>a.[start]
              )
;
3

Thank you for your help. That does make sense.

However, I am, getting the error Conversion failed when converting date and/or time from character string. when I run the query. The start and end times are both nvarchar(100). The times in the database are entered as hh:mmAM[PM], example, 09:30AM, but as type nvarchar.

I have tried convert(time, [start]) but that does not seem to work.

4

How about this:

Query 
select a.[date]
      ,a.dept
      ,a.id
      ,a.[start]
      ,a.[end]
  from yourtable as a
 where exists (select 1
                 from yourtable as b
                where b.id=a.id
                  and not (b.[date]=a.[date]
                  and      b.dept=a.dept
                  and      b.[start]=a.[start]
                  and      b.[end]=a.[end]
                          )
                  and convert(datetime,b.[date]+' '+b.[start],131)<convert(datetime,a.[date]+' '+a.[end],131)
                  and convert(datetime,b.[date]+' '+b.[end],131)>convert(datetime,a.[date]+' '+a.[start],131)
              )
;