Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

An Urgent Ad Hoc Report Expand / Collapse
Author
Message
Posted Wednesday, March 17, 2010 12:25 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 5, 2014 4:27 AM
Points: 346, Visits: 1,439
This is probably one of the most unreadable stories and unreusable pieces of code I have ever seen in an article.
Post #884953
Posted Wednesday, March 17, 2010 12:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 11:00 AM
Points: 1,142, Visits: 2,693
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
Post #884971
Posted Wednesday, March 17, 2010 12:59 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #884979
Posted Wednesday, March 17, 2010 1:53 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 953, Visits: 2,626
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
Post #885015
Posted Wednesday, March 17, 2010 4:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 26, 2014 5:56 AM
Points: 31, Visits: 148
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

Post #885093
Posted Wednesday, March 17, 2010 5:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:08 AM
Points: 267, Visits: 898
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.


.
Post #885139
Posted Wednesday, March 17, 2010 6:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 5, 2014 4:27 AM
Points: 346, Visits: 1,439
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.
Post #885152
Posted Wednesday, March 17, 2010 9:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #885201
Posted Thursday, March 18, 2010 3:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:08 AM
Points: 267, Visits: 898
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


.
Post #885323
Posted Thursday, March 18, 2010 4:24 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, July 11, 2010 5:43 PM
Points: 55, Visits: 224
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.

Post #885347
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse