Update, Append, Purge, Composite key

[userid128223]

I have following data that has source QVD and daily qvd. Source needs to get modified on daily basis via updates from daily.qvd.

• Uniq key is composed of:  EmpID+EmpName+CreateDate
• Daily file will have duplicate data where only updateDate will indicate latest recods which needs to be kept.
• Data that is similar in daily.qvd needs to be purged out based on latest Updatdate.

Daily.qvd

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$200	20	1/6/2014	1/6/2014
123	Joe	$100,000	20	1/6/2014	1/7/2014
123	Joe	$500,000	20	1/6/2014	1/9/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$55	20	1/8/2014	1/22/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
235	Doe	$500	50	1/15/2014	1/15/2014
345	Kim	$456	56	1/9/2014	1/9/2014
724	Mele	$25	20	1/18/2014	1/18/2014
887	Duwane	$15	30	1/22/2014	1/22/2014

Source.QVD

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$200	20	1/6/2014	1/6/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$55	20	1/8/2014	1/22/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
345	Kim	$456	56	1/9/2014	1/9/2014

FINAL RESULTS:

EmpID	EmpName	Amount	TerminalNo	CreateDate	UpdateDate
123	Joe	$500,000	20	1/6/2014	1/9/2014
123	Joe	$400	40	1/8/2014	1/8/2014
235	Doe	$200,000	20	1/8/2014	2/1/2014
345	Kim	$456	56	1/9/2014	1/9/2014
724	Mele	$25	20	1/18/2014	1/18/2014
887	Duwane	$15	30	1/22/2014	1/22/2014

[Nicole-Smith]

If you're new to SQL, start by going through this tutorial (http://www.w3schools.com/sql/default.asp?PHPSESSID=300ae3404d5fa2612f238abeebb8869c).  It's pretty simple and doesn't dive in very deep, but it should give you a good start.

If any of the answers above are correct or helpful, please mark them so that others can find solutions to their problems too

[userid128223]

I did thanks, your solutions are simple easy to understand and to the point.

Thanks.

[userid128223]

So if you did not have update date and only wanted to update records based on uniq key then you will use

where not exist (key) 

however because we had update date in the mix this time, you had to do inner join and group by clause.

[Nicole-Smith]

We could have gone the exists/not exists route for this example as well, but it works better using the inner join because it will take the newest data whether it's in the Daily or Source file.  (It's also less code to do it with the inner join.)

[userid128223]

out of curiosity i tried this. I get all the records. why please explain

INNER JOIN (FINAL)

LOAD EmpID, EmpName, Amount, TerminalNo, CreateDate, max(UpdateDate) as UpdateDate RESIDENT FINAL

GROUP BY EmpID, EmpName, Amount, TerminalNo, CreateDate;

[Nicole-Smith]

You shouldn't be including Amount and TerminalNo, they make every row unique.  You only want the unique rows for EmpID, EmpName, CreateDate, and max(UpdateDate).

[userid128223]

make sense amazing.

[userid128223]

dont give me solution but just give me theory if you were to approach this using where not exist. how would you solve it.

In these kind of situation is there a rule of thumb, do you have theory on that. if its this then do that kind of scenario.

[Nicole-Smith]

Load all from Source adding a key for unique lines (EmpID and CreateDate).  Then load all from Daily where same key does not exist in Source.  Then you still need the inner join to get latest update date.

[Nicole-Smith]

Looking at your data, it seems that everything in Source is also in Daily, so why not just load Daily in for the newest UpdateDate?  (Maybe your example isn't like your actual data, but if it is, it seems like the Source part isn't even needed since all data would exist in Daily anyway.)