May 22, 2008 at 6:19 am
Sergiy (5/21/2008)
Solid approach, but too much typing, as for me.😎
Sergiy, did you test the code? It is not that solid at all.
Try this test data
SELECT 'A99', NULL, NULL, NULL, '18' UNION ALL
N 56°04'39.16"
E 12°55'05.25"
May 22, 2008 at 6:50 am
If there can be no duplicate Code1-Code4 values for any single record, try this
SELECT * FROM @t
DECLARE@Code1 CHAR(6),
@Code2 CHAR(6),
@Code3 CHAR(6),
@Code4 CHAR(6)
UPDATE@t
SET@Code1 = Code1 = COALESCE(Code1, Code2, Code3, Code4),
@Code2 = Code2 = COALESCE(NULLIF(Code2, @Code1), NULLIF(Code3, @Code1), NULLIF(Code4, @Code1)),
@Code3 = Code3 = COALESCE(NULLIF(NULLIF(Code3, @Code1), @Code2), NULLIF(NULLIF(Code4, @Code1), @Code2)),
@Code4 = Code4 = NULLIF(NULLIF(NULLIF(Code4, @Code1), @Code2), @Code3)
SELECT * FROM @t
N 56°04'39.16"
E 12°55'05.25"
May 22, 2008 at 8:36 am
I have an initial step in the process which removes duplicates as the destination system counts them, which follows an unusual sense of what a duplicated is. Also all records where the code value fields are all null are removed as they are not submitted. The whole process is far more elaborate than this one step, it was mostly the concept on how to go about condensing the fields I was struggling with.
Thanks again, despite its oddness, it was an interesting puzzle to look at.
May 22, 2008 at 2:37 pm
Peso (5/22/2008)
Sergiy, did you test the code?
I don't think it deserves it.
It's absolutely stupid task and useless exersise.
IMHO, any DB developer who takes such task to work should be fired immediately and black marked.
It just indicates that the person has no idea about normalization rules or has no intention of applying it for real life tasks.
BTW, good question for a job interview. Will put it in my notebook. 😎
_____________
Code for TallyGenerator
May 22, 2008 at 3:17 pm
You're so funny Sergiy!
Maybe Mr Lester didn't design the sucker at all?
Maybe he was just assigned to save the application?
Your kind of arrogance is just the one I can do without.
"I don't think it deserves it [to be tested]"... And yet you can comment it and say it is solid!
This kind of situations are very real life tasks.
Welcome the the real world my friend!
N 56°04'39.16"
E 12°55'05.25"
May 22, 2008 at 3:19 pm
Heh, yea its a project I have to do for governement regulations, however, the government chooses to analyze the data contrary to its actual use. Infact the database is normalized, and designed for our business use. The governement agency wants the data in this way, which from a business perspective is entirely irrelevant, and from our perspective sensless and wrong.
But that is part of the job I am expected to do, deal with eniterly screwed up systems on both ends, and make it function in some fashion.
I believe the VP's precise words were, "Here are the regulations, work your magic and get it done. We have ten days, from the date of receipt" Of course that was five days prior.
Someday, I may decide to go work for a company that designs its own products and systems, one that allows for time to test completely, on and on for all the things that would make such strange problems go away.
Until then, I thrive on the challenge of less than ideal conditions, attempting to work with existing vendor systems, in all their good and bad glory. Its a different sort of world when you don't have control of the systems structures, nor even given the time to properly test things.
Until then, I may as well enjoy these pointless exercises, and learn as much as I possibly can. This position is definately not for ridgid and structured personalities, infact in the ten years I have worked here, we have lost four people like that to breakdowns (They simply could not adapt to the loose and fast changes here), and six who decided it was to difficult of a job.
Heh, Peso, I was mid typing this when you posted.
Yep, the real world, what a trip :w00t:
May 26, 2008 at 10:54 pm
Guys, it has nothing to do with reality of this world.
One of my recent projects was like this.
It was for a big company, I mean very big, bigger than a government.
Because a government rules just one country, and this company rules many governments.
Of course they were dictating what to do, up to table design.
Unfortunately for them project has got pretty strong requirements to the system performance, and it was absolutely clear for me their design won't work.
Varchar identities, concatenated values in fields, denormalized tables - everyone saw that and we all know what to expect from it.
So, I was strongly against the offered design, it was big and long argument, and I clearly refused to what they asked me to do.
Eventually they have found another, more "realistic" guy to replace me on the project. He did everything exactly as they said and everybody was happy.
Fortunately, this project had another section where we need to process inbound filed, validate the data and populate main database.
I was assigned to that part which was not so significant and noone told how to implement it.
Because I knew what's gonna happen when project is meant to go to Production I kept working on my own design and my inbound process was made to populate 2 structures: mine and "realistic".
Eventually project was ready for deployment. Everything was working fine, everybody was happy with the job done.
Until the performance test came to the picture.
Database was populated with 3 years volume of data and users started to click.
Response time on different pages was 4..12 times more than allowed limit.
Oooops.
Happy faces disappeared, 2 project managers on their side resigned not to be fired, our company was facing big financial trouble which could lead to liquidation.
At that stage I told our Project Manager about parallel structure. We did quick test and I've got green light to make all necessary changes to Application to use my design while he was convincing them not trying to fix failed design but to switch to the working one.
Surprisingly enough he did not face big problems on that way. They did not try to suggest what we should do anymore.
"Realist" was removed from the project, I was reinstated, and in couple of months the system easily passed the performance test.
After project went to production happy customers revealed that there were 5 other companies from different countries tried to do it before us. All failed. They must've been too realistic. 😉
And that it was their first 100% successful project in last 12 years.
And you know what?
We moved to a new project which adds new functionality to the existing system. And no one from the Big Company Bosses dares to express any suggestion how to design the system.
So, this is the real world too.
It's your choice on which part of that real world to be.
It's all about professionalism and self-respect.
Ask yourself: what are you on your job?
A typist?
A MS certified monkey?
Do you really hope to gain some respect and professional skills on this way?
Don't know about you, but I prefer to be respectful Professional.
_____________
Code for TallyGenerator
May 27, 2008 at 12:09 am
You're not much of a team player, are you?
Sergiy (5/26/2008)
Guys, it has nothing to do with reality of this world.
In which reality/world did you make those changes you write about?
Everquest?
As I see it, as a consultant you are obliged to tell the customer if you see something wrong with design but you cannot force them to change!
If they still want to use old design, despite your efforts to convince them to change, I would let them use the old design. But I would also have them sign a contract that says everything I have tried to convínce them but they like to use old design. This way it can't bite me afterwards.
"If the customer wants me to paint the Eiffel Tower with a toothbrush I would, even though I could air spray-paint the sucker. The customer pays and have final decision how to do the work, unless they ask you which way is the best".
N 56°04'39.16"
E 12°55'05.25"
May 27, 2008 at 3:17 am
"If the customer wants me to paint the Eiffel Tower with a toothbrush I would, even though I could air spray-paint the sucker. The customer pays and have final decision how to do the work, unless they ask you which way is the best".
So, again, who are you in this situation?
Typist?
Qualified monkey?
Does it work for car mechanics? For plumbers? For electricians?
Do customers tell a taxi driver how to drive a car?
Why not?
Probably because those guys respect their professions and don't tolerate suggestions from people who suppose to consume the result of their professional work, not to tell them how to do it.
People will never accept you as a professional if you're not acting as one.
_____________
Code for TallyGenerator
May 27, 2008 at 3:49 am
Sergiy (5/27/2008)
So, again, who are you in this situation?Typist?
Qualified monkey?
I am their business partner that solves their problem in a manner that they themself later can administrate, understand, maintain and further develop within. Unless they ask me if this can be done more efficient; Then I develop the thingies more efficient and also educate the customers so that they later themself can administrate, understand, maintain and further develop.
Sergiy (5/27/2008)
Does it work for car mechanics? For plumbers? For electricians?
Oh yes! If I tell my plumber I want my toilet in that corner, I would expect my plumber to install the toilet where I want. If he then tells me that it can't be done because then he can't use the new 4" stainless steel mounting screws I wouldn't care less. I would find another plumber that could install my toilet where I want. Not a plumber that tells me I can't have my toilet there because he refuses to do "the old way"...
Unless the plumber informs me that it is against the law to have a sour water crossing a electrical cable, or similar. Then I have to follow the law. Then the plumber has done his job professional and maybe he even can suggest another way to still have my toilet in the corner I choose, but it requests this and that work by rerouting the sour water.
I also expect my electrician to install my wall switches where I want them. And my sockets. And my ceiling sockets for my lamps. If the electricians says "Can't do that. That's the old way. You can have your socket there because then I can use my new ergonomical screw-driver", I would find another electrician that can accomodate my needs.
I also don't care how my car mechanic replaces my muffler. He can use left or right hand all for what I care. I want the muffler replaced in a timely fashion without costing to much. I also expect the muffler to work the same way as before and look the same.
I don't want my car mechanic to replace my SAAB muffler with a PORSCHE muffler because the Porsche muffler is more cool and have cooler sound.
Sergiy (5/27/2008)
Do customers tell a taxi driver how to drive a car?
Happens all the time. Have you taken a cab in NY? Or any major city for that matter?
Sergiy (5/27/2008)
People will never accept you as a professional if you're not acting as one.
Now you are getting personal.
I have seen your code here Set based solution possible? and I am not impressed.
Is that the kind of quality you deliver to customers?
N 56°04'39.16"
E 12°55'05.25"
May 27, 2008 at 4:59 am
Oh yes! If I tell my plumber I want my toilet in that corner, I would expect my plumber to install the toilet where I want. If he then tells me that it can't be done because then he can't use the new 4" stainless steel mounting screws I wouldn't care less. I would find another plumber that could install my toilet where I want. Not a plumber that tells me I can't have my toilet there because he refuses to do "the old way"...
Is it really an illustration for "yes"?
You expect a desired result from a plumber, but if you'll tell him which screws to use or how to fix the pipes - he'll in best case will ignore you or suggest you to shut up.
In our case: customer entitled to decide on web-page layout, on performance benchmarks, but what to be used to deliver required result - it's a professional business.
Sergiy (5/27/2008)
Do customers tell a taxi driver how to drive a car?Happens all the time. Have you taken a cab in NY? Or any major city for that matter?
And?
Sergiy (5/27/2008)
People will never accept you as a professional if you're not acting as one.Now you are getting personal.
Me???
You confuse me with somebody.
Probably with yourself.
Try to express the same idea with a phrase you won't consider personal.
See if you can impress me.
_____________
Code for TallyGenerator
May 27, 2008 at 6:06 am
Sergiy (5/27/2008)
You expect a desired result from a plumber, but if you'll tell him which screws to use or how to fix the pipes - he'll in best case will ignore you or suggest you to shut up.
We already know how you are able to treat your customers. There is no need to repeat your statement and rub it in.
In this scenario, we had a plumber at our previous house when we decided to renovate the bathroom.
We called in a plumber and we told him he couldn't use the 3" whiskar due to thin pipes. They wouldn't stand the pressure. Neither could he use high-pressure water.
"No problem" the plumber said and begun to do his work.
What happened? He ignored our advice/recommendation, damaged the pipes and at the end he was fined to replace all pipes and also cover our legal fees in court. All because he wouldn't listen. And we were out of toilet for two months. Not that nice. And all just because he plumber thought he knew our pipes better than ourself.
The analogy to the real world is that I as a consultant is brought in to solve a particular problem at customer. Not tell him to shut up when I think another design is better. Then we are not business partners.
Even the customer doesn't always know the present and future implications of a design change.
The customer and I can however discuss design changes and weight that against time-frame, budget, future moditications and all other "showstoppers" back and forth. But at the end, it is still the customer who decides what to be done.
I agree the customer shouldn't interfere if I use EMS, QA or SSMS to get the job done.
In some cases they do interfere and do you know what? They are entitled to do so. It's their data, their system and their network. Should you then tell the customer to shut up? I believe you would. I also think you would embarres yourself by saying "I don't agree with your security policies. I will bring in and connect my own laptop to your network to get the job done.".
As a sidenote, I never understood your obsession with the HASH query hint here Set-based solution possible? instead of producing an efficient solution. Even I spotted the triangular JOIN.
Please, tell me about your obsession about the HASH join query hint.
N 56°04'39.16"
E 12°55'05.25"
May 27, 2008 at 6:28 am
Even though we all, including the OP, recognize that this is a terrible requirement, has anyone actually come up with a solution that shifts the data left without resorting it? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2008 at 6:37 am
Yes, I posted one solution 5/22/2008 1:50 PM but that depends on unique values. Duplicate values will be removed, but that might not be a problem due to business rules?
Also the suggestion posted 5/22/2008 12:48 PM will work, unsorted, if you remove the "Value" in the ORDER BY statemement after the CASE thingy.
N 56°04'39.16"
E 12°55'05.25"
May 27, 2008 at 8:43 am
Wow, looks like people were busy here.
Jeff, and all..
I have the code working as I expect it to. Not as a copy paste of what is here of course, but the solutions suggested were terriffic help for me to get the plan for the code, and some of the catches in the process. Again, thanks for all the suggestions.
As far as all the not technical stuff, well, I guess I am just too mellow to get all worked up about it. Yes, often there are better ways to do what ever it is I am asked to do, sometimes I am heartily opposed to the designs. However, as far as what I am, I am a part of a whole company. Not my own independent business. My job is specifically to offer all possible options, identify problems, present these things to management, and it is their task to decide which we use. That is how being part of something works in my mind.
As far as respect, professionalism and all that goes with it, well I just don't seek such approval. Here, within the company I work for, I have that respect to the highest levels, believe it or not. It makes little difference to me, I do this job because I like it. I enjoy the tasks, which to me are simply puzzles to solve. Again however, I have a different set of motivations and goals as for example, Sergiy, who seems to enjoy the way things are for him, good on ya Sergiy.
If I desired the acclaim and such, I would not pick this profession. Hmm, if I desired such things, I would be a novelist by this point, rather than just hobby writing. Heh, and I know there are a fair number of writers on this board.
Soooo, on to the next of the two dozen projects being shuffled to the front of the pile.
Thanks all!
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply