5 stinking hours!
Posted 01-14-2009 at 08:07 PM by PandaNin
5 stinking hours I spent on this query today.
I skipped lunch. That's how involved I got on this thing.
Now let's get technical. I'm the Vice President of Information Systems at our company and while I don't usually get into the details sometimes I do (glad I got some skillz). Well, today I had a pretty hefty query that needed to be written and everyone else is swamped so I took it on. I was thinking, 20 mins tops. I've been working with SQL Server the last 15 years or so and I know what I'm doing most of the time. I'm no newbie.
Little did I realize, 20,000 when stored in a varchar field will return isnumeric() = 1, however it will NOT sum()!!!!!
Yeah, 20 comma 000. Someone entered that as a value in a field. Talk about difficult to find. Buried deep in a few million rows of data there it was. Easy to fix, just do a replace(fieldname,',','') and you're good to go. But I didn't think about looking for the comma. Then BAM, someone else entered $15,000.00. Oh great, now gotta replace on the $ too. BTW, SQL thinks it's still numeric. They be wrong.
And to top it all off, I didn't bring the bike cause I thought it was going to be too cold today. So I don't even get any stress relief on the drive home. bummer
On another note, tomorrow is my annual review. Cross fingers everyone. It's hard when your review is given by the CEO. They tend to grade rather harshly.
I skipped lunch. That's how involved I got on this thing.
Now let's get technical. I'm the Vice President of Information Systems at our company and while I don't usually get into the details sometimes I do (glad I got some skillz). Well, today I had a pretty hefty query that needed to be written and everyone else is swamped so I took it on. I was thinking, 20 mins tops. I've been working with SQL Server the last 15 years or so and I know what I'm doing most of the time. I'm no newbie.
Little did I realize, 20,000 when stored in a varchar field will return isnumeric() = 1, however it will NOT sum()!!!!!
Yeah, 20 comma 000. Someone entered that as a value in a field. Talk about difficult to find. Buried deep in a few million rows of data there it was. Easy to fix, just do a replace(fieldname,',','') and you're good to go. But I didn't think about looking for the comma. Then BAM, someone else entered $15,000.00. Oh great, now gotta replace on the $ too. BTW, SQL thinks it's still numeric. They be wrong.
And to top it all off, I didn't bring the bike cause I thought it was going to be too cold today. So I don't even get any stress relief on the drive home. bummer
On another note, tomorrow is my annual review. Cross fingers everyone. It's hard when your review is given by the CEO. They tend to grade rather harshly.
Total Comments 0
