a better Sql Statement?

[Developer]

Hey there,

Would someone be able to help me sort out a better sql statement than the one I currently have, basically I want to be able to select two dates, start date and end date.

• I want to see if promotions already exists during the dates I have selected. there are several conditions, one
  
• the startdate could be between the two promotion dates that are already there
  
• the enddate could be between the two promotion dates that are already there
  
• the the startdate selected could be before the promotions startdate and the end date selected could be after the promotions end date.
  
• they could also be equal, etc
  
• I think you get the idea
  

this is my current sql statement, I know it's pretty ugly looking. Don't give me a hard time about it please.

SELECT COUNT(*) FROM Promotion WHERE ProductNo = '" & Product.ProductNo & "' AND ProductNo IN(SELECT ProductNo FROM Promotion WHERE ([StartDate] < #" & Format(StartDate, "MM/dd/yyyy HH:mm:ss") & "# AND [EndDate] > #" & Format(StartDate, "MM/dd/yyyy HH:mm:ss") & "#) OR ([StartDate] < #" & Format(EndDate, "MM/dd/yyyy HH:mm:ss") & "# AND [EndDate] > #" & Format(EndDate, "MM/dd/yyyy HH:mm:ss") & "#) OR ([StartDate] > #" & Format(StartDate, "MM/dd/yyyy HH:mm:ss") & "# AND [EndDate] < #" & Format(EndDate, "MM/dd/yyyy HH:mm:ss") & "#) OR (StartDate = #" & Format(StartDate, "MM/dd/yyyy HH:mm:ss") & "# OR EndDate = #" & Format(StartDate, "MM/dd/yyyy HH:mm:ss") & "#) OR (StartDate = #" & Format(EndDate, "MM/dd/yyyy HH:mm:ss") & "# OR EndDate = #" & Format(EndDate, "MM/dd/yyyy HH:mm:ss") & "#))

Thanks in advance

 

[Developer]

someone please help, I was previously using the between statement but it doesn't take other dates outside the range or equal to the range into account.

 

[Arg81]

Between is not good for date ranges, because of Exclusive and Inclusive. There are a number of posts on this forum explaining these issues.

The way that you have used in your first post is the correct way (i.e. WHERE Date >=@StartDate AND <@EndDate)

If you want the Date to be included in the dates specified for @StartDate and @EndDate, use >= AND <=


Is this SQL created and used at code level? I'd suggest a simple way of immediately cleaning it up is to create the query on your TableAdapter, and then call it from code specifying the parameters.

Not really able to offer any other suggestions at the moment because I don't know how you are calling this query or what your tables are, but at the moment it seems you are using a query within a query, when it might be better to use a join statement (especially seeing as all you are doing is a count).