SQL query select data between two date fields

Question

I am trying to select data from a table, using two date fields (startdate and enddate). With both date fields in the where clause no results are returned, with the startdate field taken out results are returned, what is going on? Can anyone help with this dilemna

My code is:

   SELECT WPP.USERID, 
          WPI.EMAIL, 
          WPI.FIRSTNAME, 
          WPI.LASTNAME, 
          WPI.INSTITUTION 
     FROM WEBPROGRAMPARTICIPANTS WPP 
     JOIN WEBPERSONALINFO WPI ON WPP.USERID = WPI.USERID 
     JOIN WEBPROGRAMS WP ON WPP.PROGRAMCODE = WP.PROGRAMCODE 
    WHERE CONFIRMED = 1 
      AND WP.PROGRAMTYPE IN ('1') 
      AND WP.PROGRAMSTARTDATE >= '2009-01-02' 
      AND WP.PROGRAMENDDATE < '2009-09-15'
 GROUP BY WPP.USERID, 
          WPI.EMAIL, 
          WPI.FIRSTNAME, 
          WPI.LASTNAME, 
          WPI.INSTITUTION 
   HAVING COUNT(WPP.PROGRAMCODE) > 1 
 ORDER BY WPP.USERID,
          WPI.EMAIL

EDIT:

here is a result set to look at

USERID PROGRAMSTARTDATE        PROGRAMENDDATE
------ ----------------------- -----------------------
26167  2009-03-09 00:00:00.000 2009-06-12 00:00:00.000 
26362  2009-03-09 00:00:00.000 2009-06-12 00:00:00.000 
26411  2009-03-09 00:00:00.000 2009-06-12 00:00:00.000 
26491  2009-03-09 00:00:00.000 2009-06-12 00:00:00.000

Answer 1

Because you have no records in your DB with

CONFIRMED = 1 AND WP.PROGRAMTYPE IN ('1') WP.PROGRAMSTARTDATE >= '2009-01-02' ??

EDIT: As @David Andres pointed out The "COUNT(WPP.PROGRAMCODE) > 1" clause looks like a candidate for the culprit.

Answer 2

Well, to be honest, it looks fine to me. I suspect the having statement filtering your results, or a combo of the where and having statement.

Answer 3

It looks like your answer is zero, as in "zero users participated in more than one program during that date range"

What you can do to confirm this is modify your query slightly to show how many programs each user did participate in:

   SELECT WPP.USERID, 
          WPI.EMAIL, 
          WPI.FIRSTNAME, 
          WPI.LASTNAME, 
          WPI.INSTITUTION,
          COUNT(WPP.PROGRAMCODE)
     FROM WEBPROGRAMPARTICIPANTS WPP 
     JOIN WEBPERSONALINFO WPI ON WPP.USERID = WPI.USERID 
     JOIN WEBPROGRAMS WP ON WPP.PROGRAMCODE = WP.PROGRAMCODE 
    WHERE CONFIRMED = 1 
      AND WP.PROGRAMTYPE IN ('1') 
      AND WP.PROGRAMSTARTDATE >= '2009-01-02' 
      AND WP.PROGRAMENDDATE < '2009-09-15'
 GROUP BY WPP.USERID, 
          WPI.EMAIL, 
          WPI.FIRSTNAME, 
          WPI.LASTNAME, 
          WPI.INSTITUTION 
 ORDER BY COUNT(WPP.PROGRAMCODE) DESC, 
          WPP.USERID,
          WPI.EMAIL

This should list for you every user and the count of projects for that date range, ordered first by the count, then by the criteria you originally set up. It removes the having clause, which seems to be the cause of your pain.

Answer 4

Gentlemen, thank you very much for all your help. Through analysis of all what you have said and trying the different suggestions out, I have actually found out that the code is right. However, during that time frame, users did not attend more than one program during that time frame. If I go back just one more year the expected data comes up. So basically it was just a simple matter of logical attendence factoring of the user to the programs.

Thank you all again.