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

Windows Phone 8 : Track Usage with Data Sense

For all you hipsters that own a Windows 8 phone 😉 the new update 8.0.10328.78 contains a host of new features including Data Sense which gives you a simple setup and visualisations to track your usage.
http://www.windowsphone.com/en-au/how-to/wp8/basics/windows-phone-8-update-history

See your most data hungry Apps …

DataSenseReport

Watch your limits on the Tile …

DataSenseTile

Easy to setup too … for a postpaid account you just need to know your anniversary date and monthly data limit.  There are a few other setup options include prepaid (set a limit date) or unlimited (no limits but track usage).

DataSenseSetup

 

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

Enable Page Level Compression on a Table

Page level compression has the benefit of generating high levels of data compression on tables with oodles of data and lots of repetitive values. I like it http://technet.microsoft.com/en-us/library/cc280464.aspx because, amongst other things, both storage and performance work well for reporting applications. The compression activity searches columns for values that can be used to reduce storage space, so it can be ideal for fact tables. Read performance can also be quite beneficial for reporting applications http://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx.

To estimate the space savings benefit of enabling page level compression use the following stored procedure.

EXEC sp_estimate_data_compression_savings 'dbo', 'SomeTable', NULL, NULL, 'PAGE' ;

To enable page compression on an existing table :

ALTER TABLE dbo.SomeTable 
REBUILD WITH (DATA_COMPRESSION = PAGE);

Remove compression on the table as follows :

ALTER TABLE dbo.SomeTable 
REBUILD WITH (DATA_COMPRESSION = NONE);

If page compression is enabled AFTER the table is fully loaded as per the above REBUILD operation, then the table is fully rebuilt with page level compression in one swoop. If the table is created with page level compression BEFORE the load, then no page compression is initiated until the load process fills the first page. Until then, only row compression occurs. When the first page is full, then page compression is initiated and additional rows compressed into that page until the next page is reached.

Read about it here http://technet.microsoft.com/en-us/library/cc280449.aspx