Click here to monitor SSC
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14363 Visits: 9729
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
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 175
ok thanks for the reply Smile
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35873 Visits: 18715
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: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21017 Visits: 18258
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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 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
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 394
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
SSC Eights!
SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)SSC Eights! (800 reputation)

Group: General Forum Members
Points: 800 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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21017 Visits: 18258
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3248 Visits: 11771
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