finding end dates with start dates and durations- skip dates on another table
I am dealing with “due” dates.
On one table I have this: Start date and a duration.
In another table I have all days that should not be included in the duration (weekends and holidays- office is closed)
I have to figure out what the “end” date is- (the day it’s due).
The end date is the duration from the start date, plus any dates that should be skipped in between. An end date cannot fall on a date that should be skipped.
Here is the SQL for the two tables (simple versions of my tables)
___________________
CREATE TABLE IF NOT EXISTS `table_01` (
`table_01_id` int(11) NOT NULL AUTO_INCREMENT,
`start_date` date NOT NULL,
`duration` int(11) NOT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`table_01_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs AUTO_INCREMENT=5 ;
INSERT INTO `table_01` (`table_01_id`, `start_date`, `duration`, `end_date`) VALUES
(1, '2011-12-01', 5, NULL),
(2, '2011-12-03', 7, NULL),
(3, '2012-12-01', 2, NULL),
(4, '2012-12-03', 7, NULL);
CREATE TABLE IF NOT EXISTS `table_02` (
`table_02_id` int(11) NOT NULL AUTO_INCREMENT,
`skip_date` date NOT NULL,
PRIMARY KEY (`table_02_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs AUTO_INCREMENT=6 ;
INSERT INTO `table_02` (`table_02_id`, `skip_date`) VALUES
(1, '2011-12-03'),
(2, '2011-12-04'),
(3, '2011-12-06'),
(4, '2011-12-10'),
(5, '2011-12-11');
________________________________________________
Here are the “correct” answers:
table_01_id|start_date|duration|end_date
1|2011-12-01|5|2011-12-09
2|2011-12-03|7|2011-12-15
3|2012-12-01|2|2011-12-05
4|2012-12-03|7|2011-12-15
The only way I can figure this out, is to do a running total in SQL for every record
I can get it to do that, but I can’t figure out the SQL to update the end date without putting it in a temporary table first, and then taking that temporary table and updating the end date.
There are easily a thousand records that need this “end” date.
It seems a little extreme to do that. I have a gut feeling there is a simpler way to do this.
If I could JUST do this in PHP I would not post here.
It's part of a load process, and I have to do it in SQL
Any thoughts?
Any help will be much appreciated.
______
update:
I did not find a solution as I wanted. I ended up making a reference table with running totals and then updating using that :(
ah well.
Edited 2 time(s). Last edit at 12/13/2011 02:23PM by Marjorie Cantu.