April 1, 2005 at 7:13 am
Hi I have a question on finding mutually exclusive events. Which is an offspring of this discussion
Valadan’s suggestion of using:
DELETE s
FROM sickness s
LEFT JOIN
(select staffid, start_date, end_date, min(sickid) as minsickid
from sickness
group by staffid, start_date, end_date) AS ok ON ok.minsickid = s.sickid
WHERE ok.minsickid IS NULL
Will delete all records that are duplicates. But does not address the issue of an employee having claimed a sickness that is not mutually exclusive of all other sickness. How can this statement be modified to also delete records where the start and end dates of a sickness are contained within the start and end dates of another claimed sickness by the same employee.
Thanks in advance
April 1, 2005 at 11:33 am
For Dates Within, try:
DELETE FROM sickness
FROM sickness
INNER JOIN(SELECT todel.sickid, MAX(dupe.maxsickid) AS maxsickid
FROM sickness AS todel
INNER JOIN (SELECT COUNT(1), payroll, staff_id, start_date
, end_date, MAX(sickid) AS maxsickid
FROM sickness
GROUP BY payroll, staff_id, start_date, end_date
HAVING count(1)>1) AS dupe
ON todel.payroll = dupe.payroll
AND todel.staff_id = dupe.staff_id
AND (todel.start_date BETWEEN dupe.start_date AND dupe.end_date
OR todel.end_date BETWEEN dupe.start_date AND dupe.end_date
OR dupe.start_date BETWEEN todel.start_date AND todel.end_date
OR dupe.end_date BETWEEN todel.start_date AND todel.end_date
GROUP BY todel.sickid) AS delete
ON sickness.sickid = delete.sickid AND sickness.sickid < delete.maxsickid
Andy
April 1, 2005 at 1:08 pm
Warning, I'm not the best at remembering syntax, but this should get the point across.
Declare a counter variable and initialize it to one.
begin loop (while counter variable > zero)
set counter variable to zero
Run your delete routine
if you find a duplicate, seet counter variable to one
end loop
This should run the loop and delete one duplicate record from each set until it no longer finds any duplicates and then it will end. It's not pretty, but it should get the job done as a scheduled task.
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply