Category Archives: Models

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 of the raw product codes. e.g. the following table shows 3 different examples demonstrating how the different lengths of data in the raw code column require more or less leading zeros (maximum required code length of 4 characters) for the Required Code column :

Raw Code Required Code Number of Leading Zeros to Pad
3 0003 3
187 0187 1
5455 5455 0

The following snippet demonstrates how Jose managed a year’s supply of free Fajitas by padding leading zeros in front of the product codes using the REPLICATE function :

SELECT
REPLICATE('0', 4-LEN(a.[Raw Code])) + CAST(a.[Raw Code] as varchar(4)) as [Required Code]
FROM dbo.table;

TSQL : Convert a Year to a Datetime

Quick snippet to generate a datetime value where you only have a year to work with. Creates a datetime value set to the 1st day of the year.

DECLARE @Year int
SET @Year = 2010
SELECT CONVERT(datetime, CONVERT(varchar(8),@Year*10000+101))

This compilation of date possibilities is also really cool – though not as cool as Dan Aykroyd…
http://www.sqlusa.com/bestpractices/datetimeconversion/

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 value :

DECLARE @string varchar(24), @date date, @time time
SET @string = '2013-09-12 01:03:52'
SELECT REPLACE(LEFT(@string,10),'-','')
SELECT CONVERT(datetime,REPLACE(LEFT(@string,10),'-','')) as DateComponent
SELECT CAST(SUBSTRING(@string,12,9) as time) AS TimeComponent
SELECT CONVERT(datetime,REPLACE(LEFT(@string,10),'-','')) + CAST(SUBSTRING(@string,12,9) as time) as DateTimeDataType

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.

MEMBER Measures.PercentageOfTotal
as 
 [Measures].[Your Measure Count] / Sum( Axis(1), [Measures].[Your Measure Count])
 ,FORMAT_STRING="Percent"

SELECT {
[Measures].[Your Measure Count],
[Measures].[PercentageOfTotal]
} ON COLUMNS, 

{[Date].[Month].[Month].Members}
ON ROWS
 
FROM [Wruggles_Cube] 

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 need to be something with a natural sort order, so either 1 through to 12 or the year concatenated with the month i.e. for year 2010, the key for Jan through to March would be 201001, 201002, 201003 etc. The main name can be Jan, Feb, Mar, or your own custom display format.

WITH MEMBER Measures.[MonthNameOrder] AS
[Date].[Month].CurrentMember.MEMBER_NAME

MEMBER Measures.[MonthKeyOrder] AS
[Date].[Month].CurrentMember.MEMBER_KEY

MEMBER Measures.X
as 1

SELECT {
[Measures].[X],
[Measures].[MonthNameOrder],
[Measures].[MonthKeyOrder]
} ON COLUMNS, 

{[Date].[Month].[Month].Members}

ON ROWS
 
FROM [Wruggles_Cube]

Now in your SSRS chart, drag Month to your Categories, right click the Month within Categories, select Category Group properties, select sorting, then choose the MonthKeyOrder column.

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 group, the newest date will be ranked 1, then increment up to the oldest date, for example :

Id UpdateDate Rank
1 2013-04-11 3
1 2013-04-12 2
1 2013-04-13 1

And this is the code to do it :

SELECT *, row_number() over (
         partition by Id
		 order by UpdateDate desc) as [Rank]
FROM dbo.SomeTable;

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 StartRange EndRange
1 101 105

Which you want represented as :

Id StartRange EndRange RangeValue
1 101 105 101
1 101 105 102
1 101 105 103
1 101 105 104
1 101 105 105

Moving along, your view would look something like this, a Common Table Expression embedded in a view…

CREATE VIEW [dbo].[RangeBuster_VW]
AS
WITH RangeBuster (Id, SomeDate, StartRange, EndRange, RangeValue)
 AS (
SELECT [Id]
      ,[SomeDate]
      ,CAST([StartRange] as int) AS StartRange
      ,CAST([EndRange] as int) AS EndRange
      ,CAST([StartRange] as int) AS RangeValue
  FROM [dbo].[SomeTable]
UNION ALL
SELECT [Id]
      ,[SomeDate]
      ,[StartRange]
	  ,[EndRange]
	  ,[RangeValue] + 1
FROM RangeBuster
WHERE [RangeValue] < [EndRange] )
SELECT * FROM RangeBuster
GO

So this is good but say you also want to rank the rows in each grouping :

Id StartRange EndRange RangeValue Rank
1 101 105 101 5
1 101 105 102 4
1 101 105 103 3
1 101 105 104 2
1 101 105 105 1

…then add the following to your SELECT clause within the view…

SELECT *, row_number() over (
         partition by Id
		 order by RangeValue desc) as [Rank]
FROM RangeBuster