Solved

How Can I find and fix/update overlapping dates in SQL Server 2005

Posted on 2008-10-01
15
425 Views
Last Modified: 2012-05-05
Hi all,
I have an issue I have been scratching my head over for a few days now and I am wondering if any of you good people would be able to help me?

*DATES ARE UK FORMAT

I have to be able to keep a list of "To" & "From Dates" in chronological order in a database.
So for example I have list of dates in a table with several columns but 2 of those columns are "toDate" & "FromDate"

The user will in put for example
01/10/2008 - 10/10/2008
11/10/2008 - 15/10/2008
16/10/2008 - 20/10/2008

The user is responsible for making sure the new date starts 1 day after the last, when they are just putting in a new date at the end of the most recent date. (I know this might not make sense)
What this is is prices for packaging and the dates change often so they need to be able to just input a new date and the system accomodates it. If its just a new date range it just goes in but if the the new range is already covered by an existing date then it needs to split
However they could for example come back and input
05/10/2008 - 12/10/2008

and there is my problem, I have to work out how to split the already existing dates to accommodate the new input. So this means that the
above dates would have to become:

01/10/2008 - 10/04/2008
05/10/2008 - 12/10/2008 - - ->Newly input-ed Date.
13/10/2008 - 15/10/2008
16/10/2008 - 20/10/2008

I have tried so many ways but I just can not get it to work it seems so arbitrary that almost any possible date could occur and tracking the changes is to me impossible or is it?
I guess I am not approaching this the right way.

I am thinking that there must be some way to split the dates up but its prooving very hard for me. Any help would be great.

I attach some T-SQL code I am trying it works 75% of the time but not always.

Thanks to anybody who can help me with this

Jason

DECLARE @fDate AS DATETIME

DECLARE @TDate AS DATETIME

DECLARE @ID AS INT

DECLARE @DOWORK AS INT

SET @fDate = '23-NOV-2008'

SET @TDate = '30-NOV-2008'

SET @DOWORK = 1
 

--SELECT * FROM PackageRulesDateBreak ORDER BY daterulefrom ASC
 

IF NOT EXISTS (SELECT MAX(pkdaterulekey) FROM PackageRulesDateBreak

                WHERE daterulefrom < @fDate OR dateruleto < @tDate)

    BEGIN

                SET @DOWORK = 0

    END
 

IF EXISTS (SELECT pkdaterulekey FROM PackageRulesDateBreak

                WHERE daterulefrom = @fDate AND dateruleto = @tDate)

    BEGIN

        SET @DOWORK = 0

    END
 

IF NOT @DOWORK = 0

BEGIN
 

IF NOT EXISTS (SELECT pkdaterulekey FROM PackageRulesDateBreak

                WHERE daterulefrom = @fDate OR dateruleto = @tDate)

    BEGIN

        ----UPDATE THE FROM DATE

        SET @ID = (

        SELECT MAX(pkdaterulekey) FROM dbo.PackageRulesDateBreak

        WHERE daterulefrom < @fDate

        )

        UPDATE PackageRulesDateBreak

        SET dateruleto = DATEADD(DAY,-1,@fDate)

        WHERE pkdaterulekey = @ID
 
 

        SET @ID = (

        SELECT MIN(pkdaterulekey) FROM dbo.PackageRulesDateBreak

        WHERE dateruleto > @tDate

        ----UPDATE THE TO DATE

        )

        UPDATE PackageRulesDateBreak

        SET daterulefrom = DATEADD(DAY,1,@TDate)

        WHERE pkdaterulekey = @ID
 

        DELETE FROM PackageRulesDateBreak

        WHERE pkdaterulekey IN (

        SELECT pkdaterulekey FROM dbo.PackageRulesDateBreak

        WHERE daterulefrom > @fDate

        AND dateruleto < @TDate

        )
 

        INSERT INTO PackageRulesDateBreak(daterulefrom, dateruleto, pkdaterulehotelid, pkNights)

        VALUES (@fDate, @TDate, 63, 7)
 

--DELETE FROM PackageRulesDateBreak

--WHERE daterulefrom > dateruleto

END
 

----START DATE EXISTS

IF EXISTS (SELECT pkdaterulekey FROM PackageRulesDateBreak

                WHERE daterulefrom = @fDate AND NOT dateruleto = @tDate)

    BEGIN

        ----UPDATE TODATE

        SET @ID = (

        SELECT MIN(pkdaterulekey) FROM PackageRulesDateBreak

        WHERE dateruleto >= @tDate

        )

        UPDATE PackageRulesDateBreak

        SET daterulefrom = DATEADD(DAY,1,@TDate)

        WHERE pkdaterulekey = @ID
 

        DELETE FROM PackageRulesDateBreak

        WHERE pkdaterulekey IN (

        SELECT pkdaterulekey FROM dbo.PackageRulesDateBreak

        WHERE dateruleto < @TDate AND dateruleto > @fDate

        )
 

        INSERT INTO PackageRulesDateBreak(daterulefrom, dateruleto, pkdaterulehotelid, pkNights)

        VALUES (@fDate, @TDate, 63, 7)

    END
 

----END DATE EXISTS

IF EXISTS (SELECT pkdaterulekey FROM PackageRulesDateBreak

                WHERE NOT daterulefrom = @fDate AND dateruleto = @tDate)

    BEGIN

        ----UPDATE The to Date

        SET @ID = (

        SELECT MAX(pkdaterulekey) FROM PackageRulesDateBreak

        WHERE daterulefrom < @fDate

        )

        UPDATE PackageRulesDateBreak

        SET dateruleto = DATEADD(DAY,-1,@fdate)

        WHERE pkdaterulekey = @ID
 

        ----DELETE THE BETWEEN DATES

        DELETE FROM PackageRulesDateBreak

        WHERE pkdaterulekey IN(

        SELECT pkdaterulekey FROM PackageRulesDateBreak

        WHERE dateruleto <= @TDate AND dateruleto > @fDate)

--

        INSERT INTO PackageRulesDateBreak(daterulefrom, dateruleto, pkdaterulehotelid, pkNights)

        VALUES (@fDate, @TDate, 63, 7)

    END

END

ELSE
 

        INSERT INTO PackageRulesDateBreak(daterulefrom, dateruleto, pkdaterulehotelid, pkNights)

        VALUES (@fDate, @TDate, 63, 7)

PRINT 'NO WORK TO DO'

Open in new window

0
Comment
Question by:JasonChandler
  • 9
  • 5
15 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
I want to ensure I understand the problem.

So is that if the user inputs:
01/10/2008 - 10/10/2008
11/10/2008 - 15/10/2008
16/10/2008 - 20/10/2008

and this exists:
05/10/2008 - 12/10/2008

That it should split the around the existing data?

Or will this exist:
01/10/2008 - 10/10/2008
11/10/2008 - 15/10/2008
16/10/2008 - 20/10/2008

Then they input this
05/10/2008 - 12/10/2008

and the previous data needs to be split?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Please check if this does (basically) what you need:


SET DATEFORMAT DMY

set nocount on

if object_id('tempdb.dbo.#PackageRulesDateBreak') is not null
    drop table #PackageRulesDateBreak
create table #PackageRulesDateBreak
(
    pkdaterulekey int,
    pkdaterulehotelid int,
    pknights int,
    daterulefrom datetime,
    dateruleto datetime
)
--reestablish base data in table
truncate table #PackageRulesDateBreak
insert into #PackageRulesDateBreak values(1, 63, 7, '01/10/2008' , '10/10/2008')
insert into #PackageRulesDateBreak values(1, 63, 7, '11/10/2008' , '15/10/2008')
insert into #PackageRulesDateBreak values(1, 63, 7, '16/10/2008' , '20/10/2008')
set nocount off


-- MAIN CODE

DECLARE @fDate AS DATETIME
DECLARE @TDate AS DATETIME
DECLARE @ID AS INT

SET @fDate = '05/10/2008'
SET @TDate = '12/10/2008'

SELECT *
FROM #PackageRulesDateBreak

--If an entire existing date range(s) is fully contained within the new date range,
--  for example, new range is 01/01/yyyy to 01/03/yyyy and
--  existing range is 01/02/yyyy to 01/03/yyyy,
--then delete the existing range(s) (the new range will completely replace the old one).
DELETE FROM #PackageRulesDateBreak
WHERE daterulefrom BETWEEN @fDate AND @tDate
AND dateruleto BETWEEN @fDate AND @tDate

UPDATE #PackageRulesDateBreak
SET daterulefrom = CASE WHEN (daterulefrom >= @fDate AND daterulefrom <= @tDate)
                      THEN DATEADD(DAY, +1, @tDate)
                      ELSE daterulefrom END,
    dateruleto = CASE WHEN (dateruleto >= @fDate AND dateruleto <= @tDate)
                      THEN DATEADD(DAY, -1, @fDate)
                      ELSE dateruleto END
WHERE (daterulefrom >= @fDate AND daterulefrom <= @tDate)
OR (dateruleto >= @fDate AND dateruleto <= @tDate)

INSERT INTO #PackageRulesDateBreak (daterulefrom, dateruleto, pkdaterulehotelid, pkNights)
        VALUES (@fDate, @TDate, 63, 7)

SELECT *
FROM #PackageRulesDateBreak
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@ScottPletcher
Yes this is working but The problem is in the deletion. Let me explain
Lets say a date range Contains FROM: 01/10/1008 TO: 10/10/2008
and this range gets split say: 05/10/2008 to 08/10/2008
The orignal range  01/10/1008 TO: 10/10/2008 is linked to another table which holds prices so that means that when this range (01/10/1008 TO: 10/10/2008) is split:
01/10/2008 - 04/10/2008 and 09/10/2008 - 10/10/2008 must retain the orignal 01/10/1008 TO: 10/10/2008 ranges prices, but the new range 05/10/2008 to 08/10/2008 will have a new price set when it is inserted. I hope this makes sense.

Jason
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@ScottPletcher
I forgot to say Hi, and thanks so much so far.. Sorry its just this issue is really getting to me, ive lost my manners.
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@BrandonGalderisi:
Hi there thanks, If you look at the code ScottPletcher has provided you will get the idea of what I am trying to acheive and read my post under his 1st reply.
Thanks for your comment and help & consideration.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> Lets say a date range Contains FROM: 01/10/[2]008 TO: 10/10/2008
and this range gets split say: 05/10/2008 to 08/10/2008 <<

If my code deleted that row, then I've got a bug in it.

It should only delete an existing row if the dates fall COMPLETELY within the new range. [I attempted to explain that with the comments, but clearly not well enough :-) .]

For review, here is the actual DELETE code:

DELETE FROM #PackageRulesDateBreak
WHERE daterulefrom BETWEEN @fDate AND @tDate
AND dateruleto BETWEEN @fDate AND @tDate

Note that the from and to dates must *both* be within the range of the new dates.

In your example:
daterulefrom (the existing from date) should be 01/10/2008

I don't think that date, 01/10/2008, will meet the first WHERE check:
WHERE '01/10/2008' [daterulefrom] BETWEEN '05/10/2008' [@fDate] AND '08/10/2008' [@tDate]

and so that row would not be deleted ... or that was my intent.

If the new range is 05/10/2008 to 08/10/2008, then, for example, an existing range of:
06/10/2008 to 07/10/2008
would get deleted ... which I thought was OK because that *entire* range is being assigned a new price anyway :-) .

The logic is much easier if you get rid of the completely "swallowed" dates before adjusting the rest (there's no point in UPDATEing a row you need to DELETE anyway).
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@ScottPletcher:
Hi there Im so sorry I have miss interpreted your comments. Yes I get what you are saying. I am leaving work now its 10:00pm here in the uk I have been here for 2 nights stright working on this system. I am a .net programmer at heart, our DBA has walked from the company and so left me SOL. I cant seem to get SQL very well so I may very well of mis-understood your code. I will implement and test this code in the morning in the live (staging) system and report back to you. Thanks so much for looking into this, you are the man.

Jason
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@ScottPletcher
Ok Could not let it go, and tested it on the system so still at work!
But....... It Worked. you were 100% Correct, this works perfect, you are Genius! thanks very much
you have saved me a lot of time and educated me at the same time.

Thanks Scott.
0
 
LVL 2

Author Closing Comment

by:JasonChandler
Comment Utility
Thanks so much, please see my last comment on the post!
Thank you, thank you
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Np, thanks for the kind words, very glad it helped you out.  You provided a fun and interesting challenge, and that's why I come here.
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
@ScottPletcher:
I have a little problem scott. Its all working except in this example
Date 01/10/2008 - 10/10/2008
then the user inputs 05/10/2008 - 08/10/2008 It will not split the dates correctly. Any way to look at this. Thanks very much
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Sure, I'll look at this a little later today, too busy right now :-) .
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
Thanks Scott really appreciate it
Jason
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Sorry, very busy today, just got back to this.

You're right, code was buggy, sorry.  I've made some adjustments, I hope this is good now:


--select * from eedont where name like '%jas%'

SET DATEFORMAT DMY

if object_id('tempdb.dbo.#PackageRulesDateBreak') is not null
    drop table #PackageRulesDateBreak
create table #PackageRulesDateBreak
(
    pkdaterulekey int,
    pkdaterulehotelid int,
    pknights int,
    daterulefrom datetime,
    dateruleto datetime
)
set nocount on
--reestablish base data in table
truncate table #PackageRulesDateBreak
insert into #PackageRulesDateBreak values(1, 63, 7, '01/10/2008' , '10/10/2008')
insert into #PackageRulesDateBreak values(1, 63, 7, '11/10/2008' , '15/10/2008')
insert into #PackageRulesDateBreak values(1, 63, 7, '16/10/2008' , '20/10/2008')
set nocount off

DECLARE @fDate AS DATETIME
DECLARE @TDate AS DATETIME
DECLARE @ID AS INT

SET @fDate = '05/10/2008'
SET @TDate = '08/10/2008'

SELECT *
FROM #PackageRulesDateBreak

--If an entire existing date range(s) is fully contained within the new date range,
--  for example, new range is 01/01/yyyy to 01/03/yyyy and
--  existing range is 01/02/yyyy to 01/03/yyyy,
--then delete the existing range(s) (the new range will completely replace the old one).
DELETE FROM #PackageRulesDateBreak
WHERE daterulefrom BETWEEN @fDate AND @tDate
AND dateruleto BETWEEN @fDate AND @tDate

IF EXISTS(
    SELECT MIN(dateruleto) AS dateruleto
    FROM #PackageRulesDateBreak
    WHERE (daterulefrom <= @tDate)
    AND (dateruleto > @tDate)
)
    INSERT INTO #PackageRulesDateBreak (daterulefrom, dateruleto, pkdaterulehotelid, pkNights)
    SELECT DATEADD(DAY, +1, @tDate), dateruleto, 63, 7
    FROM (
        SELECT MIN(dateruleto) AS dateruleto
        FROM #PackageRulesDateBreak
        WHERE (daterulefrom <= @tDate)
        AND (dateruleto > @tDate)
    ) AS derived

SELECT *
FROM #PackageRulesDateBreak

UPDATE #PackageRulesDateBreak
SET daterulefrom = CASE WHEN (daterulefrom >= @fDate AND daterulefrom <= @tDate)
                      THEN DATEADD(DAY, +1, @tDate)
                      ELSE daterulefrom END,
    dateruleto = CASE WHEN (dateruleto >= @fDate)
                      THEN DATEADD(DAY, -1, @fDate)
                      ELSE dateruleto END
WHERE (daterulefrom >= @fDate AND daterulefrom <= @tDate)
OR (daterulefrom <= @tDate AND dateruleto >= @fDate)

INSERT INTO #PackageRulesDateBreak (daterulefrom, dateruleto, pkdaterulehotelid, pkNights)
SELECT @fDate, @TDate, 63, 7

SELECT *
FROM #PackageRulesDateBreak
0
 
LVL 2

Author Comment

by:JasonChandler
Comment Utility
You the man Scott, this is working so well!!! You are a true Gent. Thanks very much, if I could give ya another 500 points somehow I would ;-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 51
Help in Understanding a SQL Query 7 26
C# SQL BULK INSERT CLASS 5 34
SQL JOIN 6 31
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now