- Home
- General
- General Chat
SQL Query Problem
Joined: 11-Jan-2007
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'
Joined: 21-Aug-2005
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).