Solved

Update Date overlap maintaining date continuity

Posted on 2006-11-24
22
368 Views
Last Modified: 2012-06-27
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
Comment
Question by:Gautham Janardhan
  • 13
  • 9
22 Comments
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
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
be adjusted to
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
adjusted to
BD     AED     18/11/2006     20/11/2006     3.010


0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
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
  SELECT @InsertDate=DATEADD(day,1,@ToDate)
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

by:Gautham Janardhan
Comment Utility
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
be adjusted to
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

by:Gautham Janardhan
Comment Utility
<<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

by:Nightman
Comment Utility
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
  SELECT @InsertDate=DATEADD(day,1,@ToDate)
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

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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

by:Nightman
Comment Utility
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

by:Nightman
Comment Utility
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

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Author Comment

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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

by:Gautham Janardhan
Comment Utility
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

by:Nightman
Comment Utility
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
  SELECT @InsertDate=DATEADD(day,1,@ToDate)
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

by:Gautham Janardhan
Comment Utility
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

by:
Nightman earned 500 total points
Comment Utility
Hi gauthampj

Have you made any progress?

Regards
Nightman
0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 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

9 Experts available now in Live!

Get 1:1 Help Now