[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

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
JasonChandler
Asked:
JasonChandler
  • 9
  • 5
1 Solution
 
BrandonGalderisiCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
JasonChandlerAuthor Commented:
@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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
JasonChandlerAuthor Commented:
@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
 
JasonChandlerAuthor Commented:
@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
 
Scott PletcherSenior DBACommented:
>> 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
 
JasonChandlerAuthor Commented:
@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
 
JasonChandlerAuthor Commented:
@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
 
JasonChandlerAuthor Commented:
Thanks so much, please see my last comment on the post!
Thank you, thank you
0
 
Scott PletcherSenior DBACommented:
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
 
JasonChandlerAuthor Commented:
@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
 
Scott PletcherSenior DBACommented:
Sure, I'll look at this a little later today, too busy right now :-) .
0
 
JasonChandlerAuthor Commented:
Thanks Scott really appreciate it
Jason
0
 
Scott PletcherSenior DBACommented:
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
 
JasonChandlerAuthor Commented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now