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!
sqlcuriousAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:
I missed some detail, use union and change the query to this
select A.Projectnum, b.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
union
select A.Projectnum, b.linenum, a.Amount [Current], b.Amount [old], 
  case
	when b.Amount IS null and a.Amount IS not null then
	  'New'
	when b.Amount IS not null and a.Amount IS null then
	  'Removed'
	when b.Amount <> a.Amount then 
	  'Modified'
	else
	  'No change'
  end [Status]
from custcurrent a
left outer join custyest b on b.Projectnum = a.Projectnum and b.linenum = a.linenum

Open in new window

0
 
Ephraim WangoyaCommented:
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

0
 
Ephraim WangoyaCommented:
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

0
 
sqlcuriousAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.