-
problem with where statement
I am having problems with a where statement. I need to have the query bring back results if any of these cases are true. but it only seems to be bringing back instances of where the first statement is true.
where ((t.enrolleddate is null and t.withdrawldate is null)or(t.enrolleddate <= t.classdate and t.withdrawldate is null) or (t.enrolleddate is null and t.withdrawldate >=t.classdate) or (t.enrolleddate <=t.classdate and t.withdrawldate >=t.classdate))
If i run each individual where clause it works ... but when i combine them it does not. can someone please help me
-
Do you have space around first OR statement?
-
White space shouldnt matter.. but i tested it with spaces still with no luck
-
here is my whole query
SELECT d.classID, d.dateID, d.classDate, p.participantID, n.hoursAttended, p.enrolleddate,p.withdrawldate
, CASE WHEN n.hoursAttended IS NOT NULL THEN 1
WHEN d.classDate <= GETDATE() THEN 0
ELSE 2
END AS 'status'
, CASE WHEN n.hoursAttended IS NOT NULL THEN CONVERT(varchar(50), n.hoursAttended)
WHEN d.classDate <= GETDATE() THEN 'Data not yet entered'
ELSE 'N/A'
END AS 'hoursDisplay'
INTO #all
FROM classes_dates d
JOIN classes_info i ON d.classID = i.classID AND ISNULL(i.active, 0) = 1 AND i.className NOT LIKE 'Lunch%'
JOIN classes_participants p ON d.classID = p.classID --AND ISNULL(p.active, 0) = 1
LEFT JOIN participants_progressNotes n ON d.classID = n.classID AND p.participantID = n.participantID AND d.dateID = n.dateID
WHERE d.classDate BETWEEN @startDate AND DATEADD(dd, 1, @endDate)
AND ISNULL(d.active, 0) = 1
SELECT a.classID, a.dateID, a.classDate, a.participantID, a.hoursAttended, a.status, a.hoursDisplay, isnull(a.enrolleddate,'01-01-1900 00:00:00.000')'enrolleddate',isnull(a.withdrawldat e,'2999-01-01 00:00:00.000')'withdrawldate'
, pi.FName, pi.LName
, ci.team, ci.instructorID, ci.instructorID2, ci.className, ci.startDate, dbo.dayName(ci.dayOfWeek) 'classDay'
, ISNULL(RTRIM(u.LName) + ', ' + RTRIM(u.FName), 'None Assigned') 'instructor', RTRIM(u2.LName) + ', ' + RTRIM(u2.FName) 'instructor2'
FROM #all a
JOIN classes_info ci ON a.classID = ci.classID
JOIN participants_info pi ON a.participantID = pi.participantID
LEFT JOIN sec_users u ON ci.instructorID = u.userID
LEFT JOIN sec_users u2 ON ci.instructorID2 = u2.userID
WHERE a.status = 0 and (withdrawldate>=a.classdate and enrolleddate<=a.classdate)
--
ORDER BY enrolleddate,withdrawldate,a.classID, a.classDate, a.participantID
-
What do you mean doesn't work? Got error or wrong result?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|