Solved

How to handle incremental loads

Posted on 2011-09-20
4
249 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36570620
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 36570629
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 36570666
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
 

Author Closing Comment

by:sqlcurious
ID: 36818522
Thanks!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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