March 17, 2010 at 9:44 am
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.
March 17, 2010 at 10:40 am
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:
March 17, 2010 at 10:52 am
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/
March 17, 2010 at 11:52 am
whug (3/17/2010)
I would have refused the work. ...
The art of saying NO:
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 12:20 pm
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:
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.
March 17, 2010 at 12:25 pm
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.
March 17, 2010 at 12:51 pm
jacroberts (3/17/2010)
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.
So when is your article going to be published?
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
March 17, 2010 at 12:59 pm
jacroberts (3/17/2010)
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.
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.
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
March 17, 2010 at 1:53 pm
rob mcnicol (3/17/2010)
'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'š
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) š
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 17, 2010 at 4:09 pm
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.
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
March 17, 2010 at 5:51 pm
jacroberts (3/17/2010)
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.
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.
.
March 17, 2010 at 6:29 pm
CirquedeSQLeil (3/17/2010)
jacroberts (3/17/2010)
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.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.
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.
Tim Walker. (3/17/2010)
jacroberts (3/17/2010)
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.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.
I agree Bob Hovious's article is excellent, well laid out and well explained.
March 17, 2010 at 9:44 pm
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.
The ReportDay = 29 and in 2009 there were only 28 days in February. You fixed this value:
It's just not up to you to "fix" data simply because you don't 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...
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.
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... do it right all the time! Data isn't the only thing that's supposed to have some integrity to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2010 at 3:28 am
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
.
March 18, 2010 at 4:24 am
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:
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply