Solved

How to handle incremental loads

Posted on 2011-09-20
4
238 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
  • 3
4 Comments
 
LVL 32

Expert Comment

by:ewangoya
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:ewangoya
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:
ewangoya 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now