asked on

How to handle incremental loads

Hi Experts I have two tables - one that has yesterday's data(dbo.custyest) and one that has today's data(dbo.custcurrent) as following:

dbo.custyest
Projectnum | Linenum | Amount | Date
123 | 1 | 50 | 09/19/2011
124 | 2 | 20 | 09/19/2011

dbo.custcurrent
Projectnum | Linenum | Amount | Date
123 | 1 | 80 | 09/20/2011
123 | 2 | 75 | 09/20/2011

The result should look like below: : For the modified ones status will be 'Modified' with the old and new values displayed, similarly if a new row is added it will be 'New' and if an existing row is removed it will be 'removed'
Projectnum | Linenum | Current | old | Status
123 | 1 | 50 | 80 | Modified
123 | 2 | 75 | | New
124 | 2 | | 20 | Removed

Please suggest how I can create this incremental table for each project number.
Thanks!

Ephraim Wangoya

use this

select A.Projectnum, a.linenum, b.Amount [current], a.Amount [old],
  case
	when b.Amount IS null and a.Amount IS not null then
	  'Removed'
	when b.Amount IS not null and a.Amount IS null then
	  'New'
	when b.Amount <> a.Amount then 
	  'Modified'
	else
	  'No change'
  end [Status]
from custyest a
left outer join custcurrent b on b.Projectnum = a.Projectnum

Open in new window

Ephraim Wangoya

Join on linenum as well

select A.Projectnum, a.linenum, b.Amount [current], a.Amount [old],
  case
	when b.Amount IS null and a.Amount IS not null then
	  'Removed'
	when b.Amount IS not null and a.Amount IS null then
	  'New'
	when b.Amount <> a.Amount then 
	  'Modified'
	else
	  'No change'
  end [Status]
from custyest a
left outer join custcurrent b on b.Projectnum = a.Projectnum and b.linenum = a.linenum

Open in new window

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

Thanks!