August 4, 2010 at 10:45 am
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.
Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
August 4, 2010 at 10:48 am
Brandie Tarvin (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
Maud says: "Good point" 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 4, 2010 at 10:49 am
WayneS (8/3/2010)[/b]
Well, since Steve asked for opinions on one topic, let me broach another topic.What do you'll think about this[/url]? Is there a valid point? Are the people screaming about it being racist? Something else?
Maybe being racist, more likely just demonstrating their oversimplistic dimwitted world-view and narrow-minded stupidity. I'm inclined to agree with Sophia Nelson's comments.
edit: something queered the quote structure
Tom
August 4, 2010 at 10:58 am
Alvin Ramard (8/4/2010)
skcadavre (8/4/2010)
Jack Corbett (8/4/2010)
So you know the search procedure you are working on is complex when you can't write up a question about it for the forums because it takes so long to explain and hits so many tables.And yes, I'm the one trying to formulate the question. Odds are by the time I've figured out how to word the question I'll have salved the problem I'm having.
Similar thing happened to me fairly recently. Spent 2 days trying to solve a problem, then wrote up a forum post which took me 3 hours to do, during which time I solved my own problem.
Seems to be a good way to solve a problem, forcing you to look at it in a different way.
If you solve your own problem before posting the problem you should ask yourself if this is something others can benefit from. If so then consider posting both the problem and the solution.
Actually, this would probably be good material for an article. :smooooth:
August 4, 2010 at 11:00 am
Lynn Pettis (8/4/2010)
Alvin Ramard (8/4/2010)
skcadavre (8/4/2010)
Jack Corbett (8/4/2010)
So you know the search procedure you are working on is complex when you can't write up a question about it for the forums because it takes so long to explain and hits so many tables.And yes, I'm the one trying to formulate the question. Odds are by the time I've figured out how to word the question I'll have salved the problem I'm having.
Similar thing happened to me fairly recently. Spent 2 days trying to solve a problem, then wrote up a forum post which took me 3 hours to do, during which time I solved my own problem.
Seems to be a good way to solve a problem, forcing you to look at it in a different way.
If you solve your own problem before posting the problem you should ask yourself if this is something others can benefit from. If so then consider posting both the problem and the solution.
Actually, this would probably be good material for an article. :smooooth:
Good idea.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 4, 2010 at 11:02 am
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.
Swap?
I got stuck this afternoon helping out with a query where I needed to add several columns that stored elapsed time.
Easy enough? Sure, except that the person who designed the database stored those elapsed times as the TIME datatype.
And you can't add two time columns. Makes sense, there's no meaning to 11am + 5pm. However in this case 11am meant 11 hours and 5pm meant 17 hours. Cue multiple conversions to datetime followed by DATEPART(mi,...) followed by DATEADD followed by a cast back to TIME.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2010 at 11:03 am
Lynn Pettis (8/4/2010)
Actually, this would probably be good material for an article. :smooooth:
Uh-huh. Sounds to me like you're trying to get Jack to write an article so that YOU don't have to.
@=)
August 4, 2010 at 11:06 am
Brandie Tarvin (8/4/2010)
Lynn Pettis (8/4/2010)
Actually, this would probably be good material for an article. :smooooth:Uh-huh. Sounds to me like you're trying to get Jack to write an article so that YOU don't have to.
@=)
Sounds like a great idea. He could use something like that during his campaign. 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2010 at 11:22 am
GilaMonster (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Swap?
I got stuck this afternoon helping out with a query where I needed to add several columns that stored elapsed time.
Easy enough? Sure, except that the person who designed the database stored those elapsed times as the TIME datatype.
And you can't add two time columns. Makes sense, there's no meaning to 11am + 5pm. However in this case 11am meant 11 hours and 5pm meant 17 hours. Cue multiple conversions to datetime followed by DATEPART(mi,...) followed by DATEADD followed by a cast back to TIME.
and how do they store +24 hours in that time column ? (add an extra daycounter column ??) 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 4, 2010 at 11:26 am
ALZDBA (8/4/2010)
GilaMonster (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Swap?
I got stuck this afternoon helping out with a query where I needed to add several columns that stored elapsed time.
Easy enough? Sure, except that the person who designed the database stored those elapsed times as the TIME datatype.
And you can't add two time columns. Makes sense, there's no meaning to 11am + 5pm. However in this case 11am meant 11 hours and 5pm meant 17 hours. Cue multiple conversions to datetime followed by DATEPART(mi,...) followed by DATEADD followed by a cast back to TIME.
and how do they store +24 hours in that time column ? (add an extra daycounter column ??) 😉
When can we convert to Metric Time?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
August 4, 2010 at 11:29 am
mtillman-921105 (8/4/2010)
ALZDBA (8/4/2010)
GilaMonster (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Swap?
I got stuck this afternoon helping out with a query where I needed to add several columns that stored elapsed time.
Easy enough? Sure, except that the person who designed the database stored those elapsed times as the TIME datatype.
And you can't add two time columns. Makes sense, there's no meaning to 11am + 5pm. However in this case 11am meant 11 hours and 5pm meant 17 hours. Cue multiple conversions to datetime followed by DATEPART(mi,...) followed by DATEADD followed by a cast back to TIME.
and how do they store +24 hours in that time column ? (add an extra daycounter column ??) 😉
When can we convert to Metric Time?
Metric time really isn't all that farfetched! You'd have 10 hours in a day, 100 minutes in an hour and 100 seconds in a minute. The only adjustment that needs to to be made is to make the second a little longer than the second is now.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
August 4, 2010 at 11:31 am
Brandie Tarvin (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Are you putting the parameters in the WHERE clause or in the ON statements?
I know you know this, but since you said your brain was fried... Remember that putting the parameters in the ON statements of your OUTER JOINS prevents them from becoming a "hidden" INNER JOIN.
Yup, I got that. The problem in this case is that I do need the INNER JOIN conversion because without it I'd get rows I don't want, but there are rows that would be excluded that I need.
See, I don't even understand what I wrote and I have to code it. Really an issue with business process that I can't fix.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2010 at 11:32 am
Lynn Pettis (8/4/2010)
Alvin Ramard (8/4/2010)
skcadavre (8/4/2010)
Jack Corbett (8/4/2010)
So you know the search procedure you are working on is complex when you can't write up a question about it for the forums because it takes so long to explain and hits so many tables.And yes, I'm the one trying to formulate the question. Odds are by the time I've figured out how to word the question I'll have salved the problem I'm having.
Similar thing happened to me fairly recently. Spent 2 days trying to solve a problem, then wrote up a forum post which took me 3 hours to do, during which time I solved my own problem.
Seems to be a good way to solve a problem, forcing you to look at it in a different way.
If you solve your own problem before posting the problem you should ask yourself if this is something others can benefit from. If so then consider posting both the problem and the solution.
Actually, this would probably be good material for an article. :smooooth:
Article, what's that? Blog post maybe, article if it is really good.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2010 at 11:33 am
GilaMonster (8/4/2010)
Jack Corbett (8/4/2010)
Probably the most complex SQL I've had to write. Too many OUTER JOINs (business requires this design), and then parameters that could turn the OUTER JOINs to INNER JOINs and may not be what the business wants in some cases.Swap?
I got stuck this afternoon helping out with a query where I needed to add several columns that stored elapsed time.
Easy enough? Sure, except that the person who designed the database stored those elapsed times as the TIME datatype.
And you can't add two time columns. Makes sense, there's no meaning to 11am + 5pm. However in this case 11am meant 11 hours and 5pm meant 17 hours. Cue multiple conversions to datetime followed by DATEPART(mi,...) followed by DATEADD followed by a cast back to TIME.
Sure, especially now that you've solved your problem.:w00t:
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2010 at 11:39 am
Jack Corbett (8/4/2010)
The problem in this case is that I do need the INNER JOIN conversion because without it I'd get rows I don't want, but there are rows that would be excluded that I need.
Stupid question. Can you do the OUTER JOIN code, wrapped in a CTE, then filter out the rows you don't want with an additional WHERE clause in the CTE SELECT?
I'll shut up now, so you can actually solve your problem instead of telling me that you've already tried all my suggestions. @=)
Viewing 15 posts - 17,071 through 17,085 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply