continuous records - question for REEEALLLY smart guy :)

sfullman

I'm not entirely convinced this is possible, but I've been surprised before..

Take the following table:

Child_ID    Assigned  Released
1    2007-07-25    2008-01-13
1    2008-01-14    2008-01-29
1    2008-01-30    2009-01-15 *
1    2009-01-25    2009-10-01
1    2009-10-02    9999-99-99

this is an assignment history for child id#1. one record closes on a day and the next record starts on the NEXT day, meaning it is contiguous. note that there was a gap between 1/16/2009 and 1/19/25.

(btw 9999-99-99 means the assignment is open and therefore "till forever")

My question(s) are:
1. is there a way to query either "find child id from this table WHERE there is a gap in their assignment between dateA and dateB" (for example, 6/15/2008 and 3/30/2009 - there would be)
2. is there a way to actually LIST the gaps in assignment for child N, in this case just one "pseudo period" of 2009-01-16 to 2009-01-19

THANK YOU for your experience and assistance

Samuel

sfullman

After I posted this I had an idea which turned into a tutorial, the first question IS POSSIBLE, though there may be a more elegant way to do it.

I'm still wondering if it's possible to create a listing of gaps as a series of rows..

#make a table and insert data
create table diff(ChildID INT(5) unsigned not null default 1, Date1 Date, Date2 Date);
INSERT INTO diff SET ChildID=1, Date1='2007-07-25', Date2='2008-01-13';
INSERT INTO diff SET ChildID=1, Date1='2008-01-14', Date2='2008-01-29';
INSERT INTO diff SET ChildID=1, Date1='2008-01-30', Date2='2009-01-15';
INSERT INTO diff SET ChildID=1, Date1='2009-01-25', Date2='2009-10-01';
INSERT INTO diff SET ChildID=1, Date1='2009-10-02', Date2='9999-99-99';


#first show the dates that fall in a range.  my range is 2009-01-01 to 2009-12-31 (the year there was a gap)
SELECT Date1, Date2
FROM diff
WHERE 
Date1<='2009-12-31' /* end of period */ AND
Date2>='2009-01-01' /* start of period */ AND ChildID=1

#now add the time difference to demonstrate the math - 4th column is fixed
SELECT
IF(Date1 <'2009-01-01', '2009-01-01', Date1) AS StartInRange,
IF(Date2 >'2009-12-31', '2009-12-31', Date2) AS EndInRange,
DATEDIFF(
	IF(Date2 >'2009-12-31', '2009-12-31', Date2),
	IF(Date1 <'2009-01-01', '2009-01-01', Date1)
) AS DaysInPeriod,
DATEDIFF('2009-12-31', '2009-01-01') AS OverallDayCount
FROM diff
WHERE 
Date1<='2009-12-31' /* end of period */ AND
Date2>='2009-01-01' /* start of period */ AND ChildID=1

#next we group and sum - note the disparity! the difference between 364 and 353 is 11, but the actual days missed between 1/16 and 1/24 inclusive is 9 days..
SELECT
SUM(
DATEDIFF(
	IF(Date2 >'2009-12-31', '2009-12-31', Date2),
	IF(Date1 <'2009-01-01', '2009-01-01', Date1)
)) AS DaysAssigned,
DATEDIFF('2009-12-31', '2009-01-01') AS OverallDayCount
FROM diff
WHERE 
Date1<='2009-12-31' /* end of period */ AND
Date2>='2009-01-01' /* start of period */ AND ChildID=1
GROUP BY ChildID

#the difference is caused by the fact that for n records in a range, we lose n-1 daysin the calculation; i.e. DIFF(1-10) is 9, and DIFF(11-20) is 9, or 18, however DIFF(1-20) is 19
#So, let's do a little more math
SELECT
SUM(
DATEDIFF(
	IF(Date2 >'2009-12-31', '2009-12-31', Date2),
	IF(Date1 <'2009-01-01', '2009-01-01', Date1)
)) + COUNT(*)-1 AS DaysAssigned,
DATEDIFF('2009-12-31', '2009-01-01') AS OverallDayCount
FROM diff
WHERE 
Date1<='2009-12-31' /* end of period */ AND
Date2>='2009-01-01' /* start of period */ AND ChildID=1
GROUP BY ChildID

#there we go!  days assigned are 355, plus days NOT assigned (1/16 to 1/24 inclusive = 9) totals 364.  Therefore we have a way to check for gaps in assignment by child.  We also add in the number of assignments which might be helpful, and the MIN/MAX start and end of the assignments vs. the range being evaluated :
SELECT 
ChildID,
DATEDIFF('2009-12-31', '2009-01-01')
	- /*minus*/
SUM(
DATEDIFF(
	IF(Date2 >'2009-12-31', '2009-12-31', Date2),
	IF(Date1 <'2009-01-01', '2009-01-01', Date1)
)) - COUNT(*)+1 AS DaysNotAssigned,
COUNT(*) AS TotalAssignments,
MIN(Date1) AS AssignmentsBegin,
MAX(Date2) AS AssignmentsEnd
FROM diff
WHERE 
Date1<='2009-12-31' /* end of period */ AND
Date2>='2009-01-01' /* start of period */
GROUP BY ChildID

Roger_Ramjet

You can do the first in 1 query. For this to work you will need an autoinc id column.

Get all the rows where there is no gap afterwards.

SELECT id FROM tbl AS tbl1, tbl AS tbl2
WHERE date_add(tbl1.released, interval 1 day) = tbl2.assigned

use those ids to get the ids where there is a gap after

SELECT * FROM tbl
WHERE id NOT IN (SELECT id FROM tbl AS tbl1, tbl AS tbl2
WHERE date_add(tbl1.released, interval 1 day) = tbl2.assigned)

// OR

SELECT * FROM tbl LEFT JOIN (SELECT id FROM tbl AS tbl1, tbl AS tbl2
WHERE date_add(tbl1.released, interval 1 day) = tbl2.assigned) AS t
ON tbl.id = t.id 
WHERE t.id IS NULL

Of course this will always return the last row as well, but you can eliminate that by using your 9999-99-99 date in the released column.

Shrike

Nice work Roger, I puzzled over this one for a while but came up with nothing so elegant 🙂

sfullman

I agree, I wound up using Roger's implementation vs. mine (though it can tell things like NUMBER of days lapsed, that wasn't important to me).

If #2 of my question can be done, that would be quite amazing, esp. is as elegant a method as Roger did on #1..

Roger_Ramjet

Well, it is possible and I will have a go at this on the weekend. Similar method, using self-joins and date functions, so have a try yourself in the meanwhile.

Roger_Ramjet

FYI for anyone else who finds this thread: I simplified the sql in my solutions for clarity, which sfullman understood. For those who don't, the full table design and query would be

table_id   child_id    assigned  released
1               1    2007-07-25    2008-01-13
...
...
17              1    2008-01-14    2008-01-29
...
...
120            1    2008-01-30    2009-01-15 *
...
...
352            1    2009-01-25    2009-10-01
...
...
1075          1    2009-10-02    9999-99-99

SELECT * FROM tbl
WHERE table_id NOT IN (SELECT table_id FROM tbl AS tbl1 INNER JOIN tbl AS tbl2 USING(child_id)
WHERE date_add(tbl1.released, interval 1 day) = tbl2.assigned)

That will get the gaps for all child ids, just add another parameter to the WHERE clause to get a specific child id.

Roger_Ramjet

Yes, question 2 is an interesting one.

The real problem is that we do not know the exact sequence of ids for each child so we can't easily calculate which ones have a gap of more than 1 day after them.

To give all rows a sequence within child id

SELECT t1.table_id, t1.child_id, t1.assigned, t1.released, COUNT(t1.table_id) AS seq, t1.table_id >= t2.table_id AS b
FROM table t1 INNER JOIN table t2 USING(child_id)
GROUP BY t1.table_id, t1.child_id, b
HAVING b = true

That should return all rows in the table with an added field 'seq' that contains the sequence number within child id for each row (and another added boolean field 'b' which will always be TRUE in out results). We can now use that sequence number to find the interval between entries and thus those with more than 1 day.

SELECT t1.table_id, t1.child_id, t1.released, t2.assigned, t1.seq, DATEDIFF(t2.assigned, t1.released) AS gap
FROM
(SELECT t1.table_id, t1.child_id, t1.assigned, t1.released, COUNT(t1.table_id) AS seq, t1.table_id >= t2.table_id AS b
FROM table t1 INNER JOIN table t2 USING(child_id)
GROUP BY t1.table_id, t1.child_id, b
HAVING b = true) AS t1
INNER JOIN
(SELECT t1.table_id, t1.child_id, t1.assigned, t1.released, COUNT(t1.table_id) AS seq, t1.table_id >= t2.table_id AS b
FROM table t1 INNER JOIN table t2 USING(child_id)
GROUP BY t1.table_id, t1.child_id, b
HAVING b = true) AS t2
ON t1.child_id = t2.child_id AND t1.seq = (t2.seq - 1)
WHERE gap > 1

Should be right - except for typos.

That really does it all in 1 for you - find the rows with a gap after them and find the gaps themselves.

What I have done here is to
a) add a sequence to the rows
b) joined each row to the one next in sequence
c) find how many days there are between a release date and the next assigned date

Of course, you will have to deal with rows that do not have any release date as they will break the datediff function.

Roger_Ramjet

Now if you are going to run a query like this often then personally I would add the sequence column to the table and calculate it when you insert each record. Make life a lot easier. Do it with Triggers so that you can recalculate it if rows are deleted or inserted into the middle of a child sequence.

If not the extra column, then save the subquery as a Stored View. Make it a lot easier to write queries and make execution more efficient as well.