Code overlap logic for DML inserts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Code overlap logic for DML inserts

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-11-24 : 15:04:29

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 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

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-24 : 15:20:26

In table B you have a date of 2/1/08 as the effdate, why?? Also you say ID 2 should be imported but why do you have a paymentvalue of 100 if that is the case, it's 50?


Insert into TableB(AgencyCD,AgencyCostCD,EffDate,EndDate,PymntValue)
Select a.AgencyCD,a.AgencyCostCD,a.EffDate,a.EndDate,a.PymntValue 
from 
TableA a
where datediff(Day,a.EffDate,a.EndDate) <= 365

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 00:11:05

[code]INSERT INTO Destination
SELECT s.*
FROM Source s
LEFT JOIN Destination d
ON d.AgencyCD =s.AgencyCD
AND d.AgencyCostCD =s.AgencyCostCD
AND (d.EffDate >=s.EffDate
AND d.EffDate<=s.EndDate)
OR (d.EndDate >=s.EffDate
AND d.EndDate<=s.EndDate)
WHERE d.AgencyCD IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources