September 28, 2013 at 6:34 am
Hi,
Please help me to find out conflicts between two records.
Scenario: as mentioned below i need to add flag conflict 1 or 0.
problem : find studentid's having course more than 1 on a single date and if StartTime of second rows comes between Starttime and Endtime of first row then update conflict flag as 1. I want to compare all rows and find out conflicts. Plase help me how to aschieve this?
StudentIDCourseStartDate StartTimeEndTime Conflict
1001 Chem11/4/2013 0:00 7:00 AM 6:00 PM 1
1001 Phy 11/4/2013 0:00 11:00 AM 2:00 PM 1
1001 Math 11/4/2013 0:00 8:00 PM 11:00 PM 0
1001 Bio 11/4/2013 0:00 4:00 AM 7:00 AM 1
Thanks,
September 28, 2013 at 4:40 pm
It is very helpful to include table definition, sample data in the form of "insert" statements and expected result. That way we don't have to guess column names, data types, etc. and we will be able to reproduce the underlying tables just with copy and paste.
Help us to be able to help you.
A simple way to test if to intervals overlap (inclusive) is using the expression:
(A.starttime <= B.endtime) and (A.endtime >= B.starttime)
SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @T TABLE (
StudentID int NOT NULL,
Course varchar(10) NOT NULL,
StartDate date,
StartTime time,
EndTime time,
Conflict smallint NOT NULL DEFAULT(0)
);
INSERT INTO @T (
StudentID,
Course,
StartDate,
StartTime,
EndTime
)
VALUES
(1001, 'Chem', '11/04/2013', '07:00 AM', '06:00 PM'),
(1001, 'Phy', '11/04/2013', '11:00 AM', '02:00 PM'),
(1001, 'Math', '11/04/2013', '08:00 PM', '11:00 PM'),
(1001, 'Bio', '11/04/2013', '04:00 AM', '07:00 AM');
UPDATE
A
SET
A.Conflict = 1
FROM
@T AS A
WHERE
EXISTS (
SELECT
*
FROM
@T AS B
WHERE
B.StudentID = A.StudentID
AND B.Course <> A.Course
AND B.StartDate = A.StartDate
AND B.StartTime <= A.EndTime
AND B.EndTime >= A.StartTime
);
SELECT
StudentID,
Course,
StartDate,
StartTime,
EndTime,
Conflict
FROM
@T;
GO
Here are two sets of reading in case you are interested in learning more about interval queries.
Efficient Interval Management in Microsoft SQL Server
Dejan Sarka blog series
http://blogs.solidq.com/dsarka/Post.aspx?ID=149&title=Interval Queries in SQL Server Part 1
http://blogs.solidq.com/dsarka/Post.aspx?ID=150&title=Interval Queries in SQL Server Part 2
http://blogs.solidq.com/dsarka/Post.aspx?ID=151&title=Interval Queries in SQL Server Part 3
http://www.goodreads.com/author_blog_posts/4571393-interval-queries-in-sql-server-part-4
http://www.goodreads.com/author_blog_posts/4577245-interval-queries-in-sql-server-part-5
Enjoy them!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply