SQLServerCentral / Discuss Content Posted by Yakov Shlafman / Article Discussions / Article Discussions by Author / An Urgent Ad Hoc Report / Latest Posts

RE: An Urgent Ad Hoc Report

GSquared — Mon, 22 Mar 2010 06:07:58 GMT

I have to agree with Jeff on the point of not changing data in a production system without external validation.I've run into that exact situation, a 29 Feb in a non-leap year. I handled it by having an exceptions section in the report, and let the managers and personnel responsible verify the date and handle it appropriately. Turned out it was supposed to be March 29th, not Feb 29th. Made a difference, albeit a minor one. (Nothing so dramatic as Jeff's criminal investigation.) But the thing is, it [i]could[/i] have made a big difference, and I had no way of knowing whether it would or not.

RE: An Urgent Ad Hoc Report

Jeff Moden — Thu, 18 Mar 2010 19:57:34 GMT

Yakov,If you weren't being sarcastic, I truly apologize for me taking it the wrong way. I do have to stand my ground on the issue, though, and I do hope you can understand that.

RE: An Urgent Ad Hoc Report

Jeff Moden — Thu, 18 Mar 2010 19:50:33 GMT

[quote][b]Tim Walker. (3/18/2010)[/b][hr]Jeff, that is such an important point that it's a shame its buried here in response to the article posted here. It really warrants an article in its own right because I reckon lots of people with database responsibilities would fall headlong into this trap without even realising it is a trap!Your point on professionalism is also well made, because in reality it may well be hard to spot that a data change had been applied (perhaps weeks ago) and who had done it.This doesn't change the fact that data integrity is compromised, which is a shame bearing in mind the many technical ways SQL Server preserves it's internal data integrity. As usual, the Human Element is the weakest link.Tim[/quote]Considering how many shops no longer trust DBA's and actually try to make it so DBA's can't even read data never mind fix things because they no longer trust people not to be the weakest link in data integrity, I'm thinking that such an article is long overdue. Thanks for the feedback, Tim.

RE: An Urgent Ad Hoc Report

Jeff Moden — Thu, 18 Mar 2010 19:42:03 GMT

[quote][b]GOC-481399 (3/18/2010)[/b][hr]I honestly didn't think he was being sarcastic.[/quote]You could be right. I may have taken it all wrong because of the sensitive subject matter and the fact that he capitalized the word "SMART". If so, I apoligize for saying he was sarcastic.

RE: An Urgent Ad Hoc Report

GOC-481399 — Thu, 18 Mar 2010 19:32:41 GMT

I honestly didn't think he was being sarcastic.

RE: An Urgent Ad Hoc Report

Jeff Moden — Thu, 18 Mar 2010 19:26:37 GMT

[quote][b]Yakov Shlafman (3/18/2010)[/b][hr]SSChampion, why we do not question the fact that in production environment we have garbage - garbage database design, garbage data, garbage applications that produce garbage results.Before that garbage went into production customers signed it, QA signed, Developers and DBA got paidfat bonuses. And here I am changing 29 by 28.But I do agree with everything you said and our emotions should not always drive us.Thanks for your comments. Again there are so many issues (not always technical)I would like to discuss and hear others opinion especially from SMART people like you.Regards[/quote]I absolutely agree with that. Why [font="Arial Black"][i]DOES [/i][/font]production data have garbage in it!!!! And I also agree that the bums who designed a system to allow such garbage shouldn't have ever received a paycheck never mind a fat bonus.However...I think you're being sarcastic when you say that you like to "hear others opinion especially from SMART people like you." Let us truly consider why the database has garbage in it. Could it be that a bunch of people with the same attitude (or lack of) towards the sanctity of the data as what you stated in your article wrote the database?There's another aspect to some of these "urgent ad hoc reports" that people don't consider and that I've been privy to a couple of times now. You flat out don't know what the report is for. In fact, your manager may not know what the report is for. (S)he may have been directed to have his/her people produce the report and it could be for something like a police investigation to determine who possible suspects are based on their last charge date. Your willy nilly and thoughtless change may have either put an innocent person in harms way or may have kept a guilty person from being considered because it may have been the year or the month that was in error and not the day. You should have reported the discrepancy instead of taking matters into your own hands.If you're going to be a data professional, then be professional even if you think you're an underpaid professional. Like I said, data isn't the only thing that's supposed to have integrity.And drop the bit about emotions driving this... I'm not the one that decided to get sarcastic here.

RE: An Urgent Ad Hoc Report

ccoker-1050064 — Thu, 18 Mar 2010 11:08:59 GMT

When given a report with an incomplete specification and/or garbage data, and there is not enough time to verify everything, the trick is to document every gosh-darned assumption that you have to make. You can send the report attached to an email that lists in plain language all 20 of your assumptions (Feb 29 handling, using the max expense for a date, etc.). If the consumer of the report disagrees with any of your assumptions, then you can always redo the report but at least they know what they are getting and you can say that you responded quickly.Having said all that, thanks for the article. It really helps to see how someone else's thought process works. Then you can pick out an approach here or there that perhaps you should try yourself when the opportunity arises.Curt Coker

RE: An Urgent Ad Hoc Report

Yakov Shlafman — Thu, 18 Mar 2010 04:58:00 GMT

Jeff Moden, why we do not question the fact that in production environment we have garbage - garbage database design, garbage data, garbage applications that produce garbage results.Before that garbage went into production customers signed it, QA signed, Developers and DBA got paidfat bonuses. And here I am changing 29 by 28.But I do agree with everything you said and our emotions should not always drive us.Thanks for your comments. Again there are so many issues (not always technical)I would like to discuss and hear others opinion especially from SMART people like you.And I mean SMART based on your writing. ThanksRegards

RE: An Urgent Ad Hoc Report

Yakov Shlafman — Thu, 18 Mar 2010 04:47:27 GMT

jon-688305, thank you very much for you input. I am sorry if I did not stressed enough that the data in the tables is not correct/valid for multiple rows and I ignored the fact that for these rows Quater/ReportMonth relationship is invalid. The provided solution does produce valid result setbased on Year/Month/Day.I do use CROSS APPLY and Outer Apply operators a lot.ThanksRegards

RE: An Urgent Ad Hoc Report

GOC-481399 — Thu, 18 Mar 2010 04:24:20 GMT

I do agree wtih Jeff Moden's sentiments. Don't arbitrarily change anything unless you're prepared to carry the can for it down the track, because you will. So many bosses are mixture of psychopath and weazel. Do you think a boss like that will put their hand up and say they authorised or even condoned your judgement calls? Having said that, I have worked for one such psychpath weazel and it was routine to get a ridiculously ambiguous but terribly urgent request at 15 mins to beer. It's hard. It's real hard, to go back to them and "quibble" over detail they care not for, but which will materially affect the outcome of the query. If you can't debate every point with your boss (because they're liable to pull your spine out through your foreign key), then at least clearly document any assumptions you made and why you made them. And make sure you include your TSQL as a technical appendix to any ad hoc report you produce - even if you're asked not to. Someone else further up the food chain can strip it out if they want to, but at least you know that the data AND the methodolgy were intact when they left your hands.Oh... and comment your code professionally no matter how frustrated you are with the stupid reactionary request you've just received. The weazel psychopath might read it one day. Hope (s)he's not read this.:Whistling:

RE: An Urgent Ad Hoc Report

Tim Walker. — Thu, 18 Mar 2010 03:28:52 GMT

Jeff, that is such an important point that it's a shame its buried here in response to the article posted here. It really warrants an article in its own right because I reckon lots of people with database responsibilities would fall headlong into this trap without even realising it is a trap!Your point on professionalism is also well made, because in reality it may well be hard to spot that a data change had been applied (perhaps weeks ago) and who had done it.This doesn't change the fact that data integrity is compromised, which is a shame bearing in mind the many technical ways SQL Server preserves it's internal data integrity. As usual, the Human Element is the weakest link.Tim

RE: An Urgent Ad Hoc Report

Jeff Moden — Wed, 17 Mar 2010 21:44:28 GMT

I very much appreciate this article as we've all had a similar problem at one time or another and they always seem to pop up when you have an after work appointment.I would, however, like to caution folks that may read it because there are a couple of things that were done that, in my eyes as a professional "data handler", are grounds for instant termination of employment.[quote]The ReportDay = 29 and in 2009 there were only 28 days in February. You fixed this value: [/quote]It's just not up to you to "fix" data simply because you [b][i]don't[/i][/b] know what the data should actually be. For example, Yakov points out that a bad date was found because there is no Feb 29th of 2009 because 2009 wasn't a Leap Year. Yakov fixed that by changing the 29 to a 28 and everything is hunky dory, right? Wrong! What if it was the year that was fat fingered and the year should have been 2008 or the month should have been a 1 or a 3, instead? You don't know for sure and you must not make such a presumption.Never ever make such a presumption... just list it as an exception on the report. It's not up to you to make what could be an incorrect decision. While you're at it, I believe I'd also take the small bit of time to write down the fact that, considering the adjacent data, it's not likely that Feb 2009 actually occurred in the 3rd "Quater". It takes just 2 seconds to demonstrate that you're concerned about the data enough to help the company that's making it possible for you to afford the home you so desperately want to get to. ;-)On to the next problem...[quote]You review your query and decide that if multiple expenses where entered the same date (Tiebreaker) the max expense becomes the last one and it has max(RowId) for each contractor. You modify your query and it produce a correct result. [/quote]Who gave you the authority to make such a decision? Just because it's an "ad hoc" report doesn't justify such a poor decision. The first thing you should do on the very first day you report to work (or, hopefully during the interview) is to get your manager's telephone number. Certainly, if your manager gives you such an "urgent" request and you don't have your manager's telephone number, take out 5 seconds to find out what it is. Your manager gave you the urgent request because (s)he trusts you to not only get the job done, but to contact them if ANYTHING goes wrong. Why are you violating that trust? Your manager may not know the data but, upon hearing about it, may actually want to see ALL the charges on that last day for each contactor.While I appreciate the fact that we all have "real" lives to live and outside appointments to keep, there are certain shortcuts that you simply must not take no matter how urgent the in-house request is nor how urgent your outside appointment is. You're being paid good money to be a professional and professionals don't ever take such shortcuts nor make such willy-nilly decisions about the data. 2 very bad decisions were made in the process of solving this urgent request and the manager should have been contacted immediately for both.There's a huge difference between getting the urgent job done in time and getting the job right. One of those differences may be whether you have a job to come to tomorrow because the two infractions I've cited above have set the manager up for potential failure and that you can't actually be trusted with data. ;-)Slow down... be professional... [b][i]do it right all the time[/i][/b]! Data isn't the only thing that's supposed to have some integrity to it.

RE: An Urgent Ad Hoc Report

Jonathan AC Roberts — Wed, 17 Mar 2010 18:29:57 GMT

[quote][b]CirquedeSQLeil (3/17/2010)[/b][hr][quote][b]jacroberts (3/17/2010)[/b][hr]This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.[/quote]Please provide specifics if you are going to criticize the article. By being specific then people can learn from the criticism, otherwise it is pointless to criticize.[/quote]The article looked like the author had just pasted in the contents of his SQL Server Management Studio editor into the article after doing a task for his boss. Other than giving him quick worldwide access to the source code just in case he is asked to repeat the task at a later date I see no point in it.[quote][b]Tim Walker. (3/17/2010)[/b][hr][quote][b]jacroberts (3/17/2010)[/b][hr]This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.[/quote]Do you just mean it is long?I agree some of the content is wordy but I think that's the point of the article - these things take a while to do and here is how it was done!It's not a 'how to do' article - Bob Hovious did a good job of this the other day.[/quote]I agree Bob Hovious's article is excellent, well laid out and well explained.

RE: An Urgent Ad Hoc Report

Tim Walker. — Wed, 17 Mar 2010 17:51:25 GMT

[quote][b]jacroberts (3/17/2010)[/b][hr]This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.[/quote]Do you just mean it is long?I agree some of the content is wordy but I think that's the point of the article - these things take a while to do and here is how it was done!It's not a 'how to do' article - Bob Hovious did a good job of this the other day.

RE: An Urgent Ad Hoc Report

jon-688305 — Wed, 17 Mar 2010 16:09:47 GMT

Your article is an interesting insight as to how you solved the problem and how to use CTEs. However, I’m not convinced it produces the correct results, given your data. Your final query returns the result for R & P suns with the expenses date of 28th Feb 2009, whereas your data has an entry for the 10th July 2009. Surely this is the row that should be returned? This is due to the data in the Quarter column not corresponding to the ReportMonth. In fact the data seems a bit dodgy, so maybe it is just your test data.Assuming the Quater and ReportWeek are irrelevant in the query and the FinancialYear, ReportMonth and ReportDay can give you the date of the expense, then I think that the following query using a CROSS APPLY is simpler to understand than using a CTE IMHO.[code="sql"]SELECT c.ContractorName AS 'Contractor Name' , CONVERT(VARCHAR(15),e.ExpenseDate, 101) AS 'Last Expense Date' , '$' + CONVERT(VARCHAR(20),e.Expense,1) AS 'Expense' FROM tmpContractor AS c CROSS APPLY (SELECT TOP 1 se.ContractorId , se.Expense , DATEADD(yy, (se.FiscalYear - 1900),DATEADD(mm, se.ReportMonth - 1,DATEADD(dd, se.ReportDay -1,0))) AS 'ExpenseDate' FROM tmpBusinessExpense AS se WHERE se.ContractorID = c.ContractorID ORDER BY DATEADD(yy, (se.FiscalYear - 1900),DATEADD(mm, se.ReportMonth - 1,DATEADD(dd, se.ReportDay -1,0))) DESC ) AS e ORDER BY 1[/code]

RE: An Urgent Ad Hoc Report

Jason-299789 — Wed, 17 Mar 2010 13:53:25 GMT

[quote][b]rob mcnicol (3/17/2010)[/b][hr]'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':-P[/quote]Its a great article but the quote above is probably the more common response your boss will get the world over. nothing is that desperate that after 4pm cant wait until the next morning (unless its a ded server, in which case its an engineers problem) ;)

RE: An Urgent Ad Hoc Report

SQLRNNR — Wed, 17 Mar 2010 12:59:48 GMT

[quote][b]jacroberts (3/17/2010)[/b][hr]This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.[/quote]Please provide specifics if you are going to criticize the article. By being specific then people can learn from the criticism, otherwise it is pointless to criticize.

RE: An Urgent Ad Hoc Report

Trey Staker — Wed, 17 Mar 2010 12:51:02 GMT

[quote][b]jacroberts (3/17/2010)[/b][hr]This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.[/quote]So when is your article going to be published?

RE: An Urgent Ad Hoc Report

Jonathan AC Roberts — Wed, 17 Mar 2010 12:25:08 GMT

This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.

RE: An Urgent Ad Hoc Report

Michael Valentine Jones — Wed, 17 Mar 2010 12:20:37 GMT

[quote][b]CirquedeSQLeil (3/17/2010)[/b][hr][quote][b]whug (3/17/2010)[/b][hr]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[/quote]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.[/quote]You can turn the "no requirements" to your advantage. Just send an email requesting answers to about twenty questions and head for the door.

RE: An Urgent Ad Hoc Report

SQLRNNR — Wed, 17 Mar 2010 11:52:42 GMT

[quote][b]whug (3/17/2010)[/b][hr]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[/quote]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.

RE: An Urgent Ad Hoc Report

timothyawiseman — Wed, 17 Mar 2010 10:52:58 GMT

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.

RE: An Urgent Ad Hoc Report

WHug — Wed, 17 Mar 2010 10:40:37 GMT

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

RE: An Urgent Ad Hoc Report

yakov shlafman-228008 — Wed, 17 Mar 2010 09:44:11 GMT

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.

RE: An Urgent Ad Hoc Report

Trey Staker — Wed, 17 Mar 2010 09:32:45 GMT

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.

RE: An Urgent Ad Hoc Report

SQLRNNR — Wed, 17 Mar 2010 09:27:23 GMT

[quote][b]Steve Jones - Editor (3/17/2010)[/b][hr]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.[/quote]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.

RE: An Urgent Ad Hoc Report

Steve Jones - SSC Editor — Wed, 17 Mar 2010 09:09:37 GMT

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.

RE: An Urgent Ad Hoc Report

l543123 — Wed, 17 Mar 2010 08:55:58 GMT

ok thanks for the reply :)

RE: An Urgent Ad Hoc Report

GSquared — Wed, 17 Mar 2010 08:53:13 GMT

[quote][b]l543123 (3/17/2010)[/b][hr][i]"First of all, you run a script to find Contractor and Expense tables across all databases on you production server"[/i]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"[/quote]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.

RE: An Urgent Ad Hoc Report

yakov shlafman-228008 — Wed, 17 Mar 2010 08:50:41 GMT

Thanks SSC Rookie for your input.I do describe a real environment. When I got a request like thisand 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.:-)

RE: An Urgent Ad Hoc Report

l543123 — Wed, 17 Mar 2010 08:09:54 GMT

[i]"First of all, you run a script to find Contractor and Expense tables across all databases on you production server"[/i]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"

RE: An Urgent Ad Hoc Report

Brett Berger — Wed, 17 Mar 2010 06:44:07 GMT

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, BrettDeclare @x intSet @x = 1Select Right('00' + Convert(varchar(2), @x), 2)Set @x = 12Select Right('00' + Convert(varchar(2), @x), 2)

RE: An Urgent Ad Hoc Report

murraydan885 — Wed, 17 Mar 2010 05:16:47 GMT

Ad Hoc ReportAlternatively 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.

RE: An Urgent Ad Hoc Report

irozenberg — Wed, 17 Mar 2010 04:19:53 GMT

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. :-P SPASIBO

RE: An Urgent Ad Hoc Report

roachw — Wed, 17 Mar 2010 03:11:54 GMT

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

RE: An Urgent Ad Hoc Report

rob mcnicol — Wed, 17 Mar 2010 00:18:20 GMT

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':-P

RE: An Urgent Ad Hoc Report

SQLRNNR — Wed, 17 Mar 2010 00:09:34 GMT

Thanks for the article Yakov.

RE: An Urgent Ad Hoc Report

qld_dba — Tue, 16 Mar 2010 23:59:11 GMT

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.

An Urgent Ad Hoc Report

Yakov Shlafman — Tue, 16 Mar 2010 22:22:00 GMT

Comments posted to this topic are about the item [B]An Urgent Ad Hoc Report[/B]