SQL SERVER – Query Writing Strategy – SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 1

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Why Buy this Book: Weather you are a tester, developer, or administrator of SQL there are some basic terms and skill they are all expected to know. The core of design, permissions, queries, and SQL objects is often many separate books. But what if you want the proficient base across all these displaces. If you are starting out or are self-thought this will help fill in the pieces you may not know was missing.

What will I learn after reading this book: Queries with all types of Joins, Creating tables and stored procedures, Transactions, Login Permissions, and workplace tips?

Here is something I have yet to find in any book. When enthusiastic SQL students do this, they experience a revelation. The number of errors drops significantly and the speed at writing queries increases immediately. Knowing how to narrow down what we are looking for amongst a vast list of choices helps immensely.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter4.0Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Query Writing Strategy

When visiting a new restaurant, we will ask to see the menu, because we want to see all they have to offer. The odds are that we might be interested in half of the items, but only need a few dishes for our group at the table. Looking at the menu is like starting a query with a ‘SELECT *’ statement. Once we have looked at all the fields, we narrow our choice(s) to only the items we want at the time.

Sometimes restaurants have multiple menus. My favorite restaurant has a kids’ menu, an adult menu, a gluten-free menu and a drink menu. After looking this over a selection of what I liked was narrowed a few items. The Menu organizes what we have to choose from then we pick the one we want. We can write queries this way using the same two phases.

Phase I: Organize. When building a new query from many tables, we often find ourselves wondering, “Where do I start?” First, lay the steps out by identifying which tables contain the essential data. Second, get all the table joins working with a basic ‘SELECT *’ statement. Third, add any basic filtering criteria.

Phase II: Itemize. Once all joins and criteria, such as SELECT, FROM and WHERE are working, we are ready for Phase II. This entails going back and changing our ‘SELECT *’ to an itemized SELECT field list as the final step.

Let’s say we know what fields we want in our query even before we start. In this figure we write a SELECT statement and just have one of the two tables needed in FROM clause. When completed, the FROM clause will have both tables, but for now we just want to get the Location table working. By using the ‘SELECT *’ strategy on the left, we remove any possible errors from line 1. From there, we can focus on the more complicated logic used for joining tables together. We can add tables one at a time until everything is working. This is the Organize phase.

After our query is organized and working, we can go back and itemize the SELECT field list to display only the fields that are necessary. This is done during Phase II (Itemize). The steps for this system are broken down as follows:

Phase I: ORGANIZE (write a SELECT * query statement with joins)

--Test first table logic
SELECT *
FROM Location
--Test second table with join
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
--Test all tables with criteria
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'

Phase II: ITEMIZE (itemize the SELECT field list)

--Choose the fields
SELECT FirstName, LastName, City, [State]
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Beginning SQL 2012 – Basics of CONVERT and FORMAT Function – Abstract from Joes 2 Pros Volume 5

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 5

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

OPENXML has been around longer than the XML data type in SQL Server. The OPENXML requires you to use a series of system stored procedures and a variable to keep track of a handle. Using the number handle was a formality as the real work was in the patterns getting the right values from the right nodes. In this post we will shred XML with an XPath into nodes without needing to use OPENXML or keep track of numbered handles. This is possible with the nodes() method of the XML data type.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 5” in the resource folder in a file named Lab6.3StarterSetup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

The nodes() Method

Take a look at the following code. It has three song records in XML stored in an XML data type variable called @Doc. If we run the code and SELECT it we will see the link appear in the result set. Note: If you don’t want to re-type this then open the Lab6.3Starter.sql from the resource folder or SQL 2012 series Volume 5 from Joes2Pros.com.

DECLARE @Doc XML
SET @Doc='<Music>
 <Song TitleID="13159">
 <WriterName>Neil Diamond</WriterName>
 <Title>Red-Red Wine</Title>
 <Singer OrderID="1">
 <BandName>Neil Diamond</BandName>
 </Singer>
 <Singer OrderID="2">
 <BandName>UB40</BandName>
 </Singer>
 </Song>
 <Song TitleID="13160">
 <WriterName>Prince</WriterName>
 <Title>Manic Monday</Title>
 <Singer OrderID="1">
 <BandName>The Bangles</BandName>
 </Singer>
 </Song>
 <Song TitleID="13161">
 <WriterName>Roy Orbison</WriterName>
 <Title>Pretty Woman</Title>
 <Singer OrderID="1" BandName="Roy Orbison">
 <BandName>Roy Orbison</BandName>
 </Singer>
 <Singer OrderID="2">
 <BandName>Van Halen</BandName>
 </Singer>
 </Song>
 </Music>'
SELECT @Doc

Right now it looks like the three nodes are in one record in our result set. If we open the link and collapse the top-level nodes notice we have three songs or three nodes at the /Music/Song level.

How do we turn this into three records of XML fragments as opposed to one complete well-formed XML? The three /Music/Song level nodes will represent our three records. The nodes() method will produce the result needed. Instead of selecting the @Doc directly lets SELECT FROM @DOC with the nodes() method. The nodes() method will need to know the XPath. We want to see all three songs. Since each song is at the /Music/Song level we will use the following XPath:

SELECT
 FROM @Doc.nodes('/Music/Song')

The FROM clause normally expects a named table to produce a tabular result. Because the nodes() method in this example is in the FROM clause it needs to represent and look like a table. To do this we will simply alias the method. In this example we will alias this AS a table named Songs.

SELECT
 FROM @Doc.nodes('/Music/Song') AS Songs

Sometimes tables have no records but they always have fields. Like any table it has at least one field. Most tables have many fields. So a table is likely to contain a collection of records but sometimes a table may have no records at all. A table must have at least one field. That means a table is guaranteed to be a collection of at least one field. In other words a table will have a collection of columns even if it has no records.

Currently our Songs table does not have any defined way to refer to the collection of fields that make up this table alias. If we look back at the original XML there are several field options from the different elements and attributes such as TitleID, WriterName, BandName, etc. We need to specify to our Songs table that all these possible fields are inside a collection which we will call SongRow:

SELECT
 FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)

SongRow is like a suitcase that holds all of our field names for the table Songs. The first value we want to pull out is going to be the Title. From the Songs table, SELECT the SongRow collection and query the (‘Title’) element:

SELECT SongRow.query('Title')
 FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)

To recap the nodes() method, alias the tablename with the rows stored within the column collection (or field collection) in parentheses. In the SELECT statement specify the column(s).

Combining value() and nodes() methods

The nodes() method can shred an XML data type into a tabular stream while allowing us to identify the rowpattern that make up each node. Let’s start this section where we left off.

By specifying that our node is at the /Music/Song level we returned three XML fragments. The first record is <Title>Red-Red Wine</Title> which is a very small XML fragment showing us that song title in an XML element. What if we wanted Red-Red Wine as VARCHAR data without XML? In other words we don’t need the data with tags showing as XML fragments. We really want parsed values not XML fragments to make up our three rows. This is a simple change in the query. Change the SELECT list to use the value() method instead of query() and add a second argument that specifies the data type. The value() method needs a singleton [1] so add that to the Title in the first parameter as seen in the following code:

SELECT SongRow.value('Title[1]', 'VARCHAR(100)')
 FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)

By using the value() method with the nodes() method we were able to pull VARCHAR data from the XML stream. We want to pull out another field from the SongRow column collection. The <Title> element worked and is a child of <Song>. The <TitleID> is also a child of <Song> as an attribute. The nice thing about an attribute is they are guaranteed to be singletons.

It makes sense for ID to be the first field listed in a query so we can drop the Title down to the second line of our SELECT list and create another field. Let’s make TitleID our first field by placing it in front of Title in the SELECT list. TitleID is an INT and we will alias it as TitleID and Title AS Title:

SELECT SongRow.value('@TitleID', 'INT') AS TitleID,
 SongRow.value('Title[1]','VARCHAR(100)') AS Title
 FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)

Since attributes are guaranteed to be singletons it is not necessary to specify [1] in the query. This code now pulls out two fields from the SongRow collection since we used two different value() methods (one for each field).

We were able to pull out the TitleID as well as the Title using the value() method and nodes() method together. Let’s take a moment and look at the XML we are working from.

Let’s use what we know and pull out the first BandName for each singer. BandName is not a direct child of the /Music/Song node, it is a grandchild. Since the specified node level is /Music/Song how do we reach down two more levels for BandName? In the value() method we can specify the additional levels needed. In this case it is Singer[1]/BandName[1]. These are elements so we need to specify the singleton to pull the first Singer and first BandName of each song:

SELECT SongRow.value('@TitleID', 'INT') AS TitleID,
 SongRow.value('Title[1]','VARCHAR(100)') AS Title,
 SongRow.value('Singer[1]/BandName[1]', 'VARCHAR(100)')
 AS BandName
 FROM @Doc.nodes('/Music/Song') AS Songs(SongRow)

Using the value() method along with the nodes() method we have been able to shred our XML without using OPENXML or handles.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Basics of CONVERT and FORMAT Function – Abstract from Joes 2 Pros Volume 4

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 4

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

From the September 17, 2011 blog post on the new SQL 2012 FORMAT function we learned how to format currency and time using different cultures. This is an improvement on what came before and also gives us new possibilities for getting date labels without needing to use DATEPART. In this post we will compare the FORMAT function to the previous techniques and also show you an easy way to grab the part of the date you need for reports.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 4” in the file SQLQueries2012Vol4Chapter8.1-10Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

CONVERT function basics

Do you think January 5th 2013 should be shown as 1/5/2013 or 5/1/2013? The best answer is to each their own. The FORMAT function lets you pick the style, or better yet it can be relevant to the culture of the computer running the query.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee
  EmpID FirstName LastName LocationID ManagerID CharDate
1 1 Alex Adams 1 4 01/01/2001
2 2 Barry Brown 1 11 08/12/2002
3 3 Lee Osako 2 11 09/01/1999
4 4 Dave Kinnison 1 11 03/16/1996
5 5 Eric Bender 1 11 05/17/2007
6 6 Lisa Kendall 4 4 11/15/2001

 

 

 

 

 

 

20 rows

It looks like Barry Brown’s hire date is August 12, 2002. That is because in the USA culture we specify month/day/year which makes 08/12/2002 as August 12th 2002. However, if we were in Europe (where it’s day/month/year), 08/12/2002 would look a lot like December 8, 2002.

Let’s try a different formatting style using the CONVERT function. By changing the formatting style from 101 to 103 in the same query, notice we retrieve the date in the day, month, and then year.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee
  EmpID FirstName LastName LocationID ManagerID CharDate
1 1 Alex Adams 1 4 01/01/2001
2 2 Barry Brown 1 11 12/08/2002
3 3 Lee Osako 2 11 01/09/1999
4 4 Dave Kinnison 1 11 16/03/1996
5 5 Eric Bender 1 11 17/05/2007
6 6 Lisa Kendall 4 4 15/11/2001

 

 

 

 

 

 

20 rows

As we saw, style 103 returns the European day/month/year, while style 109 returns 3 letter abbreviated month/integer day/ integer year as seen in the comparison.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 103) AS CharDate
 FROM Employee
 WHERE EmpID = 2
SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 109) AS CharDate
 FROM Employee
 WHERE EmpID = 2

FORMAT with Culture Dates Basics

Now we will take the previous code and utilize the new FORMAT function. Instead of going through the process of converting the field we will simply set the format we are looking for. Here we replaced CONVERT with FORMAT and instead of naming it an NVARCHAR and picking a style, we simply (in parentheses) name the field, and in single quotes we set the parameter ‘d’ for day to achieve the same result.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 CONVERT(NVARCHAR, HireDate, 101) AS CharDate
 FROM Employee
 WHERE EmpID = 4
SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd') AS CharDate
 FROM Employee
 WHERE EmpID = 4

In that last example we choose d for day. You can also choose m (month) to return the month and day or y (year) as your parameters to return month and year. In all the examples so far the style is in the “en-us” culture because that’s the culture set on the server we used. It is not normally necessary to set this function however; there is a third optional parameter. What if we had done ‘en-gb’ for Great Britain? You can see here, the result set has a [HireDate] of 16/03/1996 and the numbers are separated with forward slashes ‘/’ as would be customary in Great Britain.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'en-gb') AS CharDate
 FROM Employee
 WHERE EmpID = 4
  EmpID FirstName LastName LocationID ManagerID CharDate
1 4 Dave Kinnison 1 11 16/03/1996

 

 

 

 

 

 

1 rows

Let’s change it up a bit more. Let’s use the style format ‘de-de’, for German, Germany. What does that look like? We can see it gives us day, month and year, separated by dots as would be customary in Germany.

SELECT EmpID, FirstName, LastName, LocationID, ManagerID,
 FORMAT(HireDate, 'd', 'de-de') AS CharDate
 FROM Employee
 WHERE EmpID = 4
  EmpID FirstName LastName LocationID ManagerID CharDate
1 4 Dave Kinnison 1 11 16.03.1996

 

 

 

 

 

 

1 rows

FORMAT with Months

You have seen what happened to the [HireDate] field when we ran it through the format with the ‘d’ and ‘en-us’. This is great for days, but what if we put a series of “MMMM” in there? Let’s put four M’s for month and simplify the fields to show just the [HireDate] and formatted fields. What will the expression field look like? As you can see, this format will pull up just the month fully spelled out. The first record says January, the second record August, and so on.

SELECT HireDate,
 FORMAT(HireDate, 'MMMM', 'en-us') AS CharDate
 FROM Employee
  HireDate CharDate
1 2001-01-01 00:00:00.000 January
2 2002-08-12 00:00:00.000 August
3 1999-09-01 00:00:00.000 September
4 1996-03-16 00:00:00.000 March
5 2007-05-17 00:00:00.000 May
6 2001-11-15 00:00:00.000 November

 

 

20 rows

Let’s try a few other combinations. What does three M’s look like? It shows us a 3 letter month abbreviation, like Jan, Aug, Sep. Let’s try two M’s. This format returns a two digit numeric, like 01 for January and 08 for August. What does a single M return? This returns the whole month spelled out, with the day. For example, January 01 and August 12 and so forth.

BE CAREFULL (MM is not mm)

SELECT HireDate, FORMAT(HireDate, 'MMM', 'en-us') AS CharDate
 FROM Employee
SELECT HireDate, FORMAT(HireDate, 'MM', 'en-us') AS CharDate
 FROM Employee
SELECT HireDate, FORMAT(HireDate, 'M', 'en-us') AS CharDate
 FROM Employee
  HireDate CharDate
1 2001-01-01 00:00:00.000 Jan
2 2002-08-12 00:00:00.000 Aug
  HireDate CharDate
1 2001-01-01 00:00:00.000 01
2 2002-08-12 00:00:00.000 08
  HireDate CharDate
1 2001-01-01 00:00:00.000 January 01
2 2002-08-12 00:00:00.000 August 12

 

 

60 rows

Up to this point the month, day and year formats all seem straight forward. There are a few things to be aware of that can get a little tricky. What happens if this ‘M’ is lower case? As you can see, there is no real change in the result. How about ‘mm’? Now we are getting a different result. The lower case ‘mm’ calls for a return in minutes. Since the examples returned are all zeros it’s really hard to tell exactly what this new field means because the hire dates don’t go down to the minutes. To really see this example, let’s utilize GETDATE() and we can see the formatted field returns 06 since this was run on Oct 6th, 2012 at 11:22pm.

SELECT HireDate, FORMAT(HireDate, 'mm', 'en-us') AS CharDate
 FROM Employee
SELECT GETDATE(), FORMAT(GETDATE(), 'mm', 'en-us')
HireDate CharDate
2001-01-01 00:00:00.000 00
2002-08-12 00:00:00.000 00
(No column name) (No column name)
2012-10-06 21:22:38.643 22

 

21 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Spatial Unions and Collections – Abstract from Joes 2 Pros Volume 3

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 3

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

In business we often hear the phrase, “We had a good quarter.” Immediately, we know this means a three month span where sales and profits have been aggregated together for the company. Take a company like Costco that might have $65 billion in total sales during the 4th quarter (October, November and December). Of course, this total comes not from a single sale of a 65 billon dollar yacht, rather from millions of sales for common items like snacks, drinks, clothes, and light bulbs. We know how to use GROUP BY and SUM to calculate totals and combine similar data. In the case of Costco we group by calendar quarter and then sum on the sales. Although aggregates are commonly used with numbers, they can also be used with spatial land coordinates to assemble them together, much like a jigsaw puzzle.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 3” in the files SQLQueries2012Vol3Chapter6.0Setup.sql and SQLQueries2012Vol3Chapter6SpecialSetup.sql.

Spatial Unions and Collections

My elementary school was right across the street from my home. The playground in back of the school was very large and had 3 full sized football (soccer) fields. Not all of this land belonged to the school. Across from the play fields was the Boys and Girls Club, which is a charity for after school kids’ projects for parents that need to work late. Since they are typically not open during school hours, the land they owned was able to be enjoyed during the day by my school. In this case, the land of the school and the club are aggregated to make one giant play and exercise area. Shapes that overlap or touch can be aggregated into one single shape (known as a polygon).

OGC (Open Geospatial Consortium) Functions Basics

In this example of the school and club, we aggregated the land for two owners. This ability became available with SQL Server 2008. There would be no way to aggregate the land for three owners into a single query. Let’s see how SQL Server aggregated spatial results between two shapes with the following examples.

SELECT * FROM HumanResources.RoomChart

By narrowing our focus to look at only the Water Tower and the Yard shapes, we can easily see that the Water Tower is only part way on the property of the Yard owner.

SELECT *
 FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)

What happens when an object occupies space in a portion of another object? When we ran the previous code, the results show how the Water Tower is partially in the Yard. Will the STContains() function see if  the Water Tower is contained within the Yard? Will the answer to this question be YES, or NO? How about the Parking Lot, is it contained in the Yard? The results for the following code sample is shown below:

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
DECLARE @Prk GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT @Prk = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 8
SELECT @Yard.STContains(@Wtr) AS IsTowerInYard,
@Yard.STContains(@Prk) AS IsParkInYard
  IsTowerInYard IsParkInYard
1 0 1

 

 

1 rows

We can see how to bring two shapes together by using the STUnion() function with the GEOMETRY data stored in the Yard and Water Tower fields. Building the query is identical to how we previously determined if the Park or the Water Tower were contained in the Yard with the STContains() function. All we need to do is replace the STContains() function with the STUnion() function. The graphical view can be seen if you run the code and click on the “Spatial results” tab.

DECLARE @Yard GEOMETRY
DECLARE @Wtr GEOMETRY
SELECT @Yard = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 5
SELECT @Wtr = RoomLocation
FROM HumanResources.RoomChart
WHERE R_ID = 9
SELECT GEOMETRY = @Yard.STUnion(@Wtr)

Aggregate Unions

There are other functions available for aggregating shapes together that eliminate the need for declaring and selecting variables.

Let’s look at an example of how to use the UnionAggregate() method and look at what the results are by running the this code.

SELECT GEOMETRY::UnionAggregate(RoomLocation)
FROM HumanResources.RoomChart
WHERE R_ID IN (5,9)
Aggregating more than 2 shapes

Let’s look at example of some business case uses when working with shapes. We are going to use a special table named SkiAreas that is normally not in the dbBasics database. To get this table you will need to run the SQLQueries2012Vol3Chapter6SpecialSetup.sql script. The data contained in the SkiAres will help solidify how we can easily combine shapes together to support different business requirements.

First, we will need to find out what kind of data resides in this table. The next code sample will show all the fields and records of the SkiAreas table.

USE dbBasics
GO
SELECT *
FROM SkiAreas
  ShapeID ShapeCode ShapeName ShapeParentCode ShapeOgraphy ShapeMetry
1 1 ID Idaho State US 0xE6100…003 0xE610…003
2 2 MT Montana State US 0xE6100…003 0xE610…003
3 3 Kt Kootenai County ID 0xE6100…003 0xE610…003
4 4 Sh Shoshone County ID 0xE6100…003 0xE610…003
5 5 Cl Clearwater County ID 0xE6100…003 0xE610…003
6 6 Ln Lincoln County MT 0xE6100…003 0xE610…003
7 7 Sd Sanders County MT 0xE6100…003 0xE610…003
8 8 M1 Runt Mountain ** 0xE6100…003 0xE610…003

 

 

 

 

 

 

8 rows

With five counties in two different states, a travel brochure would normally show three of these counties in Idaho and two counties in Montana . We can get a little more specific, by looking for just the ShapeName and the ShapeMetry fields. Click on the spatial results tab and in the ‘Select label column:’ drop-down menu choose the ShapeName field to see the names for the shapes returned by the query.

SELECT ShapeName, ShapeMetry
FROM SkiAreas
WHERE ShapeName LIKE '%County'

 

Now that we have a working query that displays each county in the SkiAreas table. We can easily convert it to a query using the UnionAggregate() function to combine each county into a single shape for this marketing region.

SELECT GEOMETRY::UnionAggregate(ShapeMetry)
FROM SkiAreas
WHERE ShapeName LIKE '%County'

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 2

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 2” in the file SQLQueries2012Vol2Chapter5.1Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs

Aggregation Functions

Most people are familiar with aggregation using a GROUP BY with some of the fields using an aggregated function like SUM or COUNT. With GROUP BY you are limited to including only aggregated data in your result set. Of the two examples below we see the first query runs and gets a total of the amounts but the second query throws an error.

SELECT SUM(Amount) AS SumOfGrants
FROM [Grant]
Messages
193700.00

1 rows

SELECT *, SUM(Amount) 
FROM [Grant]
WHERE GrantID = '001'
Messages
Msg 8120, Level 16, State 1, Line 1
Column ‘Grant.GrantID‘ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0 rows

The OVER() clause

If we want to show the total amount next to every record of the table – or just one record of the table we can use the SUM aggregation function with OVER() instead of GROU P BY,

SELECT *, SUM(Amount) OVER()
FROM [Grant] 

Adding PARTITION BY to OVER()

Adding the OVER() clause allows us to see the total amount next to each grant record. OVER() applies the aggregation SUM(Amount) across all rows of a query. Here we have left the parentheses blank, which causes OVER() to apply the aggregation across all rows of each query. We could get the sub total for each employee. See how Employee 10 has the biggest grant of $41,000. If you look at Employee 7 who has three smaller grants but a total of more than $41,000.  We can see this example where all of Employee 7’s individual grants are next to his total using the following code:

SELECT *, SUM(Amount) OVER(PARTITION BY EmpID)
FROM [Grant]

Market Share (Comparing individuals with totals)

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The sum of all 10 grants is $193,700. Recall the largest single grant (007) is $41,000. Doing the quick math in our head, we recognize $41,000 is around 1/5 of ~$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head.  We want the new column to divide each grant amount by $193,700 (the total of all the grants). The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

SELECT *, SUM(Amount) OVER() AS CompanyTotal, Amount / SUM(Amount) 
OVER() 
FROM [Grant]

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example: the ratio 0.2116 represents a percentage of 21.16%. Add a * 100 to the expression to turn the ration into a percentage.  To finish, give the column a descriptive title, PctOfTotal.

SELECT *,
 SUM(Amount) OVER() AS CompanyTotal,
 Amount / SUM(Amount) OVER() * 100 AS PctOfTotal
 FROM [Grant]
  GrantID GrantName EmpID Amount CompanyTotal PctOfTotal
1 001 92 Purr_Scents %% team 7 4750.00 193700.00 2.45
2 002 K_Land fund trust 2 15750.00 193700.00 8.13
3 003 Robert@BigStarBank.com 7 18100.00 193700.00 9.34
4 004 Norman’s Outreach NULL 21000.00 193700.00 10.84
5 005 BIG 6’s Foundation% 4 21000.00 193700.00 10.84
6 006 TALTA_Kishan Internatio… 3 18100.00 193700.00 9.34
7 007 Ben@MoreTechnology.com 10 41000.00 193700.00 21.16

 

 

 

 

 

 

10 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Beginning SQL 2012 – Why we use Code Comments – Abstract from Joes 2 Pros Volume 1

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 1

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

Old classic movies utter this famous phrase “Gentlemen, this is off the record”.  In movies this is used when talking to the press and letting them know a certain comment or two will be said, however it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code. Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called a commenting.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “Beginning SQL 2012 Joes 2 Pros tutorial series Volume “.

In the file SQLQueries2012Vol1Chapter11.0Setup.sql, if you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Why we use Code Comments

Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readmefile allowing other people reading it to learn more about the code being used.

Single-Line Comments for Communicating

In the example below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.

Use Comments to Say “Why”

It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.

Single-Line Comments for Trouble-Shooting

Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself.

We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. The ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code runs is a valuable troubleshooting tool. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table, without having to rewrite it as a separate query, or deleting any of the code in the INNER JOIN segment.

SELECT *
 FROM Employee AS em
 --INNER JOIN Location as lo
 --ON em.LocationID = lo.LocationID
  EmpID LastName FirstName HireDate LocationID ManagerID Status
1 1 Adams Alex 2001-01-01… 1 11 NULL
2 2 Brown Barry 2002-08-12… 1 11 NULL
3 3 Osako Lee 1999-09-01… 2 11 NULL
4 4 Kennson David 1996-03-16… 1 11 Has Tenure
5 5 Bender Eric 2007-05-17… 1 11 NULL
6 6 Kendall Lisa 2001-11-15… 4 4 NULL

 

 

 

 

 

 

 

12 rows

Multi-Line Comments

When we want to comment out many consecutive lines of code, we have two different commenting techniques to accomplish this goal. Use single-line comments for each and every line to be prevented from running like the last example. The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces).

SELECT *
 FROM Employee AS em
 /*INNER JOIN Location as lo
 ON em.LocationID = lo.LocationID*/

In-Line Comments

What if we wanted to disable a specific part of code located on the same line as other code that we wanted to remain unaffected? The goal might be to disable one word in a long line of words within the code. We can achieve this goal by using an in-line commenting technique. In-line comments are best accomplished by using the same delimiters we used for making multi-line comments. We simply mark the beginning and ending of the word, or words, that we need to comment out of the code when the query is run. In this example, we want to display a report without the GrantID field. If we try to accomplish this by placing the ‘–‘ (double hyphens) delimiter, in front of the GrantID field, it’s going to prevent all the fields in the SELECT statement from being seen by SQL Server.

SELECT --GrantID, GrantName, EmpID, Amount
 FROM [Grant]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
Messages
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘FROM’

0 rows

The best way to accomplish the task of preventing the GrantID field from being displayed in our result set is to use the ‘/*’ ‘*/’ delimiters. Using this technique, we can easily prevent the smallest elements of a code block from executing, without moving or affecting the code surrounding it. The following example demonstrates how this is achieved.

SELECT /*GrantID,*/ GrantName, EmpID, Amount
 FROM [Grant]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
  GrantName EmpID Amount
1 92 Purr_Scents %% team 7 4750.00
2 Robert@BigStarBank.com 7 18100.00
3 Norman’s Outreach NULL 21000.00
4 @Last-U-Can-Help 7 25000.00

 

 

 

4 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Get Free Books on While Learning SQL Server 2012 Error Handling

Fans of this blog are aware that I have recently released my new books SQL Server Functions and SQL Server 2012 Queries. The books are available in market in limited edition but you can avail them for free on Wednesday Nov 14, 2012. Not only they are free but you can additionally learn SQL Server 2012 Error Handling as well.

My book’s co-author Rick Morelan is presenting a webinar tomorrow on SQL Server 2012 Error Handling.

Here is the brief abstract of the webinar:

People are often shocked when they see the demo in this talk where the first statement fails and all other statements still commit. For example, did you know that BEGIN TRANCOMMIT TRAN is not enough to make everything work together? These mistakes can still happen to you in SQL Server 2012 if you are not aware of the options. Rick Morelan, creator of Joes2Pros, will teach you how to predict the Error Action and control it with & without structured error handling.

Register for the webinar now to learn:

  • How to predict the Error Action and control it
  • Nuances between successful and failing SQL statements
  • Essential SQL Server 2012 configuration options
Register for the Webinar and be present during the webinar. My co-author will announce a winner (may be more than 1 winner) during the session. If you are present during the session – you are eligible to win the book.
The webinar is scheduled for 2 different times to accommodate various time zones. 1) 10am ET/7am PT 2) 1pm ET/11am PT. Each webinar will have their own winner. You can increase your chances by attending both the webinars. Do not miss this opportunity and register for the webinar right now.
The recordings of the webinar may not be available.

Reference: Pinal Dave (http://blog.SQLAuthority.com)