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 14 of 14
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Get 0 rows if date is not in calendar

    Hi I have a calendar that have inserted one row per day and property since 2012-01-01 until 2013-12-31, ie 2 years.
    And I need to calculate the price only if the arrival date and the departure date are in the calendar.
    What I want is that if arrival or departure date is not in the calendar as in below exampel I want as result an empty result, i.e 0 rows.

    So I am trying to do this:
    SELECT cal_date FROM calendar_table
    where Cal_date between '2013-12-25' AND '2014-01-05' AND property = 'Casa_Blanca_4'
    Gives me 7 rows, from December 25 until December 31
    also I tried to do this:
    SELECT cal_date FROM calendar_table
    where property = 'Casa_Blanca_4' AND ('2013-12-25' AND '2014-01-05' between Cal_date)
    gives me error
    SELECT cal_date FROM calendar_table WHERE property = 'Casa_Blanca_4' AND cal_date >= '2013-12-25' AND cal_date <= '2014-01-05'
    gives me 7 rows
    SELECT cal_date FROM calendar_table WHERE property = 'Casa_Blanca_4' AND cal_date BETWEEN '2013-12-25' AND '2014-01-05'
    Gives me 7 rows

    Is it posible to do?
    Thanks in advance

  • #2
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Lol, it was a lot easier than I thouht, just thought the wrong way:
    SELECT cal_date FROM calendar_table
    where Cal_date = '2013-12-25' AND Cal_date = '2014-01-05' AND property = 'Casa_Blanca_4'

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    that can't give you what you want. Cal_date can't be both values at the same time.

  • #4
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    that can't give you what you want. Cal_date can't be both values at the same time.
    Uups, thanks, that is correct as this gives me 0 rows, and these dates are in the base:
    SELECT cal_date FROM calendar_table
    where Cal_date = '2013-12-01' AND Cal_date = '2013-12-05' AND property = 'Casa_Blanca_4'

    So how can it be done?
    In a way, maybe its ok just to check if endate is in the database, but the correct would be to check that all days between selected dates are in the database using between or similar, just in case some date is missing when dates where introduced.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    I think this result:
    Code:
    SELECT cal_date FROM calendar_table WHERE property = 'Casa_Blanca_4' AND cal_date >= '2013-12-25' AND cal_date <= '2014-01-05'
    that, as you say, gives you 7 rows which is the right answer. With a small variation:
    Code:
    SELECT COUNT(*) FROM calendar_table 
    WHERE property = 'Casa_Blanca_4' AND cal_date >= '2013-12-25' AND cal_date <= '2014-01-05'
    the query will return you one row with a value of 7.

    You know, of course, that the actual number of days between those two dates (inclusive) is 12. So the fact that you get only 7, instead of 12, tells you what you needed to know. Doesn't it?

    But if you truly want zero rows, that's easy:
    Code:
    SELECT COUNT(*) FROM calendar_table 
    WHERE property = 'Casa_Blanca_4' 
    AND DATE(cal_date) BETWEEN '$arrivaldate' AND '$departuredate'
    HAVING COUNT(*) = ( 1 + DATEDIFF('$departuredate','$arrivaldate') )
    In your example ($arrivaldate = '2013-12-25', $departuredate = '2014-1-5'), the DATEDIFF will give a value of 11, add 1 to get 12. The COUNT(*) will be 7. Since the two numbers are not equal, the HAVING will prevent the query from returning any rows.
    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.

  • #6
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I think this result:[code]

    But if you truly want zero rows, that's easy:
    Code:
    SELECT COUNT(*) FROM calendar_table 
    WHERE property = 'Casa_Blanca_4' 
    AND DATE(cal_date) BETWEEN '$arrivaldate' AND '$departuredate'
    HAVING COUNT(*) = ( 1 + DATEDIFF('$departuredate','$arrivaldate') )
    In your example ($arrivaldate = '2013-12-25', $departuredate = '2014-1-5'), the DATEDIFF will give a value of 11, add 1 to get 12. The COUNT(*) will be 7. Since the two numbers are not equal, the HAVING will prevent the query from returning any rows.
    Thanks,
    yes I want zero in this case, but I only want zero if dates that are not in the database are selected in the form, if the dates are in the table I want them selected as normal.
    It looks at it works, thanks, will have to do tests
    Last edited by helenp; 12-27-2011 at 11:20 AM.

  • #7
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hello again,
    small problem, the mysql query you gave me works perfect when I check one property:
    This gives me 0 rows if dates are not in the data base, but if the dates are in the database I get the number of days, this select chooses a property

    Code:
     SELECT TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT( * )
    FROM calendar_table
    WHERE property = 'Casa_Blanca_4'
    AND DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'
    HAVING COUNT( * ) = ( 1 + DATEDIFF( '2012-01-10', '2012-01-01' ) )
    However I have a page that checks all properties so I just tried same script taking away the property clause like this:
    Code:
    SELECT cal_date, TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT( * )
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'
    HAVING COUNT( * ) = ( 1 + DATEDIFF( '2012-01-10', '2012-01-01' ) )
    However it gives me 0 row if dates are not in database but also gives 0 rows if dates are in database....

    So how can I check to see if choosen dates are in the database?
    thanks in advance
    Last edited by helenp; 01-05-2012 at 11:20 AM.

  • #8
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Think I got it,
    looks its enough to do this:
    SELECT TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'

  • #9
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Nop, it wont do,
    this gives me 40 and I only have until 31 december 2013 in the database
    SELECT TO_DAYS( '2014-01-10' ) - TO_DAYS( '2013-12-01' ) AS dias
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2013-12-01'
    AND '2014-01-10'

  • #10
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Puf, think I got it this time, please let me know if not ok:

    SELECT property, TO_DAYS( '2014-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT( * )
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'
    GROUP BY property
    HAVING COUNT( * ) = ( 1 + DATEDIFF( '2014-01-10', '2012-01-01' ) )
    this gives me 0 and with dates in the database I get the days

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Looks right to me, and you can even simplify it a little if you wish.

    That value of computed/aliased fields are not available in the WHERE clause, but they are okay in the HAVING.

    So...
    Code:
    SELECT property, TO_DAYS( '2014-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT(*)  AS theCount
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01' AND '2012-01-10'
    GROUP BY property
    HAVING theCount = ( 1 + dias )
    Why did you use TO_DAYS in one case and DATEDIFF in another?

    Either should work, but it would look better to be consistent. Of course, now that I've eliminated the need for the DATEDIFF, it doesn't matter at all.
    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:

    helenp (02-04-2012)

  • #12
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Looks right to me, and you can even simplify it a little if you wish.

    That value of computed/aliased fields are not available in the WHERE clause, but they are okay in the HAVING.

    So...
    Code:
    SELECT property, TO_DAYS( '2014-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT(*)  AS theCount
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01' AND '2012-01-10'
    GROUP BY property
    HAVING theCount = ( 1 + dias )
    Why did you use TO_DAYS in one case and DATEDIFF in another?

    Either should work, but it would look better to be consistent. Of course, now that I've eliminated the need for the DATEDIFF, it doesn't matter at all.
    Honestly, I had to_days from the beginning as I need that to get the nights for a booking not the days that are always one more,
    and when you helped me to check if arrival and departure dates are in table for 1 property you added the datediff,
    this is your original query:
    Code:
    SELECT TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT( * )
    FROM calendar_table
    WHERE property = 'Casa_Blanca_4'
    AND DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'
    HAVING COUNT( * ) = ( 1 + DATEDIFF( '2012-01-10', '2012-01-01' ) )
    This returns:
    dias COUNT( * )
    9 10

    So I just tried to change it to use checking all properties at the same time
    The script you just passed me gives the same result without, 9 dias and count 10 and if I add a date that are not in table I get no result

    Code:
    SELECT property, TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT(*)  AS theCount
    FROM calendar_table
    WHERE DATE( cal_date )
    BETWEEN '2012-01-01' AND '2012-01-10'
    GROUP BY property
    HAVING theCount = ( 1 + dias )
    So I assume the second shorter is better and I can use it for one property also, just checked and it looked to work:
    Code:
    SELECT property, TO_DAYS( '2012-01-10' ) - TO_DAYS( '2012-01-01' ) AS dias, COUNT( * ) AS theCount
    FROM calendar_table
    WHERE property = 'Casa_Blanca_4' AND DATE( cal_date )
    BETWEEN '2012-01-01'
    AND '2012-01-10'
    HAVING theCount = ( 1 + dias )
    Thanks a lot

  • #13
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ahh...my fault, huh? <grin/>

    This is one of those queries where there are probably a half dozen ways to do it, so it really doesn't matter too much which you choose, so long as it works.
    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.

  • #14
    New Coder
    Join Date
    Oct 2011
    Posts
    65
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Ahh...my fault, huh? <grin/>

    This is one of those queries where there are probably a half dozen ways to do it, so it really doesn't matter too much which you choose, so long as it works.
    No sorry,
    having a second look I saw that the to_days where not there from the beginning as was irrevelant to the query, however as it was in my old query and I needed to see the days, I just added it the way it was to the query that already had datediff, so it was not your fault actually.
    Think I will you the second as it is shorter,
    thanks a lot for your help.


  •  

    Tags for this Thread

    Posting Permissions

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