An Urgent Ad Hoc Report

  • Yakov Shlafman

    SSCommitted

    Points: 1613

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

  • QLD_dba

    SSC Enthusiast

    Points: 140

    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: 281243

    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

    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

    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

    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

    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

    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: 934

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

    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

    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: 934

    ok thanks for the reply 🙂

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    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: 281243

    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: 4736

    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 39 total)

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