Current position:  Home > Default > Need to find rows with overlapping dates

Need to find rows with overlapping dates

Time:November 30
Advertisement
I have a table that has FirstName, LastName, EnrollmentFrom and EnrollmentTo
I need to find all names where they have overlapping dates.  For example this is invalid:
Name       EnrollmentFrom     EnrollmentTo
John Doe   1/1/2014              1/1/2015
John Doe   2/5/2014              2/5/2014
This would be valid:
John Doe   1/1/2014              1/1/2015
John Doe   1/2/2015              1/1/2016
This is just an example as the dates could be anything in one of the other enrollments.
Any help is greatly appreciated.
Advertisement
Here is what I have based on the last post but it also returns ones that are valid:
SELECT ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
FROM PAPPortal..FeedFileEnrollmentValues ffev
OUTER APPLY (select top 1 b.* from PAPPortal..FeedFileEnrollmentValues b where ffev.PatientID = b.PatientID and b.EnrollmentFrom < ffev.EnrollmentTo and b.EnrollmentTo > ffev.EnrollmentFrom and ffev.PatientID <> b.PatientID) overlap
WHERE ffev.EnrollmentTo IS NOT NULL
GROUP BY ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
ORDER BY ffev.PatientLastName, ffev.PatientFirstName, ffev.EnrollmentFrom, ffev.EnrollmentTo
Name              EnrollmentFrom         EnrollmentTo
John Doe            2013-02-08            2014-02-07
John Doe            2014-08-15            2015-08-15
hi
it seems you have given (= and <>) which is always return false(no records)
 where ffev.PatientID = b.PatientID and b.EnrollmentFrom < ffev.EnrollmentTo and b.EnrollmentTo > ffev.EnrollmentFrom and
       ffev.PatientID <> b.PatientID
Note : i have given
where a.ID=b.ID and b.DateFrom<a.DateTo and b.DateTo>a.DateFrom and
a.rno<>b.rno
If you don't have any unique column to differentiate you  can create rno with CTE and use like below
DECLARE @Data AS TABLE
-- rno int identity(1,1),
ID int,
DateFrom datetime,
DateTo datetime
INSERT INTO @Data VALUES (1, '1/1/2014', '1/1/2015')
INSERT INTO @Data VALUES (1, '2/5/2014', '2/5/2014')
INSERT INTO @Data VALUES (2, '1/1/2014', '12/31/2014')
INSERT INTO @Data VALUES (2, '1/2/2015', '12/31/2016')
;WITH CTE1 as
SELECT ROW_NUMBER() over(PARTITION by ID order by DateFrom,DateTo ) rno,* FROM @Data
SELECT a.*, overlap.rno,overlap.DateFrom,overlap.DateTo
FROM CTE1 a
OUTER APPLY (select top 1 b.* from CTE1 b where a.ID=b.ID
and b.DateFrom<a.DateTo
and b.DateTo>a.DateFrom and a.rno<>b.rno) overlapwhere overlap.ID is not null
Thanks
Saravana Kumar C

Looking for a query to find first/last dates in overlapping dates...

2015-10-11

Hi, I'm looking for a query to find the first dates and last dates in a table conaining overlapping dates. I have a subscription table which has for each Customer start and end date for different subscriptions. I want to know the different ranges of

Solution for Overlapping dates

-0001-11-30

Hi I am looking for an easy solution the problem described below I have 4 records under party key and PartyKey account id taxtype date start date end 123 145 100 01/01/2001 12/31/2002 123 145 100 05/01/2001 05/31/2001 123 145 100 01/01/2002 11/30/200

Overlapping Dates , Denormaization of the tables

-0001-11-30

Hi Guys, I am in a situation that we are denormalising few tables for better performance and reduce the joins. For which I am joining the tables using the logic to check for the dates and add the fields corresponding to those dates to represent the j

Sqlldr - can you skip last row from a data file

2015-10-11

Hi I need to skip last line from the data file when I load the data into the tables. Is that possible to do using sqlldr, if yes How? Also, the first row in the data file, which has a single column needs to be loaded into all the rows of the table. H

Inserting rows into a data block

2015-10-11

Hi, how do i insert 'n' rows into a data block and after that commit the changes into the data base. Thanks for any help.You can use something like this: Go_Block('my_block'); first_record; LOOP .... insert values to the items next_record; END LOOP;

Overlap date Issue 0hrposition_attr

2015-10-11

Hi I'm facing one records overlap date Issue with 0hrposition_attr.Out of 4  records one records i'm getting as start date as future date and end date as past date.I checked in the source i did not see such records.Due to this issue the records are n

Not Getting Rows With No Date Match

2015-10-11

I have SQL 2012 and am trying to use a CTE to get patient meal calendar rows even if the row does not match a given date range. We use a Calendar table that has 1 row for each date in the year so I wanted it to drive the results so that when I ask fo

How to know whether a row is upto date in two site ?

-0001-11-30

Hi all, My application need to check whether a record is same in two sites. I need to do this using a SQL query or a stored procedure. I saw the DBMS_RECTIFIER_DIFF package but it seems that it won't tell that whether that row is upto date. Any help

How to set different colors for even odd rows in a data table

-0001-11-30

hi, In my project i hav a data table. i am using jsf. i want the rows of the data table different colors for even, odd rows. can i do it just setting the row class property ? or there should be there some other way? please help. sailajoyHope this hel

Delete a row from a data object using an AQ-driven EMS

-0001-11-30

Hello, technetwork. I need to show the contents of a table that changes frequently (inserts, updates and deletes) at ORDBMS 10g in my BAM dashboard. What i have done is: - Create two different queue tables, queues. - Create two triggers: one for "AFT

Can anyone explain the split and overlapping date ranges in PHOTO moments.  I would have expected each date to have its own slot.  What logic is used for deciding which photos to include and where to split them?

-0001-11-30

iPad Air iOS 8.1 Stock Photo App.  14000 Photos. Overlapping date ranges in Moments.  What is the criteria used for deciding after import where photos are included in Moments.  I was expecting each day to have its own exclusive slot for example, not

Overlapping dates not found

-0001-11-30

Hi Friends, I had created a project, with two wbs elements at level 1 and 2. Assigned a network and an activity to it. Maintained all the parameters required. The project is saved. Later if i want to make any changes to the project and try to save, i

Creating rows Using BC4J Data Tags

-0001-11-30

Hi! I want to create rows using BC4J data tags. I have 2 jsp pages: one containig forms and one to insert data. Is it possible to insert new rows into 2 or more tables? How can I differ the attributes to one table and to another? How can I do this? T

Getting correct row based on date

-0001-11-30

Hi, if i have the following records for an account: now i have a date of 4-1-2013 and I want the row where either 4-1-2013 falls withing the BeginDt and EndDt OR if it does not fall within the BeginDt and EndDt, i wann the EndDt = null row.  So i sho

Abap logic not fetching multiple rows from master data table

-0001-11-30

Hi I just noticed that my logic is fetching only 1 row from master data table. ProdHier table PRODHIERACHY            Level 1000                                  1 1000011000                      2 10000110003333              3 10000110004444        

ROW-00014: Cannot update row as the data in the database has changed

-0001-11-30

We're having the problem below. We are trying to upgrade a 10g Oracle database via a linked server in SQL Server 2008. OLE DB provider "OraOLEDB.Oracle" for linked server "abc" returned message "ROW-00014: Cannot update row as the

Overlapping date problem, I'm a bit stumped...

-0001-11-30

Hi, My first post here, woohooo!! I'm trying to write some SQL for overlapping dates, hoping someone could point me in the write direction because I'm a bit stuck :( Heres the scenario.... Tables are simillar to this (although I've changed it for the

Supress Missing Blocks : Suppressing rows that contains data

-0001-11-30

Hi, In Hyperion Planning 11.1.2.2, on a webform if I do supress missing blocks, it suppresses rows that contains data also (for 3 particular members of the dimension selected in the row). This webform is otherwise a huge report & if I do not check on

Overlapping dates (rowid illiminates part of the result, pls help)

-0001-11-30

Hi, this has probably been asked before but I could not find the solution I'm looking for. I have the following table emp_id start_date end_date dept_no 1 20-jan-06 jan-31-06 1 1 25-feb-06 15-mar-06 2 1 01-feb-06 null 3 Here is the same employee who