Unleashing Common Table Expressions

On the next two Saturdays I will be presenting the Unleashing Common Table Expressions presentation at SQL Saturday 172 in Portland Oregon, and SQL Saturday 166 in Olympia Washington.

As part of refreshing the presentation I have added a few more examples of how CTE’s can be used in common practice.

Every time I do this presentation I add a few more samples, some of them are new CTE examples that I have come up with since the last presentation, and others are ones that have been suggested by people who have attended one of the sessions.

I have given the Unleashing Common Table Expressions presentation in the past at other events.

 

I am looking forward to SQL Saturday 172 in Portland Oregon, and SQL Saturday 166 in Olympia Washington.  It is going to be a great time.

 

-Steve Stedman

Bellingham WA

TSQL Rounding or Truncating DateTime to Nearest Day, Hour, Minute or Second

One thing that I end up having to look search on regularly is rounding of dates and times in Transact SQL, having looked this up too many times I finally realized that it is time for me to do my own blog post for it.

First off, whats the difference between rounding and truncating in these examples. Rounding rounds to the closest second, so 10:00:31 is rounded up to 10:01:00, and 10:00:29 is rounded down to 10:00:00. With truncation, it simple changes the truncated area to 0’s. so 10:00:31 gets truncated down to 10:00:00, and so does 10:00:59. Sometimes you may want rounding, and sometimes you may want truncation (floor) for your specific needs.

The following example shows how using datetime in SQL Server to round to the second, round to the minute, round to the hour and round to the day. It also includes the TSQL to truncate to the hour, minute and second.

The query:

declare @dtVariable as datetime;
set @dtVariable = getdate();

-- if you are testing this before noon, uncomment the
--     following line to simulate an after noon time.
--set @dtVariable = dateadd(hour, 12, @dtVariable)

-- Rounding to the second
select @dtVariable as Original,
       DATEADD(ms, 500 - DATEPART(ms, @dtVariable + '00:00:00.500'),
	           @dtVariable) as RoundedToSecond;

-- Truncated to the minute
select @dtVariable as Original,
       DATEADD(minute, DATEDIFF(minute, 0, @dtVariable), 0) as TruncatedToMinute;

-- Rounded to minute
select @dtVariable as Original,
       DATEADD(minute, DATEDIFF(minute, 0,
	         DATEADD(second, 30 - DATEPART(second, @dtVariable + '00:00:30.000'),
	         @dtVariable)), 0) as RoundedToMinute;

-- Truncated to the hour
select @dtVariable as Original,
       DATEADD(hour, DATEDIFF(hour, 0, @dtVariable), 0) as TruncatedToHour;

-- Rounded to hour
select @dtVariable as Original,
       DATEADD(hour, DATEDIFF(hour, 0,
	         DATEADD(minute, 30 - DATEPART(minute, @dtVariable + '00:30:00.000'),
	         @dtVariable)), 0)  as RoundedToHour;

-- Truncated to the day
select @dtVariable as Original,
       DATEADD(Day, DATEDIFF(Day, 0, @dtVariable), 0) as TruncatedToDay;

-- Rounded to day
select @dtVariable as Original,
       DATEADD(day, DATEDIFF(day, 0,
	         DATEADD(hour, 12 - DATEPART(hour, @dtVariable + '12:00:00.000'),
	         @dtVariable)), 0)  as RoundedToDay;

This code was tested and confirmed functional on SQL Server 2005, SQL Server 2008, SQL Server 2008R2 and SQL Server 2012.

And the output generated looks like this.

I hope you find this as useful as I do on a regular basis.

-Steve Stedman
Bellingham WA.

Using a CTE in a Function to Split Up a Database Connect String

I came across this example when preparing my CTE presentation for SQL Saturday in Portland OR in 2 weeks.

CREATE FUNCTION dbo.SplitConnectString(@s varchar(8000))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX(';', @s) as pos, 0 as lastPos
 UNION ALL
 SELECT CHARINDEX(';', @s, pos + 1), pos
 FROM splitter_cte
 WHERE pos > 0
 ),
 pair_cte AS (
 SELECT chunk,
 CHARINDEX('=', chunk) as pos
 FROM (
 SELECT SUBSTRING(@s, lastPos + 1,
 case when pos = 0 then 80000
 else pos - lastPos -1 end) as chunk
 FROM splitter_cte) as t1
 )
 SELECT substring(chunk, 0, pos) as keyName,
 substring(chunk, pos+1, 8000) as keyValue
 FROM pair_cte
);
GO
declare @connectString varchar(2048)
set @connectString = 'server=myserver;user id=sa;password=asdfasdfasdasdffjfjfj';
SELECT *
 FROM dbo.SplitConnectString(@connectString)
OPTION(MAXRECURSION 0);

Which produces the following output.

Database Health Reports – Historic Waits Report Sneak Preview

Coming in the next beta of the Database Health Report is a new feature called the Historic Reports which installs a small database to track database statistics over time.  This has an extremely light load on the overall database performance, and will allow you to track down details of why the database was slow at a specific point in time.

Here is the current rough version of the Historic waits report for one database.  With this report, you can filter down to specific time frames with different granularity options to find a specific point in time where the system was slow, and from there determine what query or queries were causing the system to be slow.

The new historic waits reports should completely replace and make obsolete the existing waits reports that were included in Beta 1 and Beta 2.  What I love about the new historic waits reporting is that this is something that I could use to make my job easier every day.

For now please download Beta 2 of the Database Health Reports or register on this site and you will be informed when Beta 3 of the Database Health Reports is available.   Beta 2 was cool, but Beta 3 is going to be spectacular.  Beta 3 is schedule for about November 15th, so I have about 3 and half weeks to get all the cool new historic waits monitoring features in for the Beta.  Beta 3 will support monitoring of SQL Server 2005, 2008, 2008R2 and 2012.

-Steve Stedman

Bellingham WA

Recursive CTE to Calculate Dates for the Last 30 Days

Have you ever needed to generate a list of dates for the last 30 days.  There are lots of different ways to do this with TSQL, but one way is with a CTE, where you can then left join against it with another query to generate a list of all  dates in a result set, along with the dates for the last 30 days that aren’t in the result set.

DECLARE @today as date;
DECLARE @30ago as date;
select @today = DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate())),
	   @30ago = DATEADD(DAY, 0, DATEDIFF(DAY, 0, getdate() - 30));
;WITH Last30DaysCTE as (
   SELECT @30Ago as CalendarDate
   UNION ALL
   SELECT dateadd(day , 1, CalendarDate) AS CalendarDate FROM Last30DaysCTE
   WHERE dateadd (day, 1, CalendarDate) < @today
)
SELECT *
  FROM Last30DaysCTE
OPTION (MAXRECURSION 30);

Which creates this list.

Just a quick CTE that can be very useful when generating a report.

Enjoy…

Steve Stedman

-Bellingham WA

Adventureworks2012 data to Google GeoChart Visualization

There are so many cool tools available today for data visualization.   This posting covers pulling data from the AdventureWorks2012 database running on SQL Server 2012, and then using the Google Visualization tools to display geographic data.

The final outcome will the this map.

People in the AdventureWorks2012 database.

Here is how I did it.

Step 1:

Create a query that generates totals for countries.  I wrote the following query to pull the totals by country.

SELECT cr.name, count(1)
  FROM [Person].[Address] a
 INNER JOIN [Person].[StateProvince] sp
       ON sp.StateProvinceID = a.StateProvinceID
 INNER JOIN [Person].[CountryRegion] cr
       ON cr.CountryRegionCode = sp.CountryRegionCode
 GROUP BY cr.name;

Which produced the following output.

Step 2:

Take a look at the Google code playground example at https://code.google.com/apis/ajax/playground/?type=visualization#geo_chart

Now all you need to do is replace the default list with the actual list from your query.

Step 3:

Format the query output to match the list format:

SELECT '[''' + cr.name + ''', ' + cast(count(1) as varchar(1024)) + '],'
  FROM [Person].[Address] a
 INNER JOIN [Person].[StateProvince] sp
       ON sp.StateProvinceID = a.StateProvinceID
 INNER JOIN [Person].[CountryRegion] cr
       ON cr.CountryRegionCode = sp.CountryRegionCode
 GROUP BY cr.name;

This query produces the following output.

Step 4:

Copy and paste the data from SQL Server Management Studio into the Code Playground example, like this:

Click the run code button in the Google Code Playground, and see if your code runs.  If not you will need to work out any bugs.

Step 5:

Copy the code and save it to a web page on your site.   The source code will look something like this:

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/
xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”content-type” content=”text/html; charset=utf-8″ />
<title>Google Visualization API Sample</title>
<script type=”text/javascript” src=”http://www.google.com/jsapi”></script>
<script type=”text/javascript”>
google.load(‘visualization’, ‘1’, {packages: [‘geochart’]});

function drawVisualization() {
var data = google.visualization.arrayToDataTable([
[‘Country’, ‘Popularity’],
[‘Australia’, 3632],
[‘Canada’, 1688],
[‘France’, 1851],
[‘Germany’, 1821],
[‘United Kingdom’, 1954],
[‘United States’, 8668]
]);

var geochart = new google.visualization.GeoChart(
document.getElementById(‘visualization’));
geochart.draw(data, {width: 556, height: 347});
}

google.setOnLoadCallback(drawVisualization);
</script>
</head>
<body style=”font-family: Arial;border: 0 none;”>
<div id=”visualization”></div>
</body>
</html>
Here is the link to the page on my site http://stevestedman.com/samples/visualization1.html

I hope you enjoy this.

-Steve Stedman

Bellingham WA