SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Urgent Ad Hoc Report


An Urgent Ad Hoc Report

Author
Message
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30233 Visits: 9730
l543123 (3/17/2010)
"First of all, you run a script to find Contractor and Expense tables across all databases on you production server"

sorry if I am wrong, but why are you searching all databases and if you dont know the tables then what are you searching :-)

also I think its a lot of code for something like,
select contractor name, max of expense and date from contractor
inner join expense on expense.contract_id = contrator.id"



That won't work. What if the highest expense is on a different date than the one you want? Max gives the highest value in the column for the rows defined in the Group By clause.

- 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
l543123
l543123
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 175
ok thanks for the reply Smile
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81963 Visits: 19217
It's easy if you control all the tables. What if you're being asked for a report from something like JD Edwards OneWorld, which is a large ERP package. The tables are named "A7230" and "E23"

Good luck figuring out the database diagram for that one. I've had to go through it and it's a mess.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40377 Visits: 18565
Steve Jones - Editor (3/17/2010)
It's easy if you control all the tables. What if you're being asked for a report from something like JD Edwards OneWorld, which is a large ERP package. The tables are named "A7230" and "E23"

Good luck figuring out the database diagram for that one. I've had to go through it and it's a mess.


Yuck - I hate supporting those kinds of databases. Not a fan of that kind of design and it is no fun trying to create any query from them in an efficient manner.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1376 Visits: 2788
Thanks for the article. It brought back a lot of memories of times when I've had such request.

I agree with Steve's comments. Most of the time when I've been asked to run these types of query with this little notice it is for an urgent business need and usually from a 3rd party database like of of the ones he listed.

I've also had request like this happen to data platforms that I have limited experience in like mysql. Sometimes it is to verify something that a business analyst is saying to an Executive. Sometimes they go to the DBA because of a relationship of trust that has been built up over time and they need a 2nd opinion.

The only caution here is that if they keep comming to the DBA for AD-hoc queries they stop relying on the B/As and your job could turn from being a DBA to being a Hybrid DBA/analyst. Knowing how and when to say no is a learned skill. If you always just say no, which a lot of DBAs do, then there may be missed opportunities for you and for the company. If you say yes too often then they'll come to you every day at 4pm and you'll be working past midnight. IMHO.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
yakov shlafman-228008
yakov shlafman-228008
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 281
Thanks SSC Veteran.
I think you have a really good idea for an article - when to say Yes and when to say No. This is an ART.
WHug
WHug
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 412
I like the story, its a good example of how we SQL junkies get it done.

I would have refused the work. Almost every time I have had to crank out a last minute ad-hoc, it has been wrong somehow, usually due to a data issue. Sometimes the Business Owner can help you QA the data, but that still isn't going to be right by 5pm. Usually the supervisor/manager doesn't have a real grasp on what the BO wants because he doesn't know the data either so the bulk of your effort is spent running the wrong direction. The only situation where this works is when a data warehouse exists where you can trust that what you are putting out is accurate.

Saying NO directly could get me canned, so my approach is to go directly to the requester (I don't care what their label is) and discuss it directly. At least then if I have to run around with my head cut off it is in the right direction, and the BO and I can come to an understanding of how correct the data will be and how long it will take to get done. I can update my manager and trust is built all-around.

The art of saying NO:
http://www.impactfactory.com/gate/assertiveness_skills_training_saying_no_too_nice/fungate_1741-4102-18850.html



timothyawiseman
timothyawiseman
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 920
This was a rather interesting article. But it made me think of two things I think are worth noting.

First, when dealing with complicated queries over large datasets that do not have indexes, it is often faster to create the indexes you need then it is to run the query without the indexes, not to mention that the indexes will then be available for use in the future. This is not universal of course, but I have very frequently found it to be the case.

Next, it struck me as strange that they banned the use of cursors and recursion for an ad hoc query. Of course I recommend avoiding them in all cases where it is practical, but when you say that something is ad hoc and will be run precisely once and you are on a tight timeline so developer time is likely much more valuable than processor time that is one case where it might be worth using one. Even there, I would look for a non-cursor non-recursion solution first, but if I could generate a solution with a cursor faster than I could a truly set based solution then it may be worth considering in a situation like this.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40377 Visits: 18565
whug (3/17/2010)
I like the story, its a good example of how we SQL junkies get it done.

I would have refused the work. ...
The art of saying NO:
http://www.impactfactory.com/gate/assertiveness_skills_training_saying_no_too_nice/fungate_1741-4102-18850.html



I would have done the same thing. After a certain time in the day, with no requirements, no thought out process - it's just a recipe to set you up for disaster.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7488 Visits: 11793
CirquedeSQLeil (3/17/2010)
whug (3/17/2010)
I like the story, its a good example of how we SQL junkies get it done.

I would have refused the work. ...
The art of saying NO:
http://www.impactfactory.com/gate/assertiveness_skills_training_saying_no_too_nice/fungate_1741-4102-18850.html



I would have done the same thing. After a certain time in the day, with no requirements, no thought out process - it's just a recipe to set you up for disaster.


You can turn the "no requirements" to your advantage. Just send an email requesting answers to about twenty questions and head for the door.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search