Friday, 22 December 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

Sunday, 17 December 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 Professional
--------------------------------------------------------------------------------------------------------

Saturday, 16 December 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, Our aim is 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 the 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 DimTablePivot (Sum( [NetBookings] ) 
For [FiscalYear] IN ( [2017-12-16] ,[2016-12-16] )) AS P

Result :
Segment        CurFY  PrevFY
Commercial     4500    5500


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

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts