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

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., Sep 16 '09 at 01:41
Is it possible that none of the confirmed, program type 1 programs begin on or after January 2, 2009? — David Andres, Sep 16 '09 at 01:41
adding to Eric J.'s suggestion, when selecting the rows, order by WP.PROGRAMSTARTDATE desc — akf, Sep 16 '09 at 01:43
Are you sure there are results that have a startdate greater than 2009-01-02 and an enddate less than 2009-09-15? — lomaxx, Sep 16 '09 at 01:44
There definitely are programs that start after that startdate, our programs only run for a couple of months at a time. — mattgcon, Sep 16 '09 at 01:45
@mattgcon, i dont doubt that there are programs, but are there userids that have more than 1 in that time period? — akf, Sep 16 '09 at 01:48
here is a result set to look at 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 — mattgcon, Sep 16 '09 at 01:48
@mattgcon: you're going to hate me...but could you please add PROGRAMCODE to the result set. =) — David Andres, Sep 16 '09 at 01:53
mattgcon, i added your data to the question and formatted it. please take a look to see if it formatted correctly. if so, can you tell us what it is from? i dont see any reference to userid,email,names etc. can you give col headers or something like that? — akf, Sep 16 '09 at 01:53
@akf, I imagine the column headers are userid, programstartdate, programenddate, which is what Eric J. initially asked for. — David Andres, Sep 16 '09 at 01:53
David that is correct AKF the headers are USERID, PROGRAMSTARTDATE, PROGRAMENDDATE — mattgcon, Sep 16 '09 at 01:56
that makes sense (read: i should have noticed that) - i have updated my edit. — akf, Sep 16 '09 at 02:06

score 3 · Accepted Answer · edited May 23 '17 at 12:13

3

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.

edited May 23 '17 at 12:13

Community

1
1

answered Sep 16 '09 at 01:40

Mitch Wheat

295,962
43
465
541

@Mitch: Feel free to include that if you like in your answer. – David Andres Sep 16 '09 at 01:51
The having clause is pretty simple, yet very much needed. What I need to accomplish is to return a result set of users that have participated in more than one program, over a course of some date range. – mattgcon Sep 16 '09 at 01:58
@mattgcon: at this point it's just a potential cause of your woes. Bear in mind you're limiting the result set to only those that are confirmed and are in program type 1, and then further limited this set to those users who participated in several programs. Try taking the HAVING clause out first and see if the results match better to your expectations. You may also want to modify your count to COUNT(DISTINCT WPP.PROGRAMCODE) just to ensure a user hasn't enrolled within the same program more than once. – David Andres Sep 16 '09 at 02:01

score 1 · Answer 2 · answered Sep 16 '09 at 01:43

1

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.

answered Sep 16 '09 at 01:43

Darthg8r

12,377
15
63
100

akf · Answer 3 · 2009-09-16T02:12:50.077

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.

score 0 · Answer 4 · answered Sep 16 '09 at 02:11

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.

SQL query select data between two date fields

4 Answers4