SQL query select data between two date fields

0
votes

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
4
sqlselectdatedatefield
Post the rows that are returned when one date is taken out (only a few rows and only the columns userid, programstartdate, programenddate are needed). – Eric J.
Is it possible that none of the confirmed, program type 1 programs begin on or after January 2, 2009? – David Andres
adding to Eric J.'s suggestion, when selecting the rows, order by WP.PROGRAMSTARTDATE desc – akf
Are you sure there are results that have a startdate greater than 2009-01-02 and an enddate less than 2009-09-15? – lomaxx
@mattgcon: you're going to hate me...but could you please add PROGRAMCODE to the result set. =) – David Andres

4 Answers

3
votes

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.

1
votes

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.

0
votes

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.

0
votes

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.