user3806148 user3806148 - 10 days ago 6
MySQL Question

MySQL find date gaps in timeslices

I have a table VariableValues in which a value is stored together with a

startdate
,
enddate
and
TariffVariableName
.

Another table Variable also has
start
,
enddate
and
name


EndDates are exclusive: so
timeslice
for month
february 2016
would be
2016-02-01 - 2016-03-01


I want to perform two checks (I prefer two queries)

One: There may be no gaps within the VariableValues

Example:

VariableValue.Name = name1
startdate: 2016-02-01 - enddate: 2016-03-01
startdate: 2016-04-01 - enddate: 2016-12-01



==> Should give an error as month April is not covered.


Two: The full period of the Variable should be covered by VariableValues

Variable.startdate: 2016-01-01 - enddate 2017-01-01
VariableValue.startdate 2016-01-01 - enddate 2016-06-01
VariableValue.startdate 2016-06-01 - enddate 2016-11-01



==> Should give an error as the month November and December is not covered by Values


Any help?

Answer

Gaps detector:

CREATE TABLE VariableValues (TariffVariableName CHAR(5), StartDate DATE, EndDate DATE);
INSERT INTO VariableValues (TariffVariableName, StartDate, EndDate)
VALUES ('name1', STR_TO_DATE('20160201', '%Y%m%d'), STR_TO_DATE('20160301', '%Y%m%d'));
INSERT INTO VariableValues (TariffVariableName, StartDate, EndDate)
VALUES ('name1', STR_TO_DATE('20160401', '%Y%m%d'), STR_TO_DATE('20161201', '%Y%m%d'));

SELECT *
FROM (
    SELECT
        next.TariffVariableName,
        (
            SELECT MAX(prev.EndDate)
            FROM VariableValues prev
            WHERE prev.TariffVariableName = next.TariffVariableName
              AND prev.EndDate < next.StartDate
        ) AS StartDate,
        next.StartDate As EndDate
    FROM VariableValues next
    WHERE NOT EXISTS (
        SELECT '*'
        FROM VariableValues overlap
        WHERE overlap.TariffVariableName = next.TariffVariableName
          AND overlap.EndDate >= next.StartDate
          AND overlap.StartDate < next.StartDate
    )
) gap
WHERE gap.StartDate IS NOT NULL
ORDER BY gap.TariffVariableName, gap.StartDate;

The same approach can be used to check interval Cover: for each interval from Variable take all overlapping intervals from VariableValues, add two artificial intervals: Variable.StartDate - Variable.StartDate and Variable.EndDate - Variable.EndDate, and run the above Gaps detector.