SQL Query - Ensure data in a table covers a year, with no date overlaps

I have a table called tblRentalRates with the following columns:

rate_id (int)    
rate_startdate (datetime) 
rate_enddate (datetime) 
weekday_rate (money) 
weekend_rate (money)

I want to accomplish the following goal:

• Write a query that will check the contents of the table and ensure that for the current year, that the data covers the entire year, without overlap. It could be ONE row, with a start and end date covering, or it could be 366 rows (one for each day of a leap year) or any combination in between.

I have an admin tool that allows the site administrator to control pricing, but I'd like to show some sort of error message at the top, i开发者_JS百科f they are missing a day from the year, or are overlapping dates, etc.

Any one have any ideas?

---

Overlaps are relatively easy (but you might have to worry about '<=' vs '<' and '>=' vs '>' for the times):

SELECT x1.*, x2.*, "Overlap"
  FROM tblRentalRates AS x1, tblRentalRates AS x2
 WHERE x1.rate_startdate < x2.rate_enddate
   AND x1.rate_enddate   > x2.rate_startdate
   AND x1.rate_id < x2.date_id   -- Avoid repeats with x1, x2 interchanged

Determining complete coverage is considerably harder. It is not helped by the quirkiness of each DBMS having their own (incompatible) system of date handling functions or date representations.

I'm going to start off using the notation for the DBMS that I'm most familiar with (IBM Informix Dynamic Server) - and will then attempt to translate that to MS SQL Server later.

We need to establish the set of rows which overlap the target year (rows which end on or after the 1st of January, and begin on or before the 31st of December):

SELECT * FROM tblRentalRates AS rr
 WHERE rr.rate_enddate   >= MDY( 1, 1,YEAR(TODAY))
   AND rr.rate_startdate <= MDY(12,31,YEAR(TODAY))

Next, we need to determine the granularity of the date values. The name DATETIME suggests that the values can store a time component as well as a date component, but it would be a lot easier if we could assume that the granularity is '1 day'. Alternatively, we need to know whether the end date is included in the range or not - is the range open-closed or open-open (or closed-open, or closed-closed).

Now we need to look for pairs of entries in the list above for which the end date of the earlier is more than one day before the start date of the later and for which there is no row in between - those constitute gaps in the coverage. I'm going to assume an open-open representation with granularity of 1 day (so a row with start '2009-06-01' and end '2009-06-01' is a single day's worth of information, and a row with start/end of '2009-06-02'/'2009-06-03' contains two day's worth of information, etc., and no date can appear between these two rows without giving us an overlap).

SELECT r1.*, r2.*, "Gap"
  FROM (SELECT * FROM tblRentalRates AS rr
         WHERE rr.rate_enddate   >= MDY( 1, 1,YEAR(TODAY)) 
           AND rr.rate_startdate <= MDY(12,31,YEAR(TODAY))
       ) AS r1
       CROSS JOIN
       (SELECT * FROM tblRentalRates AS rr
         WHERE rr.rate_enddate   >= MDY( 1, 1,YEAR(TODAY)) 
           AND rr.rate_startdate <= MDY(12,31,YEAR(TODAY))
       ) AS r2
 WHERE r1.rate_enddate < r2.rate_startdate - INTERVAL(1) DAY TO DAY
   AND NOT EXISTS
          (SELECT * FROM tblRentalRates AS r3
            WHERE r3.rate_enddate   > r1.rate_enddate) 
              AND r3.rate_startdate < r2.rate_startdate)
          )

Given this sample data:

INSERT INTO tblRentalRates VALUES(1, '2008-12-19', '2009-01-03', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(2, '2009-01-09', '2009-01-13', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(3, '2009-02-19', '2009-02-23', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(4, '2009-02-24', '2009-02-28', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(5, '2009-03-01', '2009-03-23', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(6, '2009-03-29', '2009-11-03', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(7, '2009-11-29', '2009-12-13', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(8, '2009-12-15', '2009-12-28', 1.23, 2.23);
INSERT INTO tblRentalRates VALUES(9, '2009-12-29', '2010-01-03', 1.23, 2.23);

The query yields:

1  2008-12-19  2009-01-03  1.23  2.23  2  2009-01-09  2009-01-13  1.23  2.23  Gap
2  2009-01-09  2009-01-13  1.23  2.23  3  2009-02-19  2009-02-23  1.23  2.23  Gap
5  2009-03-01  2009-03-23  1.23  2.23  6  2009-03-29  2009-11-03  1.23  2.23  Gap
6  2009-03-29  2009-11-03  1.23  2.23  7  2009-11-29  2009-12-13  1.23  2.23  Gap
7  2009-11-29  2009-12-13  1.23  2.23  8  2009-12-15  2009-12-28  1.23  2.23  Gap

Clearly, it still isn't MS SQL Server notation. However, I'm about to postulate that those experienced in the notations used by MS SQL Server can adapt the material above so that it does work - aka exercise for the reader.