Start Learning T-SQL with These Examples

Now that we have learned most basic SELECT statement. Let’s move to following three examples with AdventureWorksDW2016:

These examples are from the Microsoft virtual lab —- Exploring What’s New in SQL Server 2016 Reporting Services for Paginated Reporting.

First two examples are fairly easy and self-explanatory. I will add explanation of 3rd example at the end.

SELECT [OrganizationKey], [OrganizationName] FROM [dbo].[DimPrganization]

WHERE [OrganizationKey] IN (3,4,5,6,7,8,11,12,13) ORDER BY [Orgainzation Name]

SELECT [DepartmentGroupKey], [DepartmentGroupName] FROM [dbo].[DimDepartmentGroup]

UNION ALL

SELECT -1, N’[All Department Groups]’

ORDER BY [DepartmentGroupName];

USE [AdventureWorksDW2016];

GO

CREATE PROCEDURE [dbo].[uspReport_OrganizationExpenditures]

@OrganizationKey INT

,@DateFrom DATE

,@DateTo DATE

,@DepartmentGroupKey INT

AS

SET NOCOUNT ON;

WITH [Accounts] AS

(

<strong> SELECT</strong>

[a1].[AccountDescription] AS [level_01]

,[a2].[AccountDescription] AS [level_02]

,[a3].[AccountDescription] as [level_03]

,COALESCE ( [a3].[AccountKey], [a2].[AccountKey], [a1].[AccountKey] ) AS [AccountKey]  ,ROW_NUMBER ( ) OVER (PARTITION BY COALESCE (a3). [AccountKey], [a2].[AccountKey], [a1].[AccountKey] )

ORDER BY [a3].[AccountKey] DESC, [a2].[AccountKey] DESC  AS [Rownumber]

<strong> FROM</strong>

[dbo].[DimAccount] AS [a1]

LEFT JOIN [dbo].[DimAccount] AS [a2] on [a2].[ParentAccountKey]=[a1].[AccountKey]

LEFT JOIN [dbo].[DimAccount] AS [a3] ON [a3].[ParentAccountKey]=[a2].[AccountKey]

<strong>WHERE</strong>

[a1].[AccountType] =  N’Expenditures’

)

<strong> SELECT</strong>

[a].[Level_01]

,[a].[level_02]

,[a].[level_03]

, CAST (SUM([f].[Amount] AS MONEY) AS [Amount]

<strong>FROM</strong>

[Accounts] AS [a]

INNER JOIN [dbo].[FactFinace] AS [f] on [f].[Accountkey] = [a].[AccountKey]

<strong>WHERE</strong>

[a].[RowNumber] = 1

AND [f].[ScenarioKey] =1

AND [f].[OrganizationKey] = @OrganizationKey

AND [f].[Date]  BETWEEN @DateFrom AND @DateTo

AND [f].[DepartmentGroupKey] = CASE WHEN @DepartmentGroupKey = -1 THEN [f].[DepartmentGroupKey] ELSE @DepartmentGroupKey END

&nbsp;

GROUP BY

[a].[Level_01]

,[a].[Level_02]

,[a].[Level_03];

GO

At this moment, I want you to see that the bottom line of this long sql script consists of two SELECT statement. I have marked these two SELECT FROM and WHERE with bold font. First SELECT uses two self joins to flat account hierarchy. If you are interested in the topic of self join, you can search and find more find more examples. Second SELECT uses aggregate function SUM ( ) and GROUP By clause.

Besides these two SELECT, there are more in this script. For further learning, you will need to find T-SQL class on Lynda.com, or use this class on Microsoft Virtual Academy: Querying with Transact-SQL.

Leave a Reply Cancel reply