please help with date range

[developer77]

Hello everyone,

I have the following stored procedure that passes the start and end date range as parameters. I'm trying to pull all date ranges from the parameters based on the start date and end date of students enrolled. For example, I have the following start dates and end dates in the sql server database

StartDate EndDate
12/01/2006 12/10/2006
11/30/2006 12/10/2006

When I enter in a start date parameter of 12/01/2006 and an end date parameter of 12/07/2006, I would like the second record above to be pulled since the parameters do fall between 11/30/2006 and 12/10/2006. Currently it only pulls only the 1st record since it meets the date logic in the stored procedure. Please help me fix the date logic so that it works correctly. I appreciate any help I can get. Thanks.


CREATE PROCEDURE students_enrolled
(

@StartDateRange datetime = null,
@EndDateRange datetime = null

)
AS
--
BEGIN

SELECT s.FirstName,
s.LastName,
s.MiddleName,
e.student_id
FROM tbl_Student s
left join tbl_Enrollment e on e.id = s.EnrollID
WHERE s.active_flg = 'Y'
AND ((e.StartDate >= IsNull(@StartDateRange, e.StartDate) and e.StartDate <= IsNull(@EndDateRange, e.StartDate)) or (e.EndDate >= IsNull(@StartDateRange, e.EndDate) and e.EndDate <= IsNull(@EndDateRange, e.EndDate)))
END
RETURN
GO

 

[Catadmin]

It's your OR statement that's causing the problem. It's pulling the first record because the ENDDate meets the requirements you're looking for.

Are both date sets from the same student or different students?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Sorry, I got that backwards. It's pulling the first record because of the STARTDate being equal to your OR clause.

If you want it to pull the second record, with the start date BEFORE your @StartDate, then you need to work on that part of the query.

...
WHERE    s.active_flg = 'Y'
  AND   (
(e.StartDate >= IsNull(@StartDateRange, e.StartDate) 
 [b]OR[/b] e.StartDate <= IsNull(@EndDateRange, e.StartDate)) 
OR
...

Right now, the query is doing exactly what you told it to do. Tell us, in english, what the startdate is supposed to be matched against.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[developer77]

Hi catadmin, thank you for your responses. The date sets are for different students. I think the startdate shouldn't match to anythign but just serves as a bases for the date range comparison. I would like to pull any dates between the parameter range that falls in the start and end dates in the database row. I hope this makes sense.

 

[Catadmin]

I would like to pull any dates between the parameter range that falls in the start and end dates in the database row.

Then your code is working correctly. If you want to pull the second record, you need to change your parameter range, not the code.

Set @StartDateRange = '11/01/2007' or something like it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[developer77]

Hi Catadmin,

That was what I thought the code was doing but the start and end date parameters I plugged in (@StartDateRange = 12/01/2006 and @EndDateRange= 12/10/2006) does fall between the 2nd date set in the database 11/30/2006 and 12/10/2006 but that record is not being pulled.

Thanks,
Sara