remove overlapped time slots

I need get the list of instructors who do not have any classes between a given date and time .

I got the code but it does not work

SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 
AND EXISTS (SELECT 1 from instructors I WITH (NOLOCK) where deleted = 0 and I.instructorKey=u.userKey) 
AND EXISTS (SELECT 1 from userInstructorLicense UIL WITH (NOLOCK) 
where UIL.userKey = u.userKey and DATEDIFF(DAY,UIL.expirationdt,'2018-07-19 14:00:00.0')< 0 
and UIL.availStateKey = 1) 

and NOT  EXISTS ( 
	SELECT 1 FROM Session S WITH (NOLOCK) 
	INNER JOIN SessionUnit SU WITH (NOLOCK) ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM WITH (NOLOCK) on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey AND  (('2018-07-19 14:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) 
OR ('2018-07-19 16:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) ) ) 

ORDER BY U.lastname , U.firstname

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Taking a step back... Does this show anything ?
SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email, I.instructorKey,I.deleted, UIL.expirationdt, UIL.availStateKey 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
inner join instructors I WITH (NOLOCK) on I.instructorKey=u.userKey
inner join userInstructorLicense UIL WITH (NOLOCK) on UIL.userKey = u.userKey
WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 
/*
and NOT  EXISTS ( 
	SELECT 1 FROM Session S WITH (NOLOCK) 
	INNER JOIN SessionUnit SU WITH (NOLOCK) ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM WITH (NOLOCK) on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey AND  (('2018-07-19 14:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) 
OR ('2018-07-19 16:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) ) ) 
*/
ORDER BY U.lastname , U.firstname

Open in new window

For any of the Instructors your interested in ?
0
erikTsomikSystem Architect, CF programmer Author Commented:
It shows a lot of instructors.

I am specifically do not want to see 1 instructor because he has another session on the same date 2018-07-19 and his session starts at 2:00 pm and finished at 4:00pm
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without digging into your T-SQL (sorry), check out my article T-SQL: Identify bad dates in a time series for a code and image heavy demo on how to pull off identifying gaps and overlaps in a time series.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
@erikTsomik,

Thats OK, we can now uncomment the section of code to check times....

Step at a time :)
0
erikTsomikSystem Architect, CF programmer Author Commented:
@Jim Horn: Thank you
0
Mark WillsTopic AdvisorCommented:
might be better (for time check)


WHERE su.instructorKey = U.userKey AND  (( convert(datetime,SU.sessionStart) between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' )
OR (convert(datetime,SU.sessionEnd) between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) )
0
erikTsomikSystem Architect, CF programmer Author Commented:
@Mark Wills. No changes it produces the same result
0
Mark WillsTopic AdvisorCommented:
What do you mean "no changes"

Just to be sure we are on the same page, please try :
SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email, I.instructorKey,I.deleted, UIL.expirationdt, UIL.availStateKey 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
inner join instructors I WITH (NOLOCK) on I.instructorKey=u.userKey
inner join userInstructorLicense UIL WITH (NOLOCK) on UIL.userKey = u.userKey
WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 

and i.deleted = 0
and UIL.availStateKey = 1
--and DATEDIFF(DAY,UIL.expirationdt,'2018-07-19 14:00:00.0')< 0 

and NOT  EXISTS ( 
	SELECT 1 FROM Session S 
	INNER JOIN SessionUnit SU  ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM  on SM.sessionKey= S.sessionKey 
    WHERE su.instructorKey = U.userKey AND  (( convert(datetime,SU.sessionStart) between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) 
    OR (convert(datetime,SU.sessionEnd) between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) ) ) 

ORDER BY U.lastname , U.firstname

Open in new window

BTW what datatype are those session dates that need "convert(datetime,SU.sessionStart)" and shouldnt they have a style code ?
0
erikTsomikSystem Architect, CF programmer Author Commented:
@Mark Wills. Reran the query still getting the person that I should not see.

For the sessionStart and sessionend they are both datetime type
0
Mark WillsTopic AdvisorCommented:
Getting closer :)

This is good and thanks for your patience.

The error is the NOT EXISTS

as soon as it finds an instance, the EXISTS will bail, therefore if there is an existing entry for your instructor outside that specific period then it never gets a chance to check the other possibilities of the 'where' clause inside the NOT EXISTS.

So what you want to do is to get a count() of the number of times that where clause is satisfied inside your subquery
and 1 > ( 
	SELECT COUNT(*) as IDX 
	FROM Session S 
	INNER JOIN SessionUnit SU  ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM  on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey 
	AND  (( SU.sessionStart between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) 
	OR ( SU.sessionEnd between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) ) ) 

Open in new window

or we can load up the join conditions to limit the possibilities
and NOT EXISTS ( SELECT NULL 
                 FROM Session S 
                 INNER JOIN SessionUnit SU  ON S.SessionKey = SU.SessionKey 
                                           AND su.instructorKey = U.userKey 
                                           AND (( SU.sessionStart between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) 
                                            OR  ( SU.sessionEnd between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) )	
                 inner join sessionMap SM  on SM.sessionKey= S.sessionKey )  -- this last inner join is probably not needed

Open in new window

0
erikTsomikSystem Architect, CF programmer Author Commented:
The query is not working . I am still getting the same person I should not see
0
Mark WillsTopic AdvisorCommented:
I am an idiot :)

Of course the count will be > 1 because it is checking all instructors, and the same for not exists

At least the statement "The error is the NOT EXISTS" hold true...

we should be using a cross apply... so it checks each instructor

Back in a moment....
0
erikTsomikSystem Architect, CF programmer Author Commented:
Well I know that the instructor that I do not want to see only have 1 session at that time so I think the problem is datetime comparison. It just I cannot get it right
0
Mark WillsTopic AdvisorCommented:
Nah, we need to be checking that the instructor is not in that period....

More like
and I.instructor not in ( 
	SELECT su.instructor 
	FROM Session S 
	INNER JOIN SessionUnit SU  ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM  on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey 
	AND  (( SU.sessionStart between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) 
	OR ( SU.sessionEnd between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) ) ) 

Open in new window


But think the cross apply is maybe better because we gain visibility on any other instructors in that period as well
SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email, I.instructorKey, wrk.IDX 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
inner join instructors I WITH (NOLOCK) on I.instructorKey=u.userKey
inner join userInstructorLicense UIL WITH (NOLOCK) on UIL.userKey = u.userKey
cross apply (SELECT count(*) as IDX
             FROM Session S 
             INNER JOIN SessionUnit SU  ON S.SessionKey = SU.SessionKey and su.instructorKey = U.userKey 
	         WHERE (( SU.sessionStart between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) 
             OR     ( SU.sessionEnd between '2018-07-19 14:00:00' and '2018-07-19 16:00:00' ) ) ) wrk	

WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 

and i.deleted = 0
and UIL.availStateKey = 1
and DATEDIFF(DAY,UIL.expirationdt,'2018-07-19 14:00:00.0')< 0 
--and wrk.idx = 0                                    -- remove the comments after testing

ORDER BY U.lastname , U.firstname

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
erikTsomikSystem Architect, CF programmer Author Commented:
thank you it seems like its working but I would need more testing. So the problem was the EXISTS because it was only evaluated the one record?
0
Mark WillsTopic AdvisorCommented:
Yep, the problem was EXISTS

What we needed to do was identify if a particular instructor existed - having the subquery like that it could have been ANY instructor - and EXISTS will exit checking as soon as it has a matching condition. So, never really had a chance of getting that one specific instructor.

But by checking for an instructor in a list of instructors for that period, then we found that one instructor (and in theory could be more).

Does that make sense ?
0
erikTsomikSystem Architect, CF programmer Author Commented:
I believe the exists should scan all records
0
Mark WillsTopic AdvisorCommented:
Unfortunately it doesnt - it simply checks and finishes at the first encounter.... Need to consider it more like "= any"

Which is actually its underlying strength (performance wise) and why it doesnt matter if you select 1 or NULL or * - it isnt returning anything other than a boolean

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/exists-transact-sql?view=sql-server-2017
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.