Find existing data within a date range change

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find existing data within a date range change

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-07-30 : 12:00:58

Sorry for the terrible title (and wall of beginning text) but here's what I'm looking for: We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:

DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8))
INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99')

DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)

DECLARE @NEWDATES TABLE(StartDate date,EndDate date,ID varchar(8))
INSERT INTO @NEWDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)

SELECT * FROM @RECORDS R INNER JOIN @ORIGINALDATES O ON R.ID = O.ID INNER JOIN @NEWDATES N ON O.ID = R.ID
WHERE
(R.RecordDate > N.StartDate AND R.RecordDate <= O.StartDate
OR
R.RecordDate >= O.StartDate AND R.RecordDate < N.StartDate
OR
R.RecordDate > ISNULL(N.EndDate,'30000101') AND R.RecordDate <= ISNULL(O.EndDate,'30000101')
OR
R.RecordDate >= ISNULL(O.EndDate,'30000101') AND R.RecordDate < ISNULL(N.EndDate,'30000101'))

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2014-07-31 : 13:51:33

The reason my query was running terribly was my order of table selections (the actual query has a bunch of other tables in it). Now that I've re-orderd and joined on some fields that are indexed, it's working much faster. I'm still curious if there's a better way to make sure any records that would be effected are selected, but at least the correct data is being returned quickly now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources