SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID FROM RequestData rd INNER JOIN Employee e ON e.EmployeeID = rd.EmployeeID WHERE (rd.RequestDate = @.ReqDate AND rd.EndRequestDate IS NOT NULL) OR (rd.RequestDate = @.ReqDate AND rd.EndRequestDate = @.EndReq)
How can I do something like the above query? I want to look for rd.RequestDate = @.ReqDate and rd.EndRequestDate = something(doesn't matter as long as it has a value). And in the same token I want to search for where they both equal some parameter.
In my app. a user can select a single date on a calendar which I would plug into the @.ReqDate in the first part of the WHERE clause or a week (where I take the first day of the selected week and the last day of the selected week and plug them in the @.ReqDate and @.EndReq in the second part of the WHERE clause.)
As I understand, you want to do is: if the 'rd.EndRequestDate' field is not null then search with only rd.RequestDate; else search with both date. Then a 'ISNULL' function can help you:
SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID
FROM RequestData rd INNER JOIN Employee e
ON e.EmployeeID = rd.EmployeeID
WHERE rd.RequestDate = @.ReqDate
AND rd.EndRequestDate = ISNULL(EndRequestDate,@.EndReq)
That works, but I also need to adjust my SP a little bit, I need it to do the following: The first where can do a rd.RequestDate = @.ReqDate where EndRequest isn't NULL. However, if both Dates are filled in then I need to do something like the following: rd.RequestDate >= @.ReqDate AND EndRequest <= @.EndReq. Any ideas on this?
|||Got it-- just added an OR Clause to my SP--
SELECT rd.RequestID, rd.RequestDate, rd.EndRequestDate, rd.HoursSelected, rd.BeginTime, rd.EndTime, rd.Approved, rd.ApprovedBy, e.FirstName, e.LastName, e.DeptID
FROM RequestData rd INNER JOIN Employee e ON e.EmployeeID = rd.EmployeeID
WHERE ((rd.RequestDate = @.ReqDate) AND (rd.EndRequestDate = ISNULL(rd.EndRequestDate, @.EndReq))) OR
((rd.RequestDate >= @.ReqDate) AND (rd.EndRequestDate <= @.EndReq))
