PDA

View Full Version : Get 0 rows if date is not in calendar

helenp
12-25-2011, 05:59 PM
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?

helenp
12-25-2011, 08:17 PM
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'

12-26-2011, 04:44 AM
that can't give you what you want. Cal_date can't be both values at the same time.

helenp
12-26-2011, 08:20 PM
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.

Old Pedant
12-27-2011, 02:11 AM
I think this result:

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:

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:

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.

helenp
12-27-2011, 09:45 AM
I think this result:

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

helenp
01-05-2012, 11:16 AM
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

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:

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?

helenp
01-05-2012, 01:34 PM
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'

helenp
01-05-2012, 01:38 PM
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'

helenp
01-05-2012, 02:38 PM
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

Old Pedant
01-05-2012, 09:22 PM
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...

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.

helenp
01-05-2012, 11:05 PM
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...

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:

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

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:

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

Old Pedant
01-06-2012, 12:34 AM
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.

helenp
01-06-2012, 11:31 AM
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.