SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Urgent Ad Hoc Report


An Urgent Ad Hoc Report

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 516
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
Yakov Shlafman
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 516
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
ccoker-1050064
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 31
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
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 DOES 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GOC-481399
GOC-481399
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 224
I honestly didn't think he was being sarcastic.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23451 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search