Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to handle incremental loads

Posted on 2011-09-20
4
Medium Priority
?
261 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 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

670 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