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'

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

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

According to your words, I think the new records (after splitting) should be something like:

01/10/2008 - 04/10/2008 05/10/2008 - 12/10/2008 13/10/2008 - 15/10/2008 16/10/2008 - 20/10/2008

And IMHO, this is pure business logic that should be dealt with outside of the database, i.e. in code (eg. a business layer method that checks for a date overlaps and performs the required updates while inserting the new record in one transaction).