asked on

Selecting Records Between Time Ranges On Dates

I ma trying to get a set of instructors where instructors do not have reservation on the date and time
for example on the date 1/1/2014 from 6:00AM to 8:00AM

what am I doing wrong

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  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 (
                ((<cfqueryparam value="#submittedStartDateTime#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -14, SU.sessionStart) 
					and DateAdd(minute, 14, SU.sessionEnd) OR <cfqueryparam value="#submittedEndDateTime#" cfsqltype="cf_sql_timestamp"> between DateAdd(minute, -14, SU.sessionStart) and DateAdd(minute, 14, SU.sessionEnd)) 
				)
                OR ((<cfqueryparam value="#submittedStartDateTime#" cfsqltype="cf_sql_timestamp"> between SU.sessionStart and SU.sessionEnd 
						OR <cfqueryparam value="#submittedEndDateTime#" cfsqltype="cf_sql_timestamp"> between SU.sessionStart and SU.sessionEnd) 
				)
          )
			AND (
        		SELECT COUNT(1)
				FROM sessionMap SMX WITH (NOLOCK)
				WHERE S.sessionKey = SMX.sessionKey
			) > 0
			)
			
		OR (U.userKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#qbtwSession.instructorKey#">)	
		
	ORDER BY U.lastname
		, U.firstname

Open in new window

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

erikTsomik

ASKER

I was wondering if the logic is correct I want only include people who do not have any reservation for a specific time and also the people who has not been reserved at all

_agx_

Well the basic construct:

SELECT ...
FROM Users u
WHERE NOT EXISTS
(
... existing reservations for u.UserID and date range ...
)

is correct. But the original date logic looked a bit off. The new logic should fix that.

You can test it by running the subquery separately. Just set variables for the userID and dates you wan to test.

erikTsomik

ASKER

How would I also include instructors who has not reservations, because I just want exclude the once that have reservations for that date/time span, but also need the once who has no reservation maybe need to change something

_agx_

Gah... sorry, that came out wrong. I meant to say:

You're already doing that. Because you're using a subquery and NOT EXISTS, you're only excluding ones with conflicting reservations. Assuming no errors in the rest of the query, the outer query will still return instructors with no reservations at all or ones with no conflicting reservations during that time period.

erikTsomik

ASKER

when using the time range do I need to end time to be 7:59 or 8:01

_agx_

Your subquery is checking for conflictingreservations. So use whatever time would constitute a conflict. For example, if you want exclude the user if there's a reservation for 7:59 - then use that time.

Edit: I'm heading out. I'll check back tomorrow.

erikTsomik

ASKER

OK.thanks