April 7, 2006 at 5:19 pm
Interesting solutions and thanks. I said 1 myself, but both are interesting solutions.
I also think that the requirements, asking to read the table once, are misleading. I don't think that many people will come up with a derived table using scalar values under pressure.
April 7, 2006 at 5:37 pm
To be quite honest, if another DBA/programmer brought the second statement to me, I'd ask him what he thought he was doing... The logic to think through that query is more complicated than the original problem. If they brought the first one, I'd probably suggest they not rely on the row ordering in the table. Despite Edmundo's post, in either case, I would demand that the DBA defend the technique they used to code it, and prove to me that it scales sufficiently in our environment.
If I were given just those two pieces of code and required to pick one, I'd have to go with the first due to it's simplicity... but I don't like it's reliance on row order; that goes against my understanding of good relational database design fundamentals. (on a side note, if I were to issue this as a quiz for employment myself, I'd hire the candidate that could point this out and explain it)
I submit the following script as a better solution, and I'd like to note that though I didn't specifically try to handle the situation that joshusch posted, this at least throws an obviously anomalous row that show's the bad data in the table.
select
a.name
, a.end_dt [unemployed_start]
, min(b.start_dt) [unemployed_end]
, datediff(day,a.end_dt,min(b.start_dt)) unemployed_days
from dbo.quiz1 a
left join dbo.quiz1 b
on a.name = b.name
and a.start_dt < b.start_dt
group by
a.name
, a.start_dt
, a.end_dt
having datediff(day,a.end_dt,min(b.start_dt)) 0
<>
Ok, nix the anomalous row bit, I didn't apply the update to 'end' his otherwise current employment before I tested. This query just identifies the period as unemployed.
<>
Matthew Galbraith
April 8, 2006 at 12:15 pm
The first one gives better performance because the inner joins are much better than inner queries. The reason was the inner joins use less number database reads, less CPU utilization and total server resouces than inner queries. It can be seen by starting SQL profile and execute each script and watch the magic.
Thanks,
Bhushan
April 8, 2006 at 7:25 pm
Being a rookie,
I don't ever pretend to know more than I do - that's why I ask a lot of questions...
I nevr would have thought about the row number reliance as has ben pointd out by just about everyone else. I just took the qustion and answers as being the definitive list.
Just goes to prove - I still have a long way to go!
Gavin Baumanis
Smith and Wesson. The original point and click device.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply