An Inspiring Story Of A Man And A Car Wash

I wanted to share with you a great story I read on Quora recently:

Why working in a car-wash (at age 44) was the best thing that could have happened to me

This really spoke too me, because in our modern economy we are always looking to take the next step forward, or the next step up the ladder, and if we’re not doing so quickly enough we look to pretend we are still heading in the right direction. Struggling to admit defeat, ask for help or start again from the beginning.

So this article by Micheal Aumock, was really refreshing to read. His attitude change from simply showing up to a job he disliked and wallowing in self-pity, to trying to be the best in his role, is the exact attitude I am trying to develop.

I may not be the best blogger out there. My opinions at the time of writing may prove to be incorrect, or I may misinterpret the intentions of another writer. But isn’t that how we grow?

If you have a spare 5 minutes I would strongly recommend having a read of Michael’s story.

SQL–Excluding zero value records

A challenge I was tasked with recently was providing an SSRS report, where by a user could choose to exclude rows from a table if a particular quantity column contained a zero value.

Originally this coloured me perplexed, until I got my head around the logic required in my where clause which feeds into the data source of my SSRS report.

So for my own future reference and on the off chance any one else faces a similar problem, here is a snippet of the code I used:

SELECT          ItemNumber
                       ,ItemDescription
                       ,PlannerCode
                       ,PurchasedYTD
                       ,SoldYTD
                       ,UsedYTD
FROM          Orders
WHERE        ((@ExcludePur = 1 AND PurchasedYTD <> 0) OR (@ExcludePur = 0) )
                      AND ((@ExcludeSold = 1 AND SoldYTD <> 0) OR (@ExcludeSold = 0) )
                      AND ((@ExcludeUsed = 1 AND UsedYTD <> 0) OR (@ExcludeUsed = 0) )
ORDER BY   ItemNumber

All three parameters have to passed a value, and are not optional, so in this case we can use an “AND” clause to compare all three filters, and then use an “OR sub clause to exclude zero value records if the relevant parameter =1. Otherwise if the parameter = 0 we’ll not limit the result set.

(Apologies for the dodgy formatting, I am still finding my feet with my new blog writing tool)

SQL–Function To Remove Non-numeric Characters

Such a simple task in hindsight, but if you’re scratching your head about how to remove non-numeric characters from a string, this function should help you.

The function uses the in built STUFF and PATINDEX functions to identify a characters which aren’t numeric (0-9) and replace each non-numeric character with an empty string (‘’).

CREATE Function [xFnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

SQL and the ALT+Shift shortcut

Something i’ve come across recently is the number of developers out there who don’t know about one of my favourite SQL Server management studio(SSMS) shortcuts. The ALT+Shift command to select an area of onscreen real estate.

This shortcut allows you to select a cross section of lines and rows in in SSMS and add/remove text. This is very good when you want to add something simple like a comma, or a table alias to the beginning of a number of columns. In the example below, we’ll simply add the typename function to a couple of ID’s, in reality this isn’t going to save much time but just imagine the scenarios were you’ve repetitively copied and pasted the same 2-3 letters, line after line.

My starting point:

From here I want to position my cursor between the comma and the ‘S’ on line three. Next I simply hold ALT+Shift and then press down.

After doing this I can simply start typing, and should see my typed letters appearing on both lines:

Once that is done, I simply need to go to the end of each line and add my closing bracket.

Finally, in the last screenshot I am simply showing how I can select a range of characters over multiple lines and either delete that which is constraint to my select box or replace it with a set of alternative values.