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 Thursday, March 18, 2010 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:45 PM
Points: 42, Visits: 430
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
Post #885361
Posted Thursday, March 18, 2010 4:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 22, 2014 3:45 PM
Points: 42, Visits: 430
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
Post #885370
Posted Thursday, March 18, 2010 11:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 19, 2010 3:23 AM
Points: 5, 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
Post #885756
Posted Thursday, March 18, 2010 7:26 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 35,808, Visits: 32,482
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."

(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 #886015
Posted Thursday, March 18, 2010 7:32 PM
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 honestly didn't think he was being sarcastic.
Post #886018
Posted Thursday, March 18, 2010 7:42 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 35,808, Visits: 32,482
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."

(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 #886020
Posted Thursday, March 18, 2010 7:50 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 35,808, Visits: 32,482
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."

(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 #886023
Posted Thursday, March 18, 2010 7:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 35,808, Visits: 32,482
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."

(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 #886025
Posted Monday, March 22, 2010 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #887299
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse