Friday, December 22, 2017

How to use Self Join to retrieve Supervisor Name based on Supervisor Id from Employee Table in SQL Server

SQL Server Self Join to retrieve Supervisor Name based on Supervisor Id from same Employee Table
Joining the table to itself is called Self Join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. 
Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.
Based on Scenario, a Self Join could be an Outer Join or an Inner Join.
Scenario :
Suppose we have an Employee Table with Employee and Supervisor Id details. Here, based on the Supervisor Id we need to generate a Supervisor Name from the same Table.
SELECT [EmpId],[EmpName],[JobTitle],[SupervisorId] FROM [dbo].[Tbl_Emp]

GO

We can generate a Supervisor Name from the same table by using the Self Join as follows..

A) Self Join
Select   T1.[EmpId],T1.[EmpName]
            ,T1.[JobTitle],T1.[SupervisorId]
            ,T2.[EmpName] as [SupervisorName]
            ,T2.[JobTitle] as [SuperJobTitle]

From   Tbl_Emp T1 , Tbl_Emp T2
Where T1.SupervisorId=T2.EmpId

B) Self Join using Inner Join
Select T1.[EmpId],T1.[EmpName]
          ,T1.[JobTitle],T1.[SupervisorId]
          ,T2.[EmpName] as [SupervisorName]
          ,T2.[JobTitle] as [SuperJobTitle]

From Tbl_Emp T1 Inner Join Tbl_Emp T2
         On T1.SupervisorId=T2.EmpId

Result :

C) Self Join using Left  Outer Join
Select T1.[EmpId],T1.[EmpName]
          ,T1.[JobTitle],T1.[SupervisorId]
          ,T2.[EmpName] as [SupervisorName]
          ,T2.[JobTitle] as [SuperJobTitle]

From Tbl_Emp T1 Left Join Tbl_Emp T2
         On T1.SupervisorId=T2.EmpId

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Sunday, December 17, 2017

How to Calculate the Minimum, Maximum and Average Salary by Department in SQL Server

SQL Query to Calculate the Minimum, Maximum and Average Salary by Department
Scenario :
Suppose we have an Employee Table and its related Department Table as follows..
USE [EmpDB]
GO


SELECT [Emp_Id], [EmpName] ,[JobTitle] ,[Dept_Id]  ,
[EmpAge] ,[Experience] ,[NetSal]
FROM [dbo].[Tbl_Emp]
GO


SELECT [DeptId]
      ,[DeptName]
  FROM [dbo].[Tbl_Dept]
GO

Now we will Calculate the Minimum, Maximum and Average Salary by Department as follows :
1) Calculating Average of Salary drawing by the Employees whose JobTitle='Financial Analyst' from Emp_Table.

Select JobTitle,Count(Emp_Id) CountOfEmp, Sum(NetSal) NetSal,
Round(AVG ( NetSal) ,2) AvgNetSal
     From Tbl_Emp
     Where JobTitle='Financial Analyst'
     Group By JobTitle

Result :

2) Calculating the Minimum, Maximum, Net Salary and Average Salary drawing by Department

Select D.DeptName, Count(E.Emp_Id) CountOfEmp,Sum(NetSal) NetSalDrawing,
    Round(Avg(E.NetSal),0) AvgNetSal, Min(E.NetSal) MinSal,Max(E.NetSal) MaxSal
    From Tbl_Emp E Inner Join Tbl_Dept D
 On E.Dept_Id=D.DeptId
 Group By D.DeptName

Result :


3) Calculating AVG of Salary drawing by the Employees whose Age Group between (>35 and <45)

Select D.DeptName,S1.CountOfEmp, S1.NetSal ,S1.AvgNetSal From
   (Select Dept_Id, Count(Emp_Id) CountOfEmp, Sum(NetSal) NetSal,
     Round(AVG ( NetSal) ,2) AvgNetSal
     From Tbl_Emp
     Where EmpAge Between 36 and 46
     Group By Dept_Id
   ) S1
 Inner Join Tbl_Dept D
 On S1.Dept_Id= D.DeptId

Result :

4) Calculating AVG of  Salary drawing by the Employees whose Age Group between
(>35 and <45) and Experience >=5

Select Dept_Id,D.DeptName,S1.CountOfEmp, S1.NetSal ,S1.AvgNetSal From
   (Select Dept_Id, Sum(NetSal) NetSal, Round(AVG ( NetSal) ,2) AvgNetSal ,
     Count(Emp_Id) CountOfEmp
     From Tbl_Emp
     Where (EmpAge Between 36 And  46) and [Experience]>=5
     Group By Dept_Id
   ) S1
 Inner Join Tbl_Dept D
 On S1.Dept_Id= D.DeptId

Result :


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
-------------------------------------------------------------------------------------------------------
-

Saturday, December 16, 2017

How to Transform Rows into Columns from a Table in SQL Server

How to Pivot the Data using various methods in SQL Server
In the Field of Business Intelligence, to enable the Business users with an understanding of data created and stored by business systems. We can do this by providing the Insights to the Business users to know how the business is performing.
Sample Table : Select * From Tbl_Sample


A Tabular report may not provide the correct Insights to the Business for Analysis, so that we need to transform that data into a Pivot or Cross Tab format.

We can do this using various options in SQL Server.

1) Using T-SQL Pivot Operator:

SELECT *FROM
   (
   SELECT
          [RegionName],
          [ProdName],      
          [FiscalQtr],
          [NetSales]
   FROM [dbo].[Tbl_Sample]
   ) AS PivotSource
PIVOT(SUM([NetSales]) 
FOR [FiscalQtr] IN([Q1-2016], [Q2-2016], [Q3-2016], [Q4-2016])
    ) AS PivotTable

GO

Result:

2) Using XML PATH Option:
DECLARE @vColumns NVARCHAR(MAX), @vQuery NVARCHAR(MAX);

SET @vColumns = STUFF(
          (
         SELECT DISTINCT ','+QUOTENAME(S1.[FiscalQtr])
         FROM [dbo].[Tbl_Sample]
S1
         FOR XML PATH(''), TYPE).value('.', 'nvarchar(Max)'),
        1, 1, '');
SET @vQuery = 'SELECT [RegionName],[ProdName],'+@vColumns+' From (
       SELECT [RegionName],[ProdName],[FiscalQtr],[NetSales]
       FROM [dbo].[Tbl_Sample]
      ) PivotSource PIVOT (Max(NetSales) FOR [FiscalQtr] IN ('+@vColumns+')) P';

EXECUTE (@vQuery);
GO
Result :


3) Using Dynamic SQL Query:
DECLARE @vColumns NVARCHAR(MAX), @vSQL NVARCHAR(MAX);
SET @vColumns = '';
SELECT @vColumns+=',P.'+QUOTENAME([FiscalQtr]) FROM
(
    SELECT [FiscalQtr]
    FROM [dbo].[Tbl_Sample] as S2
    GROUP BY [FiscalQtr]

) as S1;
SET @vSQL ='SELECT [RegionName],[ProdName],'+STUFF(@vColumns, 1, 1, '')+
' FROM (
   SELECT [
RegionName],[ProdName],[FiscalQtr],[NetSales]
   FROM [dbo].[Tbl_Sample]) AS PivotSource 
PIVOT (SUM([NetSales]) FOR [FiscalQtr] IN
    ('+STUFF(REPLACE(@vColumns, ',P.[', ',['), 1, 1, '')+')) AS P;';
Print (@vColumns)
Print (@vSQL)
EXEC sp_executesql @vSQL

GO

Result:

-------------------------------------------------------
STRING Functions:
QUOTENAME:
The QuoteName is String Function which is used to Enclose a String with some
Quote( Eg : ,,'',{},[],<>)
Eg :
Declare @String varchar(30) = 'Sample String'
Select 'Default' as QuoteType , QUOTENAME(@String) QuotedValue
Union All Select 'Tag', QUOTENAME(@String,'<')
Union All Select 'Parentheses'  , QUOTENAME(@String,'(')
Union All Select 'CurlyBraces' , QUOTENAME(@String,'{')
Union All Select 'Brackets' , QUOTENAME(@String,'[')
Union All Select 'Tick/SingleQuotes' , QUOTENAME(@String,'''')
Union All Select 'Dot' , QUOTENAME(@String,'.')

GO
Result :
-------------------------------------------------------
REPLACE:
The REPLACE is a string Function which Replaces each Occurrence of a string from the Main String with a new string.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select REPLACE (@String,'S','abc') Replaced_String
Result :
Replaced_String
abcome#*abcample*abctring
-------------------------------------------------------
STUFF :
The STUFF is a string Function which Replaces the part of a string from the Main String with a new string, based on the Starting Position and No.of Characters to Replace.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select STUFF (@String,7,3,'Dim') Stuffed_String
Result :
Stuffed_String

Some#*Dimple*String
-------------------------------------------------------
CHAR INDEX:
The CHAR INDEX is a string Function which returns the Position of a SubString in the Main String based on the Starting Postion.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select CHARINDEX('S',@String,3) Char_Index
Result :
Char_Index
      7
------------------------------------------------------
SUBSTRING:
The SUBSTRING is a string Function which extracts the Portion of a string from the Main String based on the Starting Position and No.of Characters to Extract.
Eg :
Declare @String varchar(30) = 'Some#*Sample*String'
Select SUBSTRING(@String,7,3) Sub_String
Result :
Sub_String
    Sam
-------------------------------------------------------
Note :
Simple Example to Transpose Data:
 OrderDate      Segment         NetBookings
2017-12-16   Commercial        4500
2016-12-16   Commercial        5500

Transposing Data :
Select [Segment],[2017-12-16] as [CurFY],[2016-12-16] as [PrevFY]
From DimTable Pivot (Sum( [NetBookings] ) 
For [FiscalYear] IN ( [2017-12-16] ,[2016-12-16] )) AS P

Result :
Segment        CurFY  PrevFY
Commercial     4500    5500

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
-------------------------------------------------------------------------------------------------------
-

Friday, December 15, 2017

How to Calculate the Cumulative Sum or Running Total in SQL Server

SQL Query to Calculate the Cumulative Sum or Running Total
Scenario :
Suppose we have a Table "TblSample" as follows..


Now we can Calculate the Cumulative Sum or Running Total on this Table in various ways.
1) Using Sum () Function with Over () Clause :
This is the simplest method to calculate the cumulative sum/running total in SQL Server.
A) Cumulative Sum based on Id Column :
     Select *, Sum(NetSales) Over ( Order by ID ) As CumSum_ById
     From [dbo].[TblSample]
Result :
B) Cumulative Sum based on Id Column and Partition By Region: 
    Select *, Sum(NetSales) 
                  Over ( Partition By Region Order By ID ) As CumSum_ById_Region
     From [dbo].[TblSample]
Result :

2) Using 'Correlated Scalar Query' :
A)  Cumulative Sum based on Id Column
      Select * , (  Select Sum(NetSales) From [TblSample] S2
                        Where S2.ID<= S1.ID
                      )  Cumulative_Net_ById
      From [dbo].[TblSample] S1
     
Order BY S1.ID
     GO
Result :
B Cumulative Sum based on Partition By specific Columns
     Select * , (  Select Sum(NetSales) From [TblSample] S2
                   Where S2.ID<= S1.ID
                )  Cumulative_Net_ById,
                (  Select Sum(NetSales) From [TblSample] S2
                   Where S2.ID<= S1.ID and
                   S2.Region=S1.Region
                )  Cumulative_Net_ByRegion,

               (  Select Sum(NetSales) From  [TblSample] S2
                  Where S2.ID<= S1.ID and
                  S2.Region=S1.Region and
                  S2.Product=S1.Product
               ) 
Cumulative_Net_ByProduct
      From [dbo].[TblSample] S1
      Order BY S1.ID,S1.Region,S1.Product
      GO
Result :


3) Using 'Self Join Query' : 

Cumulative Sum based on Id Column
Select S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales,
           Sum(S2.NetSales)Cumulative_Net
      
From  [DimSample] S1,
                 [DimSample]
S2
       Where S2.ID<= S1.ID
Group By S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales
Order BY S1.ID


4) Using 'Common Table Expressions' : 
Suppose if we don't have 'ID' Column in the Table , then we can generate the 'ID' Column using the Common Table Expressions (CTE) , and the we can Calculate the Cumulative Sum or Running Total as follows..
Select * From [dbo].Tbl_Sample
Calculating Cumulative Sum based on Partition By specific Columns using Common Table Expressions (CTEs) :
WITH S1 AS  '--First CTE
 (
   SELECT Row_Number() Over (Order By Region) RowId,* FROM [Tbl_Sample]
 ),
 S2  AS  '--Second CTE
 (
   SELECT Row_Number() Over (Order By Region) RowId,*FROM [Tbl_Sample]
 )
Select * , (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId
    )  Cumulative_Net_ById,

    (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId and
       S2.Region=S1.Region
    )  Cumulative_Net_ByRegion,

    (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId and
       S2.Region=S1.Region and
       S2.Product=S1.Product
    ) 
Cumulative_Net_ByProduct 

From S1
Order BY S1.RowId,S1.Region,S1.Product

GO       

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog