January 24, 2008 at 9:00 am
Every quarter we get a new "Zip code mailing. Two extracts are shown below:
09-2007 Input data
ID ZipCode CityName
1 91607 North Hollywood
2 91607 Valley Village
3 91609 Todu
4 ... ....
5
(Note mutiple city names for the same zipcode).
01-2008 Input data
ID ZipCode CityName
1 91607 North Hollywood
2 91607 Valley View
3 91609 Todu
4 ... ....
5
The combinaiton of ZIPCode and CityName form a primary key
Database table we need to update with every new mailing.
ID ZipCode CityName NewFlag NewDate DeletedFlag DeletdDate
1 91607 North Hollywood
2 91607 Valley Village 1 01-2008
3 91607 Valley View 1 01-2008
4 91609 Todu
The database table maintains records from all months but captures which records were added\deleted between successive mailings.
"Valley View" (91607) appeared in the most recent mailing but was not in the prior mailing so it must be new. Valley Village (91607) appeared in the prior mailing by not in the current one so it must be marked "Deleted". "Todu (91609) appears in both mailings so it is effectively unchanged.
TIA,
Barkingdog
January 24, 2008 at 9:10 am
Shouldn't be too complicated... what have you tried so far? What I'd do is use a LEFT JOIN to identify which records are to be deleted, and a RIGHT JOIN to identify which are the new ones. Or you could look at the possibilty of doing it all in one go with a FULL OUTER JOIN - I'm not sure whether that would work or not.
John
January 24, 2008 at 9:11 am
You haven't asked a question? What do you need help with?
What would you do? Show us that you have made some attempt to figure out what should happen, and that this isn't homework.
January 24, 2008 at 12:10 pm
Sorry about being unclear. I'd like to see the t-sql you would use to update the production database zip table given its current state and the previous zip data (assumed to be in a temp table).
TIA,
Barkingdog
P.S. The use of JOINS seemed a bit messy to me in this case. For each table, I concatenated the zipcode and Cityname fields giving, say ZIPCity, then I ran something like
select ZIPCity from most recent data import
except
select ZIPCity form previous import
to get the "new" records. Flipping the Select's around shows me the "Deleted" records. This approach is also a bit too!
January 24, 2008 at 12:15 pm
What you LEFT/RIGHT JOIN on? ZipCode and CityName?
TIA,
barkingdog
January 25, 2008 at 1:31 am
If EXCEPT works for you, then go with it, although I don't think you need to waste time concatenating into a single column - just specify both in the query instead. As for being a bit messy, they do say you can't make an omelette without breaking an egg! Yes, I would join on ZipCode and CityName.
It would be interesting to compare the execution plans of the EXCEPT and the JOIN methods, if you try the latter, please will you post an execution plan for each?
John
January 28, 2008 at 3:28 pm
I've tested Except vs Left Outer Join, and end up with nearly the same execution plans. Only difference is the Left Outer Join had a "Filter" in it, and the scans and reads were identical. Same for execution time. I'm pretty sure Include is just an Inner Join and Except is just a Left Outer Join, so far as the actual query engine is concerned. Just different ways to write the same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply