Overlap logic check for DML inserts

Question

Overlap logic check for DML inserts

am-244616

SSCarpal Tunnel

Points: 4936
More actions
November 24, 2008 at 1:04 pm

#196339

Guys,
I have two tables one destination and the other source, I am trying to import the data from the source but I am unable to build
the SQL to do so
TableA (Source)
ID AgencyCDAgencyCostCDEffDateEndDate PymntValue
_______________________________________________________________________
1 BOFABOFACOST1/1/081/1/09100
2 BOFABOFACOST1/2/091/1/1050
TableB (Destination)
AgencyCDAgencyCostCDEffDateEndDate PymntValue
_______________________________________________________________________
BOFABOFACOST2/1/081/1/09 100
For the same combination of AgencyCD and AgencyCOstCD if the related effective and end date from the source does not overlap with destination only then the value should be imported. In the above example ID 1 from source should not be imported, however ID 2 from source should be imported to the destination table.
Is there any way to accomplish this using DML insert statement with select clause.
Any suggestions and inputs would help.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

am (11/24/2008)
Guys,
I have two tables one destination and the other source, I am trying to import the data from the source but I am unable to build
the SQL to do so
TableA (Source)
ID AgencyCDAgencyCostCDEffDateEndDate PymntValue
_______________________________________________________________________
1 BOFABOFACOST1/1/081/1/09100
2 BOFABOFACOST1/2/091/1/1050
TableB (Destination)
AgencyCDAgencyCostCDEffDateEndDate PymntValue
_______________________________________________________________________
BOFABOFACOST2/1/081/1/09 100
For the same combination of AgencyCD and AgencyCOstCD if the related effective and end date from the source does not overlap with destination only then the value should be imported. In the above example ID 1 from source should not be imported, however ID 2 from source should be imported to the destination table.
Is there any way to accomplish this using DML insert statement with select clause.
Any suggestions and inputs would help.
Thanks

To be sure we understand, after running the query to update TableB, it should look like this:

TableB (Destination)
AgencyCDAgencyCostCDEffDateEndDate PymntValue
_______________________________________________________________________
BOFABOFACOST2/1/081/1/09 100
BOFABOFACOST1/2/091/1/1050

Correct?

Would you also post the code you have already worked on that isn't working correctly.

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

That is the correct output of TableB

Below is the SQL that I used

SELECT A.AGENCYCD, A.AGENCYCOSTCD, A.EFFDATE, A.ENDDATE, A.PYMNTVALUE

FROM TABLEA A INNER JOIN TABLEB B ON

A.AGENCYCD = B.AGENCYCD AND A.AGENCYCOSTCD = B.AGENCYCOSTCD

WHERE NOT (A.ENDDATE =Y.[EFFDATE])

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Try the following in a test environment:

--TableA (Source)

--

--ID AgencyCD AgencyCostCD EffDate EndDate PymntValue

--_______________________________________________________________________

--1 BOFA BOFACOST 1/1/08 1/1/09 100

--2 BOFA BOFACOST 1/2/09 1/1/10 50

--

--TableB (Destination)

--

--AgencyCD AgencyCostCD EffDate EndDate PymntValue

--_______________________________________________________________________

--BOFA BOFACOST 2/1/08 1/1/09 100

--

create table dbo.TableA (

TableAID int identity(1,1),

AgencyCD varchar(10),

AgencyCostCD varchar(10),

EffDate datetime,

EndDate datetime,

PaymentValue int

);

insert into dbo.TableA (

AgencyCD,

AgencyCostCD,

EffDate,

EndDate,

PaymentValue)

select 'BOFA','BOFACOST','2008-01-01','2009-01-01',100 union all

select 'BOFA','BOFACOST','2009-01-02','2010-01-01',50;

create table dbo.TableB (

TableBID int identity(1,1),

AgencyCD varchar(10),

AgencyCostCD varchar(10),

EffDate datetime,

EndDate datetime,

PaymentValue int

);

insert into dbo.TableB (

AgencyCD,

AgencyCostCD,

EffDate,

EndDate,

PaymentValue)

select 'BOFA','BOFACOST','2008-02-01','2009-01-01',100;

select * from dbo.TableA;

select * from dbo.TableB;

insert into dbo.TableB (

AgencyCD,

AgencyCostCD,

EffDate,

EndDate,

PaymentValue)

select

a.AgencyCD,

a.AgencyCostCD,

a.EffDate,

a.EndDate,

a.PaymentValue

from

dbo.TableA a

inner join dbo.TableB b

on (a.AgencyCD = b.AgencyCD

and a.AgencyCostCD = b.AgencyCostCD)

where

not (a.EffDate between b.EffDate and b.EndDate

or a.EndDate between b.EffDate and b.EndDate)

select * from dbo.TableA;

select * from dbo.TableB;

drop table dbo.TableA;

drop table dbo.TableB;