Results 1 to 5 of 5

Thread: problem with where statement

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Do you have space around first OR statement?

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    White space shouldnt matter.. but i tested it with spaces still with no luck

  4. #4
    Join Date
    Feb 2009
    Posts
    3

    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

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    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
  •