TSQL : Convert a String to Pad Leading Zeros

Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front […]

TSQL : Convert DateTime String to Datetime

You have imported data from a text file into your database which included datetime values formatted as strings. An example is the following string ‘2013-09-12 01:03:52’ which represents the date 12th September 2013 at 3 minutes past 1am on the 52nd second. The following snippet example show how to convert this string to a datetime […]

MDX : Percentage of Total and SSRS

A simple Percentage of Total Calculation example. If you are bringing your MDX dataset into SSRS remember SSRS doesn’t recognise MDX percentage values formatted with FORMAT_STRING=”Percent” – so you’ll need to specify the percentage format in your SSRS chart or tablix.

MDX : Ordering by Month in your SSRS Reports

You want your months to display in your SSRS report as Jan, Feb, Mar etc yet you run the report and the ordering is all out of sorts. Adapt the following MDX sampler, to grab the month key, and follow the steps subsequent to set the ordering in your SSRS chart. The month key will […]

TSQL : Ranking records according to the latest date

One frequently occurring scenario I come across is a bunch of records with the same identifiers but each row has a different datestamp – then I need to identify the latest record in each group. This is the end result of what needs to be achieved, I want a ranking for each record in the […]

TSQL : Convert a Range to Rows in a View

You have a table that contains rows identifying start and end values for a range and you want to convert this to rows i.e. one row for every number between the start of the range and the end of the range. You also need to do it in a view. An example is : Id […]

TSQL: Reset Table Identity Value with Reseed

The following example reseeds the identity value back to 1 for the table [schema]/[tablename]…this is useful for example after executing a truncate table where you need to reload the table from identity value 1 again :