An Urgent Ad Hoc Report

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

  • 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.

  • 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

  • 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'

    😛

  • 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...

  • 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

  • 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.

  • 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)

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

  • 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.

    🙂

  • 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

  • ok thanks for the reply 🙂

  • 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.

  • 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

  • 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

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

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