Solved

# Update Date overlap maintaining date continuity

Posted on 2006-11-24
Medium Priority
445 Views
CurFrom      CurTo      FromDate                     ToDate                  ExcRate

BD      AED      1/11/2006                      5/11/2006                      3.000
BD      AED      6/11/2006                      9/11/2006                      3.120
BD      AED      10/11/2006      15/11/2006      3.060
BD      AED      16/11/2006      20/11/2006      3.010
BD      AED      21/11/2006      null            3.020

1) Primary Key is CurFrom, CurTo, FromDate.So it cannot be updated.
But New records can be inserted. Old records can be deleted.
2) Todate can be null,
2) Date Range has to be always continous when ever a new record is inserted or deleted.

3) if date overlaps during insert or updates existing overlap
dates shud be updated accordingly, so that no overlap is present
4)if i try to insert a new record

a) BD - AED   1/11/2006 - 26/11/2006  5.000 - all the above records are in overlap
so the table shud look like (delete rec 2,3,4)

BD - AED  1/11/2006 - 26/11/2006      5.000
BD - AED  27/11/2006 - null              3.000 - (last record)

b) Continuity of the date range has to be maintained.
0
Question by:Gautham Janardhan
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 13
• 9

LVL 29

Expert Comment

ID: 18006545
Some questions on the logic
What would happen if you tried to insert
BD - AED   2/11/2006 - 26/11/2006  5.000
Would
BD     AED     1/11/2006                     5/11/2006                     3.000
BD     AED     1/11/2006                    1/11/2006                     3.000

And if you tried to insert
BD - AED  10/11/2006 - 17/11/2006  5.000
Would
BD     AED     10/11/2006     15/11/2006     3.060 be deleted
and
BD     AED     16/11/2006     20/11/2006     3.010
BD     AED     18/11/2006     20/11/2006     3.010

0

LVL 29

Expert Comment

ID: 18006643
Give this a try - not sure if it meets all of the requirements ;)

declare @CurFrom nvarchar(2)
declare @CurTo nvarchar(3)
declare @FromDate datetime
declare @ToDate datetime
declare @ExcRate  decimal(18,3)
declare @LastExc decimal(18,3)
declare @CurrentDate datetime
declare @InsertDate datetime
declare @ErrNo datetime

SET NOCOUNT ON
SELECT @CurrentDate=CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)
SELECT @ErrNo=0
select @CurFrom='BD',@CurTo='AED',@FromDate='20061101',@ToDate='20061126',@ExcRate=5.00
BEGIN TRANSACTION
SELECT @LastExc=(SELECT Top 1 ExcRate FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo ORDER BY FromDate DESC)
SELECT @InsertDate=ISNULL(MAX(FromDate),@CurrentDate) FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate
If @InsertDate<=@ToDate
begin
end
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND
((FromDate BETWEEN @FromDate AND @ToDate) AND ((ToDate BETWEEN @FromDate AND @ToDate) OR ToDate IS NULL))

INSERT INTO Curr VALUES(@CurFrom,@CurTo,@FromDate,@ToDate,@ExcRate)
if @ToDate<@CurrentDate OR @ToDate IS NOT NULL
begin
IF NOT EXISTS(SELECT * FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate)
begin
INSERT INTO Curr VALUES(@CurFrom,@CurTo,@InsertDate,null,@LastExc)
end
end
SELECT @ErrNo=@@Error
if @ErrNo<>0
begin
ROLLBACK
end
else
begin
COMMIT
end
0

LVL 29

Author Comment

ID: 18006666
nightman
<<What would happen if you tried to insert
BD - AED   2/11/2006 - 26/11/2006  5.000
Would
BD     AED     1/11/2006                     5/11/2006                     3.000
BD     AED     1/11/2006                    1/11/2006                     3.000

>>

if i try to insert
BD - AED   2/11/2006 - 26/11/2006  5.000

records will be

BD     AED     1/11/2006                     1/11/2006                     3.000
BD - AED   2/11/2006 - 26/11/2006  5.000
BD     AED     27/11/2006     null          3.020

0

LVL 29

Author Comment

ID: 18006671
<<And if you tried to insert
BD - AED  10/11/2006 - 17/11/2006  5.000
>>

records will be

BD     AED     1/11/2006                     5/11/2006                     3.000
BD     AED     6/11/2006                     9/11/2006                     3.120
BD     AED     10/11/2006     17/11/2006     5.000                    -- updated
BD     AED     18/11/2006     20/11/2006     3.010                  -- from updated
BD     AED     21/11/2006     null          3.020
0

LVL 29

Expert Comment

ID: 18006759
OK, give this stored proc a shot:

CREATE PROCEDURE CurrencyOverlap
(
@CurFrom nvarchar(2),
@CurTo nvarchar(3),
@FromDate datetime,
@ToDate datetime,
@ExcRate  decimal(18,3)
)
as
SET NOCOUNT ON
declare @ErrNo int
declare @LastExc decimal(18,3)
declare @CurrentDate datetime
declare @InsertDate datetime

SELECT @CurrentDate=CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)
SELECT @ErrNo=0
BEGIN TRANSACTION
SELECT @LastExc=(SELECT Top 1 ExcRate FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo ORDER BY FromDate DESC)
SELECT @InsertDate=ISNULL(MAX(FromDate),@CurrentDate) FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate
If @InsertDate<=@ToDate
begin
end
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND ((FromDate BETWEEN @FromDate AND @ToDate) AND ((ToDate BETWEEN @FromDate AND @ToDate) OR ToDate IS NULL))
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
INSERT INTO Curr VALUES(@CurFrom,@CurTo,@FromDate,@ToDate,@ExcRate)
UPDATE Curr SET ToDate=dateadd(day,-1,@FromDate) WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate<@FromDate AND ToDate>=@FromDate

if @ToDate<@CurrentDate OR @ToDate IS NOT NULL
begin
IF NOT EXISTS(SELECT * FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate)
begin
INSERT INTO Curr VALUES(@CurFrom,@CurTo,@InsertDate,null,@LastExc)
end
end

SELECT @ErrNo=@@Error
if @ErrNo<>0
begin
ROLLBACK
return
end
else
begin
COMMIT
end
0

LVL 29

Author Comment

ID: 18006781
this validation shud take place when i insert or update or delete records from that table.

if possible in a trigger
0

LVL 29

Expert Comment

ID: 18006982
Quite a lot to put in a trigger, and you run the risk of this occurring recursively (especially bad for concurrency here). I would do it all in a stored proc.
The one that I have given you should work quite well for update or insert.

I will have to write another one for delete - will put it together later today or this evening (if you can wait that long).
0

LVL 29

Expert Comment

ID: 18008102
When deleting, there will be a gap in the data (obviously). To you want to extend the previous record to include the missing period, or the next record.

e.g.

BD     AED     1/11/2006      5/11/2006       3.000
BD     AED     6/11/2006       9/11/2006      3.120
BD     AED     10/11/2006     15/11/2006     3.060
BD     AED     16/11/2006     20/11/2006     3.010
BD     AED     21/11/2006     null                3.020

DELETE -->BD     AED     10/11/2006     15/11/2006     3.060

Should the results look like this:

BD     AED     1/11/2006      5/11/2006       3.000
BD     AED     6/11/2006      15/11/2006      3.120
BD     AED     16/11/2006     20/11/2006     3.010
BD     AED     21/11/2006     null                3.020

OR

BD     AED     1/11/2006      5/11/2006       3.000
BD     AED     6/11/2006       9/11/2006      3.120
BD     AED     10/11/2006     20/11/2006     3.010
BD     AED     21/11/2006     null                3.020
0

LVL 29

Expert Comment

ID: 18009155
Give this a shot for the delete stored procedure:

ALTER Procedure DeleteCurrency
(
@CurFrom nvarchar(2),
@CurTo nvarchar(3),
@FromDate datetime
)
as
SET NOCOUNT ON
declare @LastToDate datetime

BEGIN TRANSACTION
SELECT @LastToDate=ToDate WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
UPDATE Curr SET ToDate=@LastToDate WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=(SELECT MAX(FromDate) FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate<@FromDate)
COMMIT
0

LVL 29

Author Comment

ID: 18010244
nightman,

WHen deleting also continuity shud be maintained,

My begin tran and commit tran will be from the application .its just a windows application form in C# .

So a user can insert  a new exchange rate for a period.
but he can only update the rate.
he can also delete a record.

More over using web service we pull data to this table from another location.

So ideally trigger can only solve my problem.
Hope i am clear on this.u need not worry abt the trans within the trigger, we will manage that within the db layer in our application.
0

LVL 29

Expert Comment

ID: 18010549
Hi gauthampj

Doing it in a trigger is going to result in recursion, so you don't want this. The two stored procs that I have posted should meet you needs (one for updatre/insert, another for delete). You can simply remove the transaction code from it if you want.
0

LVL 29

Author Comment

ID: 18025561
Nightman,

Thanks for the solution.

But the problem i have here is that, data can get populated in the table thru import also.
We use a webservice to pull data to this table from a remote location.

thats where we have the problem. The frequency of the data pulling is once a day.
0

LVL 29

Expert Comment

ID: 18025857
I understand that a trigger is ideal, as you then don't have to worry about where the update came from (Query Analyser, SSIS, DTS, Web page, etc). However, the nature of the changes will result in an update (or delete) being fired from the trigger, firing off another trigger, etc..

This will result in recursion, row locking (possibly even page locking) and blocking processes, and ultimately deadlock and transaction failure, so this is not a good idea.

Cheers
Night
0

LVL 29

Author Comment

ID: 18027496
Nightman i think trigger recursion we can control

Recursive Triggers
A trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set. There are two types of recursion:

Direct recursion
Occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3, which causes trigger Trig3 to fire. Trig3 updates table T3 again, which causes trigger Trig3 to fire again.

Indirect recursion
Occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. For example, an application updates table T1, which causes trigger Trig1 to fire. Trig1 updates table T2, which causes trigger Trig2 to fire. Trig2 in turn updates table T1 which causes Trig1 to fire again.

Only direct recursion is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion, set the nested triggers server option to 0, as well.

0

LVL 29

Expert Comment

ID: 18029180
It's not just the recursion, it's the locking and blocking.
Imagine concurrent inserts to the data table that essentially handles seqential inserts - there will be constant locking and blocking as the code has to iterate up and down the data to maintain the continuity.

Instead, consider for batch imports that you import all the data into a staging table, clean it up, making sure that the continuity is maintained and then insert into the main table.
0

LVL 29

Author Comment

ID: 18029602
Nightman,

Lets say i import data to a staging table, and within the table i do a continuity check and correct the data.

even then i will have to insert the data into my main data table. there also continuity check will have to be done with the existing data.

correct me if i m wrong
0

LVL 29

Expert Comment

ID: 18029682
You're wrong ;)

You check the start and end point of your 'sanitised' data against the existing data in the table, and simply replace everything in between. Much quicker. Of course, this assumes that the sequence will be correct (my understanding is that you are pulling FOREX rates on a daily batch basis, so I assume that this will come in sequentially).
0

LVL 29

Author Comment

ID: 18034253
Nightman,
Thanks for ur continued patience.

I will tell u our application logic. we use our own locking methods (soft locking) which is accomplished by tagging the spid() of the connection against the read record.(Our application works in one connection).

1) My data Import to forex table and also updation function of this forex will always be in the single user mode.
2) the function to read rate will be dirty read with no lock.

<<in the prev post u mentioned
<<You check the start and end point of your 'sanitised' data against the existing data in the table, and simply replace everything in between. Much quicker. Of course, this assumes that the sequence will be correct (my understanding is that you are pulling FOREX rates on a daily batch basis, so I assume that this will come in sequentially). >>

this logic wont work. here u r talking abt delete - insert which i cannot apply.
only the changed records need to be updated. if i m going to do that then blocking issues will arise even if i use stagging table.

0

LVL 29

Expert Comment

ID: 18034766
OK - We will assume that you can only do one record at a time. Please give these a try.

CREATE TRIGGER dbo.Curr_IUTrig on dbo.Curr
for insert, update as

if @@ROWCOUNT = 0 return -- get out if no records affected!
begin
set NOCOUNT on

declare @CurFrom nvarchar(2)
declare @CurTo nvarchar(3)
declare @FromDate datetime
declare @ToDate datetime
declare @ExcRate  decimal(18,3)
declare @LastExc decimal(18,3)
declare @CurrentDate datetime
declare @InsertDate datetime

SELECT CurFrom=@CurFrom,CurTo=@CurTo,FromDate=@FromDate,ToDate=@ToDate FROM inserted
SELECT @CurrentDate=CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)
SELECT @LastExc=(SELECT Top 1 ExcRate FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo ORDER BY FromDate DESC)
SELECT @InsertDate=ISNULL(MAX(FromDate),@CurrentDate) FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate
If @InsertDate<=@ToDate
begin
end
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND ((FromDate BETWEEN @FromDate AND @ToDate) AND ((ToDate BETWEEN @FromDate AND @ToDate) OR ToDate IS NULL))
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
INSERT INTO Curr VALUES(@CurFrom,@CurTo,@FromDate,@ToDate,@ExcRate)
UPDATE Curr SET ToDate=dateadd(day,-1,@FromDate) WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate<@FromDate AND ToDate>=@FromDate

if @ToDate<@CurrentDate OR @ToDate IS NOT NULL
begin
IF NOT EXISTS(SELECT * FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate>@ToDate)
begin
INSERT INTO Curr VALUES(@CurFrom,@CurTo,@InsertDate,null,@LastExc)
end
end
end
GO

CREATE TRIGGER dbo.Curr_DTrig on dbo.Curr
for delete as

if @@ROWCOUNT = 0 return -- get out if no records affected!
begin
set NOCOUNT on

declare @LastToDate datetime
declare @CurFrom nvarchar(2)
declare @CurTo nvarchar(3)
declare @FromDate datetime

SELECT @CurFrom=CurFrom, @CurTo=CurTo,@FromDate=FromDate FROM Deleted

SELECT @LastToDate=ToDate WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
DELETE FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=@FromDate
UPDATE Curr SET ToDate=@LastToDate WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate=(SELECT MAX(FromDate) FROM Curr WHERE CurFrom=@CurFrom AND CurTo=@CurTo AND FromDate<@FromDate)
end
0

LVL 29

Author Comment

ID: 18091623
Nightman sorry i was out of station for the last week. I will run this and tell ya the result.

Neways thank you for the effort
0

LVL 29

Accepted Solution

Nightman earned 2000 total points
ID: 18198035
Hi gauthampj

Regards
Nightman
0

LVL 29

Expert Comment

ID: 18208307
Thanks for the PAQ, but more importantly does it address your requirement? If the solution doesn't work, it must either be adjusted or I don't mind if you request a refund/delete of this Q.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.
###### Suggested Courses
Course of the Month15 days, 5 hours left to enroll