Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post

    mysql query with conditional date range

    Hello all:

    I have the following mysql query statement:

    PHP Code:
    SELECT COUNT(*) as ttl_rows FROM (SELECT DISTINCT(date(timeentry)) AS dayCOUNT(*) AS total FROM history where MONTH(CAST(timeentry as date)) = MONTH(NOW()) AND YEAR(CAST(timeentry as date)) = YEAR(NOW()) GROUP BY DATE_FORMAT(timeentry'%d'ASC) as ttl_row 
    the statement performs a search for all records entered for the current month and thus would produced the following:

    PHP Code:

    day     total
    2012
    -12-01     1
    2012
    -12-02     6
    2012
    -12-04 4
    2012
    -12-05     3 
    As you can see, there is no record for 2012-12-03. My interest here is to modify the query to return all dates with the date range and where there is no entry, insert 0. So essentially, my return would be something like:

    PHP Code:
    day     total
    2012
    -12-01     1
    2012
    -12-02     6
    [COLOR="Red"]2012-12-03     0[/COLOR]
    2012-12-04 4
    2012
    -12-05     3 
    I hope this is achievable and that anyone here may be able to assist.

    Thanks
    Mossa
    Last edited by mbarandao; 12-06-2012 at 02:49 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    The only easy way to achieve this is to have a *SEPARATE* calendar table that has EVERY DATE (in you expected date range) in it.

    That is:
    Code:
    CREATE TABLE calendar (
        theDate DATE PRIMARY KEY
    );
    and then the contents of that table are just
    Code:
    theDate
    --------
    2011-1-1
    2011-1-2
    ...
    2019-12-31
    ...
    And now it is easy:
    Code:
    SELECT C.theDate, IFNULL( COUNT(*), 0 ) AS countForDate
    FROM calendar AS C LEFT JOIN history AS H
    ON C.theDate = DATE( CAST( H.timeentry AS DATE ) )
    WHERE YEAR(C.theDate) = YEAR(NOW()) AND MONTH(C.theDate) = MONTH(NOW())
    GROUP BY C.theDate
    ORDER BY C.theDate
    The WHERE clause can of course pick any range of date you want. Such as
    Code:
    WHERE C.theDate BETWEEN '2012-9-1' AND '2012-12-31'
    &&&&&&&&&&&&

    You can use a STORE PROCEDURE to create a TEMPORARY TABLE with the required range of dates (and then end the PROCEDURE with a SELECT * from the temporary table), but (a) you need to be good at stored procedures to make it efficient, (b) if you change your mind about WHAT you are selecting you have to rewrite the procedure, and (c) it's nowhere near as fast.

    SO it's worth the time to create your CALENDAR table, as above. You only have to do it once. I have one I created in 2003 that I am still using today, even after migrating it from another DB (from Access, as a matter of fact). And I find more and more uses for it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    mbarandao (12-07-2012)

  • #3
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Thank you Old Pedant. I appreciate your assistance and guidance. I'll attempt what you have suggested and note back here of the outcome.

    Thanks again!
    Mossa

  • #4
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Hello Old Pedant:

    I have done as instructed; all seems to be working fine with the exception of the returning of the "0" where the record is null on the history table. Instead of the zero, it returns 1. Any thought?

    I'm using the following sql:

    PHP Code:
    SELECT C.theDateIFNULLCOUNT(*), ) AS countForDate
    FROM calendar 
    AS C LEFT JOIN history AS H
    ON C
    .theDate DATECASTH.timeentry AS DATE ) )
    WHERE YEAR(C.theDate) = YEAR(NOW()) AND MONTH(C.theDate) = MONTH(NOW())
    GROUP BY C.theDate
    ORDER BY C
    .theDate 
    I'm thinking the IFNULL should be on the history table. But it seems that in the query above it is performed on the calendar table --I may be wrong. I would appreciate your thoughts on this

    Thanks
    Mossa
    Last edited by mbarandao; 12-07-2012 at 06:17 AM.

  • #5
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Solution fount! the following modification the sql resolved the issue

    PHP Code:
    SELECT C.theDateIFNULLsum(case when DATECASTH.timeentry AS DATE ) ) is null then 0 else 1 end), ) AS countForDate
    FROM calendar 
    AS C LEFT JOIN history AS H
    ON C
    .theDate DATECASTH.timeentry AS DATE ) )
    WHERE YEAR(C.theDate) = YEAR(NOW()) AND MONTH(C.theDate) = MONTH(NOW()) and
    C.theDate BETWEEN '2012-12-1' AND '2012-12-07'
    GROUP BY C.theDate
    ORDER BY C
    .theDate 
    Thank you!
    Last edited by mbarandao; 12-08-2012 at 08:29 PM.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    That is truly bizarre!

    That should not have affected the 0 count stuff, at all.

    I do this all the time, using just YEAR(NOW()) and MONTH(NOW()) and it works like a charm.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Nov 2010
    Location
    Washington DC
    Posts
    338
    Thanks
    22
    Thanked 1 Time in 1 Post
    Old Pedant,

    Not sure the cause of the problem, but I thank you for your help on this issue

    Best,
    Mossa


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •