Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to handle incremental loads

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

577 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