CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Billa
Billa

Reputation: 5266

Ignore records with date range

Trying to build the recurring event system based on Display next event date and the SQL Fiddle

It works well. I tried integrating the leaves plan with the repeating events in the calander to filter it. Mean I need to ignore the repeat events if they fall under the planned leave date.

Here is my leave detail schema

CREATE TABLE dbo.Doctor
(
  DoctorID TINYINT IDENTITY(1, 1) NOT NULL,
  DoctorName VARCHAR(10) NOT NULL,
  CONSTRAINT PK_Doctor_DoctorID PRIMARY KEY (DoctorID)
)


INSERT Doctor(DoctorName) VALUES ('Dr John')

CREATE TABLE dbo.Leaves
(       LeaveID INT IDENTITY(1, 1) NOT NULL,
        DoctorID TINYINT NOT NULL,
        LeaveStartDateTime DATETIME2 NOT NULL,
        LeaveEndDateTime DATETIME2 NULL,

    CONSTRAINT PK_Leaves_LeaveID PRIMARY KEY (LeaveID),
    CONSTRAINT FK_Leaves_DoctorID FOREIGN KEY (DoctorID) REFERENCES dbo.Doctor (DoctorID)

);

Leave Data

  1. From Date '20140115 00:00:00' to '20140116 11:59:59' ->15t & 16th (2 days)

  2. From Date '20140120 00:00:00' to '20140125 11:59:59' -> 20th-25th (6 days)

I need to remove rows which falls between dates mentioned above

The Query

WITH RepeatingEvents AS
(   SELECT  d.DoctorName,
            l.LeaveStartDateTime,
            l.LeaveEndDateTime,
            e.Name,
            re.StartDateTime,
            re.EndDateTime,
            re.TimesToRepeat,
            RepeatEventDate = CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME),
            RepeatNumber = ROW_NUMBER() OVER(PARTITION BY re.RepeatEventID ORDER BY c.Datekey)
    FROM    dbo.Event e
            INNER JOIN dbo.RepeatEvent re
                ON e.EventID = re.EventID
            INNER JOIN dbo.RepeatType rt
                ON rt.RepeatTypeID = re.RepeatTypeID
            INNER JOIN dbo.Calendar c
                ON c.DateKey >= re.StartDate
            INNER JOIN dbo.RepeatDayOfWeek rdw
                ON rdw.RepeatEventID = re.RepeatEventID
                AND rdw.DayNumberOfWeek = c.DayNumberOfWeek
            INNER JOIN dbo.DoctorXEvent de ON e.EventID = de.EventID
            INNER JOIN dbo.Doctor d ON d.DoctorID = de.DoctorID
            LEFT JOIN dbo.Leaves l ON l.DoctorID = d.DoctorID 
    WHERE   rt.Name = 'Weekly'
)
SELECT  DoctorName, LeaveStartDateTime, LeaveEndDateTime,Name as EventName, StartDateTime, RepeatEventDate, RepeatNumber
FROM    RepeatingEvents
WHERE   (TimesToRepeat IS NULL OR RepeatNumber <= TimesToRepeat)
AND     (EndDateTime IS NULL OR RepeatEventDate <= EndDateTime)
AND     (RepeatEventDate NOT BETWEEN LeaveStartDateTime AND LeaveEndDateTime)

SQL FIDDLE DEMO

My query not filtering those records and also gives duplicate entries because of LEFT JOIN.

Upvotes: 2

Views: 321

Answers (1)

Daniel B
Daniel B

Reputation: 797

The left join to Leaves does not work since the dates are not involved in the join. Replace the left join with a new predicate to remove unwanted rows:

AND NOT EXISTS 
    (SELECT 1 FROM Leaves l WHERE l.doctorId = d.doctorId 
    AND CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME)  
    BETWEEN l.LeaveStartDateTime AND l.LeaveEndDateTime)

This way the unwanted rows, and the duplicates, are removed.

/Daniel

Upvotes: 1

Related Questions