[RESOLVED] calling smart guy, possible to break dates down like this?

sfullman

OK, I would not be surprised if sQL can do this 🙂

so I have a table of children (this is bare bones for the post)

id int(11) unsigned auto_increment primary key
firstname char(30)
lastname char(30)

the children are assigned to fosterhomes

table: ChildrenFosterhomes
children_id int(11)
fosterhomes_id int(11) //this + previous = primary key
dateassigned datetime,
datereleased datetime

it's not uncommon to have a foster child discharged from one home and reassigned to another; or to run away for 5 days and be re-assigned to the same home; records might look like this for child #1:

children_id	fosterhomes_id	dateassigned	datereleased
1	5	2008-01-01	2008-01-15
1	5	2008-01-20	2008-05-17 //left for 5 days, re-assigned
1	4	2008-05-18	0000-00-00 //re-assigned to fosterhome#4, 0000.. means still there

NOW, children also have levels of care (Basic, Moderate, Specialized etc) which the state can change from time to time, so for child #1

table: ChildrenLocs
children_id int(11)
locs_id int(11) //this + previous = primary key
startdate datetime
expirationdate datetime

records might look like this for child #1:

children_id	locs_id
1	1	2008-01-01	2008-01-27	//basic
1	2	2008-01-28	2008-04-30	//moved up to moderate
1	1	2008-05-01	0000-00-00	//back to basic; open, no planned expiration..

so, here in English is what I'd like to create for child #1:

begindate	enddate	fosterhome	loc
2008-01-01	2008-01-15	5	1	//in home, loc basic
2008-01-16	2008-01-19	NULL	1	//not assigned
2008-01-20	2008-01-27	5	1	//back in home, loc still basic
2008-01-28	2008-04-30	5	2	//new loc
2008-05-01	2008-05-17	5	1	//back to basic
2008-05-18	0000-00-00	4	1	//still basic loc, but new foster home

essentially what i'm doing here is interlacing (best term I can think) the two tables of date ranges?

my quesiton, is this even remotely possible in mysql 5.0 - and is it possible elegantly?
i'd appreciate any feedback on this I can get, though my table structure has to be separate (can't interlace the actual tables unfortunately) -
i'd even appreciate if this can be done through temporary tables or multiple queries.

Thanks,
Samuel

Weedpacket

sfullman wrote:
(can't interlace the actual tables unfortunately)

Look up the use of UNION in SQL queries.

sfullman

Actually I figured this one out very elegantly and actually need to join the tables, not union them for the comparison.

To understand this you'll need to see the sql.txt attachment below - it has 3 simple tables called
a_children
a_ChildrenFosterhomes
a_ChildrenLocs

plus sample data.

So, what I did was left join both tables and effectively cross all the time periods, i.e. if the child had 4 levels of care and 3 foster homes over the period, there'd be 12 permutations:

SELECT a.firstname, a.lastname,
b.dateassigned, b.datereleased,
c.Startdate, c.expirationdate
FROM
a_children a LEFT JOIN a_ChildrenFosterhomes b ON a.id=b.childrenid
LEFT JOIN a_ChildrenLocs c ON a.id=c.childrenid

#that gets me this
samuel 	fullman 	2008-05-18 	2008-11-26 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-05-18 	2008-11-26 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-05-18 	2008-11-26 	2008-01-28 	2008-04-30
samuel 	fullman 	2008-01-01 	2008-01-15 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-01-01 	2008-01-15 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-01-01 	2008-01-15 	2008-01-28 	2008-04-30
samuel 	fullman 	2008-01-20 	2008-05-17 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-01-28 	2008-04-30
samuel 	fullman 	2008-11-27 	0000-00-00 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-11-27 	0000-00-00 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-11-27 	0000-00-00 	2008-01-28 	2008-04-30
joe 	law 	NULL 	NULL 	NULL 	NULL

but that's one big happy mess. It's time to filter down to records that only have some type of date overlap between Level of Care record and Foster home assignment, which we handle in the where clause:

SELECT a.firstname, a.lastname,
b.dateassigned, b.datereleased,
c.Startdate, c.expirationdate
FROM
a_children a LEFT JOIN a_ChildrenFosterhomes b ON a.id=b.childrenid
LEFT JOIN a_ChildrenLocs c ON a.id=c.childrenid
WHERE
(b.DateReleased >= c.StartDate OR !b.DateReleased) AND
(b.DateAssigned <= c.ExpirationDate OR !c.ExpirationDate)

#Note how I have !b.DateReleased - this is because open-ended will be 0000-00-00 - I could have allowed null values too.  Here is what I get:
samuel 	fullman 	2008-01-01 	2008-01-15 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-01-20 	2008-05-17 	2007-01-01 	2008-01-27
samuel 	fullman 	2008-05-18 	2008-11-26 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-11-27 	0000-00-00 	2008-05-01 	0000-00-00
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-01-28 	2008-04-30

Hmm.. we're down to 6 events which is what the data suggests, but now we need to select the "inner" date of the overlap, i.e. if fosterhome assignment was from 1/1/2008 to 5/31/2008, and the level of care was effective 3/1/2008 to 9/30/2008, then our lap period is from 3/1 - 5/31. So, the greater of the starting dates and the lesser of the ending dates. Separating calculated fields with a '---' below, I do the following:

SELECT a.firstname, a.lastname,
b.dateassigned, b.datereleased,
c.Startdate, c.expirationdate, '---',
IF(b.DateAssigned > c.StartDate, b.DateAssigned, c.StartDate) AS PdStart,
IF(b.DateReleased < c.ExpirationDate, b.DateReleased, c.ExpirationDate) AS PdEnd,
c.locsid AS CareLevel

FROM
a_children a LEFT JOIN a_ChildrenFosterhomes b ON a.id=b.childrenid
LEFT JOIN a_ChildrenLocs c ON a.id=c.childrenid
WHERE
(b.DateReleased >= c.StartDate OR !b.DateReleased) AND
(b.DateAssigned <= c.ExpirationDate OR !c.ExpirationDate)
ORDER BY PdStart ASC

#which gets me this - I kept the first 4 "real" dates for comparison - plus I sorted by PdStart
samuel 	fullman 	2008-01-01 	2008-01-15 	2007-01-01 	2008-01-27 	--- 	2008-01-01 	2008-01-15 	1
samuel 	fullman 	2008-01-20 	2008-05-17 	2007-01-01 	2008-01-27 	--- 	2008-01-20 	2008-01-27 	1
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-01-28 	2008-04-30 	--- 	2008-01-28 	2008-04-30 	2
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-05-01 	0000-00-00 	--- 	2008-05-01 	0000-00-00 	1
samuel 	fullman 	2008-05-18 	2008-11-26 	2008-05-01 	0000-00-00 	--- 	2008-05-18 	0000-00-00 	1
samuel 	fullman 	2008-11-27 	0000-00-00 	2008-05-01 	0000-00-00 	--- 	2008-11-27 	0000-00-00 	1

OK, now I've got those pesky 0000-00-00 values where they shouldn't be - that can be changed with a slight mod of how we evaluate PdEnd:

SELECT a.firstname, a.lastname,
b.dateassigned, b.datereleased,
c.Startdate, c.expirationdate, '---',
b.fosterhomesid,
IF(b.DateAssigned > c.StartDate, b.DateAssigned, c.StartDate) AS PdStart,
IF(b.DateReleased < c.ExpirationDate OR c.ExpirationDate=0 /* no expiration of level of care */, b.DateReleased, c.ExpirationDate) AS PdEnd,
c.locsid AS CareLevel
FROM
a_children a LEFT JOIN a_ChildrenFosterhomes b ON a.id=b.childrenid
LEFT JOIN a_ChildrenLocs c ON a.id=c.childrenid
WHERE
(b.DateReleased >= c.StartDate OR !b.DateReleased) AND
(b.DateAssigned <= c.ExpirationDate OR !c.ExpirationDate)
ORDER BY PdStart ASC

#see the comment above "no expiration of level of care" - that gets me this:
samuel 	fullman 	2008-01-01 	2008-01-15 	2007-01-01 	2008-01-27 	--- 	5 	2008-01-01 	2008-01-15 	1
samuel 	fullman 	2008-01-20 	2008-05-17 	2007-01-01 	2008-01-27 	--- 	5 	2008-01-20 	2008-01-27 	1
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-01-28 	2008-04-30 	--- 	5 	2008-01-28 	2008-04-30 	2
samuel 	fullman 	2008-01-20 	2008-05-17 	2008-05-01 	0000-00-00 	--- 	5 	2008-05-01 	2008-05-17 	1
samuel 	fullman 	2008-05-18 	2008-11-26 	2008-05-01 	0000-00-00 	--- 	4 	2008-05-18 	2008-11-26 	1
samuel 	fullman 	2008-11-27 	0000-00-00 	2008-05-01 	0000-00-00 	--- 	5 	2008-11-27 	0000-00-00 	1

WOW! Any time the fosterhome assignment changes (in one table) or the level of care changes (in another table), it will be a distinct record in the join, and I am calculating the start and end of the event. It's an understatement to say I'm happy about that. What is especially gratifying is that there is a gap between the 1st and 2nd record, which corresponds to the child being absent those times. Further testing revealed that records showing a non-contiguous level of care would also create a gap (unwanted) - so I have to make sure that the level of care is always there.

In the final analysis, this could be extrapolated to THREE or more tables to interlace chronological events even more (like let's say the daily perdiem rate the state pays on any level of care changes from time to time - it does but rarely). However, in that case I'd calculate the fields to the right of the '---' using php versus complex IF() statements using MySQL. But the principle itself seems sound by crossing all combos between tables, filtering by overlap, and then determining the common start and end date.

Hope this helps someone else using MySQL dates for chronological records.

Samuel Fullman
Compass Point Media (definitely a plug)