An Urgent Ad Hoc Report

Question

Post reply

An Urgent Ad Hoc Report

Yakov Shlafman

SSCommitted

Points: 1613
More actions
March 16, 2010 at 10:22 pm

#219999

Comments posted to this topic are about the item An Urgent Ad Hoc Report

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

QLD_dba SSC Enthusiast Points: 150 More actions · Answer 1

Well done Yakov.

This is an excellent example of what our everyday work load is like.

Are you listening newbies? And even better the solution is a great example of the use of 2005 CTE features.

5 stars to you Yakov.;-)

Regards, Greg.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Thanks for the article Yakov.

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

rob mcnicol SSC Eights! Points: 834 More actions · Answer 3

great article - thanks heaps.

oh, might it benefit from the addition of this little tweak near the start?

'you tell your boss he's off his rocker for suggesting that you work past your usual time but you will happily do the report next morning on receipt of a box of chocolates and a nice bunch of flowers by way of apology'

😛

roachw Valued Member Points: 71 More actions · Answer 4

I must be missing somthing here, I'm no SQL expert (in fact I'd never come accross CTE's before) but that seems like an awfull lot of code for what, on the face of it, seems like a pretty straight forward report. There's reams of it man!! I'd have been there till midnight producing that lot 🙂 Not only would I have missed dropping the kids off I would have missed picking em up too. Might look a bit closer at the CTE's though...

irozenberg SSC-Addicted Points: 478 More actions · Answer 5

Thanks for article, Yakov.

Would not usually bother with sample data in a sandbox environment - usually would copy entire tables and create views with 1000 rows,

this would prevent NASTY surprises later on. Subject to having access to PRODUCTION environment and be allowed to copy data across.

If you do not have either - politely explain you manager that you did everything you COULD tonight and he will get his urgent report first thing in the morning. 😛

SPASIBO

murraydan885 SSC Journeyman Points: 77 More actions · Answer 6

Ad Hoc Report

Alternatively you purchase Tableau Software and the whole effort requires about 60 seconds. Alternative 2, your user is able to create their own ad hoc report with Tableau.

Brett Berger SSC Enthusiast Points: 124 More actions · Answer 7

Another option for ensuring the length of the individual dateparts is to left pad the value and retain the rightmost characters, thereby eliminating the case statement. This has come in handy in various situations.

Thanks, Brett

Declare @x int

Set @x = 1

Select Right('00' + Convert(varchar(2), @x), 2)

Set @x = 12

Select Right('00' + Convert(varchar(2), @x), 2)

l543123 SSC Eights! Points: 964 More actions · Answer 8

"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"

yakov shlafman-228008 Old Hand Points: 393 More actions · Answer 9

Thanks SSC Rookie for your input.

I do describe a real environment. When I got a request like this

and I do not have any input or explanation this is my only choice.

The worst case scenario if Contractor table is not called Contractor.

Then no options until you can get table names.

🙂

GSquared SSC Guru Points: 260824 More actions · Answer 10

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 SSC Eights! Points: 964 More actions · Answer 11

l543123

SSC Eights!

Points: 964

March 17, 2010 at 8:55 am

#1135308

ok thanks for the reply 🙂

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 12

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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[/url]
Learn Extended Events

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 14

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