10/22/2013

Intro to #SQL Sample Code #MSBI

I will be presenting an Intro to SQL at the next Code Camp in Tampa!

 
As a spoiler, here's the code I plan to present:



-----------------------------------------------
--    1 - SELECT * FROM TABLE
-----------------------------------------------
SELECT *   FROM [HumanResources].[Department]
 
-----------------------------------------------
--    2  - SELECT FIELD NAMES FROM TABLE
-----------------------------------------------
SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [HumanResources].[Department]
 
-----------------------------------------------
--    3 - SELECT SPECIFIC FIELDS FROM TABLE
-----------------------------------------------
SELECT [Name] AS DepartmentName
      ,[GroupName] AS GroupName
FROM [HumanResources].[Department]
 
-----------------------------------------------
--    4  - WHERE CLAUSE
-----------------------------------------------
SELECT dep.[Name] AS DepartmentName
      ,dep.[GroupName] AS GroupName
FROM [HumanResources].[Department] dep
WHERE dep.GroupName = 'Research and Development'
 
-----------------------------------------------
--    5  JOINS / ALIAS
-----------------------------------------------
SELECT
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
       ,p.[FirstName]  + ' ' + p.[MiddleName] + ' ' + p.[LastName] AS FullName
    ,p.[Suffix]
    ,e.[JobTitle]
FROM [HumanResources].[Employee] e
       INNER JOIN [Person].[Person] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
 
-----------------------------------------------
--    6 - GROUP BY / HAVING CLAUSE / ORDER BY
-----------------------------------------------
SELECT
       COUNT(*) AS CountLastName
    ,p.[LastName]
FROM [HumanResources].[Employee] e
       INNER JOIN [Person].[Person] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
GROUP BY p.[LastName]
--HAVING COUNT(*)>2
--ORDER BY 1 DESC
 
-----------------------------------------------
-- 7 -JOINS INNER
-----------------------------------------------
SELECT *
FROM [Person].[Person] PER
INNER JOIN [Person].[PersonPhone] PERPH
ON PERPH.BusinessEntityID = PER.BusinessEntityID
INNER JOIN [Person].[PhoneNumberType] PNT
ON PNT.PhoneNumberTypeID = PERPH.PhoneNumberTypeID
 
-----------------------------------------------
--     7.5 - JOINS OUTER
-----------------------------------------------
SELECT *
FROM [Person].[Person] PER
LEFT OUTER JOIN [Person].[PersonPhone] PERPH
ON PERPH.BusinessEntityID = PER.BusinessEntityID
LEFT OUTER JOIN [Person].[PhoneNumberType] PNT
ON PNT.PhoneNumberTypeID = PERPH.PhoneNumberTypeID

-----------------------------------------------
--     8 - CREATE VIEW
-----------------------------------------------
 
IF EXISTS (SELECT * FROM sys.views WHERE name = 'vEmployeeDepartment2' AND schema_id = SCHEMA_ID('HumanResources'))
       DROP VIEW [HumanResources].[vEmployeeDepartment2]
GO
 
CREATE VIEW [HumanResources].[vEmployeeDepartment2]
AS
 
SELECT
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]
    ,d.[Name] AS [Department]
    ,d.[GroupName]
     ,edh.[StartDate]
FROM [HumanResources].[Employee] e
       INNER JOIN [Person].[Person] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
    ON e.[BusinessEntityID] = edh.[BusinessEntityID]
    INNER JOIN [HumanResources].[Department] d
    ON edh.[DepartmentID] = d.[DepartmentID]
WHERE edh.EndDate IS NULL
 
GO
 
-----------------------------------------------
--     9 - SELECT FROM VIEW
-----------------------------------------------
SELECT * FROM  [HumanResources].[vEmployeeDepartment2]
 
-----------------------------------------------
--     10 - CREATE TEMP TABLES
-----------------------------------------------
IF OBJECT_ID('tempdb..#TEMP_EMPL_DEP') IS NOT NULL
--THEN IT EXISTS
       DROP TABLE #TEMP_EMPL_DEP
GO    
SELECT * INTO #TEMP_EMPL_DEP FROM  [HumanResources].[vEmployeeDepartment2]
SELECT * FROM #TEMP_EMPL_DEP
 
-----------------------------------------------
--     11 - UPDATE TEMP TABLE
-----------------------------------------------
UPDATE #TEMP_EMPL_DEP
       SET Title = ''
       WHERE Title IS NULL
      
SELECT * FROM #TEMP_EMPL_DEP
 
-----------------------------------------------
--     12 - DELETE FROM TEMP TABLE
-----------------------------------------------
DELETE FROM #TEMP_EMPL_DEP
WHERE BusinessEntityID = 1
 
SELECT * FROM #TEMP_EMPL_DEP
 
-----------------------------------------------
--     13 - TRUNCATE TEMP TABLE
-----------------------------------------------
TRUNCATE TABLE  #TEMP_EMPL_DEP

SELECT * FROM #TEMP_EMPL_DEP
 
-----------------------------------------------
--     14 - INSERT
-----------------------------------------------
IF OBJECT_ID('tempdb..#Department') IS NOT NULL
--THEN IT EXISTS
       DROP TABLE #Department
GO    
CREATE TABLE #Department
(
       [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
       [Name] VARCHAR(50) NOT NULL,
       [GroupName] VARCHAR(50) NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
 )
 INSERT INTO #Department
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
SELECT [Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [HumanResources].[Department]
SELECT * FROM #Department
ORDER BY 2,3
--ORDER BY [Name], [GroupName]

-----------------------------------------------
--     15 - DROP TABLE (#TEMP)
-----------------------------------------------
DROP TABLE #Department
 
-----------------------------------------------
--     16 - LIKE STATEMENT
-----------------------------------------------
SELECT * FROM #Department
WHERE [Name] LIKE 'Production%'
 
SELECT * FROM #Department
WHERE [GroupName] NOT LIKE '%and%'
 
-----------------------------------------------
--     17 - UNION
-----------------------------------------------
SELECT 1 as [Type]
       ,[Name] AS DepartmentName
      ,[GroupName] AS GroupName
FROM [HumanResources].[Department]
WHERE [GroupName] = 'Sales and Marketing'
UNION
SELECT 2 as [Type]
              ,[Name] AS DepartmentName
      ,[GroupName] AS GroupName
FROM [HumanResources].[Department]
WHERE [GroupName] = 'Research and Development'
 
-----------------------------------------------
--     18 - AND / OR
-----------------------------------------------
SELECT [Name] AS DepartmentName
      ,[GroupName] AS GroupName
FROM [HumanResources].[Department]
WHERE [GroupName] = 'Research and Development'
       AND [Name] = 'Engineering'
 
SELECT [Name] AS DepartmentName
      ,[GroupName] AS GroupName
FROM [HumanResources].[Department]
WHERE [GroupName] = 'Research and Development'
       AND (
                     [Name] = 'Engineering'
                     OR
                     [Name] = 'Tool Design'
              )

-----------------------------------------------
--     19 - COMMENTS
-----------------------------------------------
--LINE COMMENT
--SELECT * FROM [HumanResources].[Department]
 
--BLOCK COMMENT
/*
       THIS CODE PULLS DEPARTMENT DATA
       FROM HUMAN RESOURCE SCHEMA
       FROM ADVENTUREWORKS DATABASE
       ON THE JONT530 SERVER
       JONB 10/22/2013
*/

SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [HumanResources].[Department]
 
 
-----------------------------------------------
--     20 - STORED PROCEDURE / CTE
-----------------------------------------------
 
/*
USE [AdventureWorks2012]
GO

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;
 
    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this --ate.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE b.[ProductAssemblyID] = @StartProductID
            AND @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)
END;

GO

*/

--EXEC [dbo].[uspGetBillOfMaterials] 895, '06/26/2004'
 
-----------------------------------------------
--     21 - CONVERT STRING TO DATE
-----------------------------------------------
SELECT '10/26/2013' AS TodaysDate
SELECT CONVERT(DATETIME,'10/26/2013') AS TodaysDate
SELECT DatePart(M,CONVERT(DATETIME,'10/26/2013')) AS MonthOfDate
SELECT GETDATE() AS TodaysDate
 
-----------------------------------------------
--     22 - CASE STATEMENT
-----------------------------------------------
DECLARE @DATEPARAMETER DATETIME
SET @DATEPARAMETER = GETDATE()
SET @DATEPARAMETER =DATEADD(YYYY,1, GETDATE())
SET @DATEPARAMETER =DATEADD(YYYY,-1, GETDATE())
SELECT CASE
       WHEN DatePart(YYYY,CONVERT(DATETIME,@DATEPARAMETER)) = 2012 THEN 'LAST YEAR'
       WHEN DatePart(YYYY,CONVERT(DATETIME,@DATEPARAMETER)) = 2013 THEN 'CURRENT YEAR'
       WHEN DatePart(YYYY,CONVERT(DATETIME,@DATEPARAMETER)) = 2014 THEN 'NEXT YEAR'
       ELSE 'OTHER YEAR'
       END
       AS WhatYear

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Top 22 Complaints by Number