Comparing Records by Dates that are Stored in a Single Table

[suttercain]

Hi everyone,

 

Artacus was nice enough to help me with an issue last week when comparing data from two separate tables using a union. See: Original Post

 

Now I want to do the same thing, kind of, using a single table in lieu of two tables. I need to compare id's based on the date the id was added or removed.

 

Example of Table

 

------------------

| ID | Date          |

| 1  | 09-01-2009 |

| 2  | 09-01-2009 |

| 3  | 09-01-2009 |

| 1  | 09-02-2009 |

| 3  | 09-02-2009 |

| 4  | 09-02-2009 |

| 5  | 09-02-2009 |

-------------------

 

So the MySql result would need to state: Id 2 was removed on 09-02-2009. Id 4 and 5 were added on 09-02-2009.

 

Any help or suggestions would be greatly appreciated. Thanks in advance.

[artacus]

I'm not sure what you mean by added and removed. I'm guessing what you really need are min and max dates for an id.

 

SELECT id, MIN(date) AS added_on, 
  -- Not sure if you need the last day it WAS in your result
  -- or the first day it WAS NOT
  MAX(date) + 1 AS removed_on 
FROM example
GROUP BY id

[suttercain]

Hi Artacus,

 

Basically need the same 'comparison' like I did before when it was between two tables, but this time using just one table. The output result of the table I posted above should state something similar to this:

 

2 was removed on 09-02-2009.

4 was added on 09-02-2009.

5 was added on 09-02-2009.

 

So ya, taking the most recent date, and comparing the id's to the previous date. Comparing any changes made between the two dates and listing them similar to that of the list above.

 

Thanks again for your time!

 

-SC

 

[artacus]

This isn't the greatest design. Always try to keep the start and end times for an event in a single row.

This would be ideal for a full outer join but I'm pretty sure MySQL still doesn't support them.

 

SELECT CASE WHEN yesterday.id IS NULL AND tomorrow.id IS NULL THEN 
  concat(today.id, ' was added ', today.date, ' and removed ', tomorrow.date)
  WHEN yesterday.id IS NULL THEN concat(today.id, ' was added ', today.date)
  WHEN tomorrow.id IS NULL THEN concat(today.id, ' was removed ', tomorrow.date)
  ELSE 'Artacus freaking rocks'
END AS status_update
FROM example today 
LEFT JOIN example yesterday ON today.id = yesterday.id
  AND today.date = yesterday.date + INTERVAL 1 DAY
LEFT JOIN example tomorrow ON today.id = tomorrow.id
  AND today.date = tomorrow.date - INTERVAL 1 DAY
WHERE yesterday.id IS NULL OR tomorrow.id IS NULL

[suttercain]

This isn't the greatest design. Always try to keep the start and end times for an event in a single row.

 

Thanks! Ya, I agree sadly there is no "end time" per say because if an id isn't added or removed from a specific date to the next, it's a continuation or a "no change."

 

I ran the code and I got the following out put when echoing 'status_update'

 

3 was added 2009-09-01 -

1 was added 2009-09-01 -

[artacus]

Yeah, well 1 and 3 weren't in the table on 8/31/2009. I assumed you would have a continuum here in your data. If you've got a fencepost then you'll have to add some logic to handle that.