Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

An Urgent Ad Hoc Report Expand / Collapse
Author
Message
Posted Wednesday, March 17, 2010 8:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #884748
Posted Wednesday, March 17, 2010 8:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 11:34 AM
Points: 42, Visits: 174
ok thanks for the reply :)
Post #884755
Posted Wednesday, March 17, 2010 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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
Post #884778
Posted Wednesday, March 17, 2010 9:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #884806
Posted Wednesday, March 17, 2010 9:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:46 PM
Points: 1,380, Visits: 2,684
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
Post #884814
Posted Wednesday, March 17, 2010 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 5, 2010 2:07 PM
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.
Post #884825
Posted Wednesday, March 17, 2010 10:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:07 PM
Points: 30, Visits: 296
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



Post #884866
Posted Wednesday, March 17, 2010 10:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #884878
Posted Wednesday, March 17, 2010 11:52 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #884929
Posted Wednesday, March 17, 2010 12:20 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 3,135, Visits: 11,482
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.

Post #884947
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse