Archive

Archive for the ‘SQL’ Category

Easy SQL totals and other Aggregates with rollup and cube

March 31, 2011 2 comments

David Cooksey on easy sums with SQL rollup

March 31, 2011 | David Cooksey

Easy SQL totals and other Aggregates with Rollup and Cube

SQL 2008’s introduction of the rollup and cube keywords allows for easy subtotals and other aggregates by your selected groups. To see how simple it is to set up your automatic subtotals, let’s look at an example. For those who want to skip right to SQL, see the script at the bottom.

Imagine you have a table that stores sales information. This table holds the customer name and the products that were purchased, along with the salesman who got credit for the sale and the purchase date. The precise schema for the table follows.

By joining this table to its related lookup tables we see that it has the following data:

If we wanted to write a query to give us totals by customer name, it would look something like the following.

By adding WITH ROLLUP to the query we get an automatic grand total.

The rollup keyword generates aggregate summaries based on the order of the columns in the group by clause. If grouping by customer name, salesman name, and product name—in that order—summary rows will be generated for all products by salesman and customer, and all salesmen and products by customer. However, there will be no summary row for all customers by either salesman name or product name.

Now let’s try something a little more complex. Let’s get totals and averages by customer and salesman.

The cube keywords used here generate summary rows for all combinations of columns in the group by clause. In this result set we have aggregates grouped by customers and aggregates grouped by sales. These summary rows have null in one or more of their columns. The grouping keyword tells us whether a particular row in the result set contains aggregate information for a given column. The grand total row is marked as a grouping row for both sales and customers. The grouping keyword may be necessary to distinguish summary rows from rows that legitimately contain null.

Rollup and cube provide grouped aggregate summaries with very little effort. I highly recommend them for anyone who has to do a lot of SQL report writing!

Download the .sql file

David Cooksey is a Senior .NET developer at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.

Recursive Common Table Expressions

November 5, 2009 7 comments

November 5th 2009 | Kevin Jones

Recursive Common Table Expressions

Recently we had the need to do a recursive query in SQL. We were using SQL 2005 and up, which supports a feature called Common Table Expressions, or CTEs. They aren’t new, yet so many blog posts present the same example over and over again. Common Table Expressions are extremely helpful when you have a hierarchy of data. In our example below we have a table called OrganizationUnits that contains a representation of the Organizational Units in an Active Directory domain. The basic schema looks like this:

OrganizationUnitId    INT IDENTITY
OrganizaitonUnitName    NVARCHAR(255)
ParentOrganizationUnitId    INT
DomainId    INT

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The ParentOrganizationUnitId column foreign keys to the OrganizationUnits table (itself). Our goal was, given an organization unit ID, get me all of its children, and its children’s children, etc. In SQL 2000 this used to be painful as it required a cursor. Let’s start with baby steps. The first thing we can do is remove the recursive part and just get the immediate children. That’s a simple select statement. Here is some sample data:

 

 

OrganizationUnitId

OrganizationUnitName

ParentOrganizationUnitId

DomainId

1

USA

NULL

1

2

California

1

1

3

Virginia

1

1

4

Los Angeles

2

1

5

Falls Church

3

1

6

Richmond

3

1

 

This is fairly typical of a table when dealing with these kinds of structures. So, if we wanted to get all of the children of Organization Unit 1, it would return all records. If we wanted to get all of the children of 3, it should return Falls Church and Richmond.

Let’s start with getting USA and all of its immediate children. That would look something like this:

SELECT    *
FROM        OrganizationUnits
WHERE        OrganizationUnitId = 1
OR
ParentOrganizationUnitId = 1

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

That wasn’t very difficult, right? However, it didn’t give us all of the children for Virginia or California. So let’s create a common table expression out of this table, as the “anchor”. We’ll name our common table expression “ou_cte”.

WITH ou_cte AS
(
    SELECT    *
    FROM        OrganizationUnits
    WHERE        OrganizationUnitId = 1
            OR
            ParentOrganizationUnitId = 1
)
SELECT * FROM ou_cte

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This is the basic syntax for a common table expression. Think of it as declaring an alias for a select statement so far. To make this really powerful though, that select statement inside of the common table expression can join on itself! This will be the recursive part of the Common Table Expression. The beginning and the end of each statement is denoted with a UNION ALL. We’ll start by removing the second part of the WHERE clause in the query since our recursive join will handle it.

WITH ou_cte AS
(
    SELECT    *
    FROM        OrganizationUnits
    WHERE        OrganizationUnitId = 1
)
SELECT * FROM ou_cte

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Now we will add the union to create the recursive join.

WITH ou_cte AS
(
    SELECT    *
    FROM        OrganizationUnits
    WHERE        OrganizationUnitId = 1
    UNION ALL
    SELECT    ou.*
    FROM        OrganizationUnits ou
            JOIN ou_cte
            ON ou_cte.OrganizationUnitId = ou.ParentOrganizationUnitId
)
SELECT * FROM ou_cte

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This will return all of the rows. If we changed that WHERE clause to 2, it would return California and Los Angeles.

Neat, right? You can do some powerful things on recursive joining. Like building a folder path. Imagine the folder path is required to have a column called “FullDisplayName” that returns a slash delimited path, like USA\Virginia\Falls Church. That is very easy to do. We’ll start by declaring FullDisplayName as OrganizationUnitName, then loop through each record, appending the name again.

WITH ou_cte AS
(
    SELECT    *, [OrganizationUnitName] AS [FullDisplayName]
    FROM        OrganizationUnits
    WHERE        OrganizationUnitId = 2
    UNION ALL
    SELECT    ou.*,
CAST(ou_cte.OrganizationUnitName + '\' +
ou.[OrganizationUnitName] AS VARCHAR(255)) AS
[FullDisplayName]
    FROM        OrganizationUnits ou
            JOIN ou_cte
            ON ou_cte.OrganizationUnitId = ou.ParentOrganizationUnitId
)
SELECT * FROM ou_cte

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

This will yield a computed path that meets our requirements.

2

California

1

1

California

4

Los Angeles

2

1

California\Los Angeles

 

This allows us to build some very powerful queries that remove a lot of in-code logic and make reporting much more simple.

Note the cast in the above operation. If I didn’t do this, it would give me an error saying I was trying to union results that weren’t of the same type. This is because I am concatenating strings – and that results in a char type who’s length is exactly the size of the concatenation, such as CHAR(22) for California\Los Angeles. It must be cast back to the exact same type and the same length for this to work.

These are some basic examples of what can be a very powerful query.

Kevin Jones is a Team Lead at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.

Removing time from SQL datetime

August 6, 2009 6 comments

Quick Date and time SQL

August 6th 2009 | Kevin Jones

Removing time from SQL datetime

We use SQL server to store data—a lot. Sometimes we need to remove the time for a DATETIME column. This is useful for analysis or grouping by day. There is no built-in function that does this, so developers often write their own. I’ve written several, some of which are much faster than others.

Some people convert it to a VARCHAR without the time, and back to a DATETIME (effectively making the time midnight). Something like this:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(15), GETDATE(), 101))

This works, but it’s slow when operating over tens of thousands of rows. It’s also a little tricky to remember if you don’t write SQL often.

If you use SQL 2008, you can use a new data type called DATE. It’s compatible with DATETIME, and it’s as simple as a CONVERT or CAST, meaning you can use operators on them.

SELECT CAST([Registered] AS DATE) FROM Users

You can cast it back to a DATETIME if you need the type to be a DATETIME. If your column doesn’t care about time and you use SQL 2008, I recommend changing the column type to DATE so you don’t have to worry about time.

However, if you use SQL 2005 or below, I recommend casting it to a FLOAT, calling FLOOR, then back to a DATETIME.

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

This is significantly faster than converting to the a VARCHAR and back to a DATETIME, mainly because when you cast a DATETIME to VARCHAR, SQL Server considers things like the collation, and what format it should use, then does it all again when parsing it back to a DATETIME.

When casting to a FLOAT, the integer part is the number of days, and the decimal part is the fraction part of the day, or time. Using FLOOR effectively gets rid of the decimal. You can also use CEILING to push it to the next day.

Kevin Jones is a Senior .NET Consultant and Team Lead at Thycotic Software, an agile software services and product development company based in Washington DC. Secret Server is our flagship password management software product.

Developer Tip Quickly look up table information in SQL Server Management Studio

July 20, 2009 2 comments

Quickly look up table information in SQL Server Management Studio

July 20th 2009 | Jonathan Cogley

Quickly look up table information in SQL Server Management Studio

You are working on a SQL query joining some tables when you need to know more about one of your tables … you could open another query window and run “sp_help tbUserGroup” OR you can just select the table and hit ALT F1 to do the same thing!

Quickly look up table information in SQL Server Management Studio

thanks to Mark King for showing me this trick

Jonathan Cogley is the CEO of Thycotic Software, an agile software consulting and product development company based in Washington DC. Secret Server is our flagship enterprise password management product.Are you on Twitter? Follow Jonathan