An Urgent Ad Hoc Report

Question

Post reply

An Urgent Ad Hoc Report

Viewing 9 posts - 31 through 38 (of 38 total)

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

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 1

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 set

based on Year/Month/Day.

I do use CROSS APPLY and Outer Apply operators a lot.

Thanks

Regards

Yakov Shlafman SSCommitted Points: 1613 More actions · Answer 2

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 paid

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

Regards

ccoker-1050064 SSC Journeyman Points: 99 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 4

Yakov Shlafman (3/18/2010)
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 paid
fat 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

I absolutely agree with that. Why [font="Arial Black"]DOES [/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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GOC-481399 SSC Veteran Points: 249 More actions · Answer 5

GOC-481399

SSC Veteran

Points: 249

March 18, 2010 at 7:32 pm

#1136406

I honestly didn't think he was being sarcastic.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

GOC-481399 (3/18/2010)
I honestly didn't think he was being sarcastic.

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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

Tim Walker. (3/18/2010)
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

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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 8

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.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 9

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 could have made a big difference, and I had no way of knowing whether it would or not.

- 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