Help with some date code.

fotofx

I need to calculate how many days fall within holiday period.

I have an arrival date and a departure date.

Lets say 11/10/2009 to 11/26/2009

I have a holiday period of 11/24/2009 to 11/29/2009 and 12/23/2009 to 1/3/2010

I Need to figure how many days would fall into the holiday period (3) in this example.

I would like to do it using mysql to store the holiday dates so I can already put next years in also. Any thoughts on how you would code this?

I am figuring it would have to be broken down by day with each day tested against the database but there has to be an easier way.

Steve

johanafm

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

fotofx

No, I am not trying to calculate the number of days between two dates. Please reread my question. I just need to know how many of the days fall within the holiday period. I already know the number of days between the dates..

Thanks
Steve

johanafm

Not between those dates, between the other dates.

ifchecks on what part of the holiday is within the other dates, then diff on the appropriate dates. Sum if necessary.

fotofx

I am not seeing that in the linked page.. Am I missing something?

Any chance at an example?

Thanks
Steve

johanafm

Ok, this is not complete, you will have to add some more checks but it's probably self evident when you see the rest

CREATE TABLE a (
	dep DATE,
	arr DATE);

CREATE TABLE b )
	start DATE,
	end DATE);

INSERT INTO a VALUES ('2009-11-10', '2009-11-26');
INSERT INTO b VALUES ('2009-11-24', '2009-11-29'), ('2009-12-23', '2010-01-03');


SELECT SUM(CASE WHEN start < dep AND end > arr THEN DATEDIFF(end, start) + 1
		WHEN end > arr THEN DATEDIFF(arr, start) + 1 END) AS days
FROM a
INNER JOIN b ON start < arr AND end > dep

And I probably should have used <= instead of just < in some places... Do double check all "end case" value scenarios.