Saturday 21 March 2015

Write Query to get top N number of Employee of each department whose salary is maximum.

     One of my friend was facing issue in his development so I worked and solved his issue.I will try to share his requirement with respect to Employee table.
     His requirement was to show top 3 employee of each department having maximum salary.It seems to be bit confusing but it  is  not.
 Code :
We have following table :

Create Table Employee
(
EmployeeID Int Identity Primary Key,
EmployeeName Varchar(10),
DepartmentID Int ,
Salary Money
)

--Insert data

 INSERT INTO Employee
            (EmployeeName,
             DepartmentID,
             Salary)
VALUES      ('S12',
             1,
             2000),
            ('S13',
             1,
             3000),
            ('S14',
             1,
             4000),
            ('S15',
             1,
             5000),
            ('S16',
             1,
             6000),
            ('S17',
             1,
             7000),
            ('S18',
             1,
             8000),
            ('S19',
             1,
             9000),
            ('S110',
             1,
             10000),
            ('S11',
             2,
             1000),
            ('S12',
             2,
             2000),
            ('S13',
             2,
             3000),
            ('S14',
             2,
             4000),
            ('S15',
             2,
             5000),
            ('S16',
             2,
             6000),
            ('S17',
             2,
             7000),
            ('S18',
             2,
             8000),
            ('S19',
             2,
             9000),
            ('S110',
             2,
             10000),
            ('S11',
             3,
             1000),
            ('S12',
             3,
             2000),
            ('S13',
             3,
             3000),
            ('S14',
             3,
             4000),
            ('S15',
             3,
             5000),
            ('S16',
             4,
             6000),
            ('S17',
             4,
             7000),
            ('S18',
             4,
             8000),
            ('S19',
             4,
             9000),
            ('S110',
             4,
             10000)


Now we have data  as follow

Now question is ,really can we get it through query ?

Answer is yes.

Code :

;WITH cte
     AS (SELECT *,
                (SELECT Count(*)
                 FROM   employee
                 WHERE   salary>=e.salary
                        AND departmentID = e.departmentID) AS count
         FROM   Employee e)

SELECT *
FROM   cte
WHERE count <= 3

Logic is very simple but for understanding it will take some time.I will try to explain step by step.
1.We have main  table Employe. We will add one column in CTE as count.
2.Count will be calculated based on corelated subquery..
3.It will check how many employee of that department are getting equal are more than that  salary .
4.How value for  first emplyeeId is calculated with respect to DepartmentId
 -->
--First Row
  select *,(select  count(*) from Emplyee b where b.salary <=1000  and b.departmentID=a.departmentID
)

so for first row count will come as 10
EmployeeID EmployeeName DepartmentID Salary count
 1                 S11                           1                1000.00 10

--Second Row
  select *,(select  count(*) from Emplyee b where b.salary <=2000
and b.departmentID=a.departmentID
)

so for second row count will come as 9
EmployeeID EmployeeName DepartmentID Salary count
2                  S12                   1                 2000.00 9

 Like this all count  will be calculated with respect to departmentId
If we want to find out top 5 salary of employee we  only need to replace 3 by 5 in above query as follow
SELECT *
FROM   cte
WHERE count <= 5
  Same thing we can achieve through ranking function 
;
with cte as(
select * , rank() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee  emp
)
select * From cte where cn<=3 order by DepartmentID  asc



;
with cte as(
select * , row_number() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee emp
)
select * From cte where cn<=3 order by DepartmentID  asc




;
with cte as(
select * , dense_rank() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee emp
)
select * From cte where cn<=3 order by DepartmentID  asc


Write program in SQL Server to print pyramid

                    Today,I was reading one of  programming language book .I saw lot of program with respect to pyramids .I always consider it as game of printing some number or  special character.
So I thought why don't I utilize my Saturday with that games.Now we considering that special character as '*'.Lets play with this.We will start with printing pyramids .

Output:
1.



Script :

DECLARE @lclMaxLevel INT=5
DECLARE @lclPrintCount INT =0

WHILE @lclMaxLevel > 0
  BEGIN
      PRINT Space(@lclMaxLevel)
            + Replicate('*', @lclPrintCount)
            + Replicate('*', @lclPrintCount+1)

      SET @lclMaxLevel=@lclMaxLevel - 1
      SET @lclPrintCount=@lclPrintCount + 1
  END

2.
Script :
DECLARE @lclMaxLevel INT=5
DECLARE @lclPrintCount INT =0

WHILE @lclMaxLevel > 0
  BEGIN
      PRINT Space(@lclMaxLevel)
            + Replicate('*', @lclPrintCount+1)

      SET @lclMaxLevel=@lclMaxLevel - 1
      SET @lclPrintCount=@lclPrintCount + 1
  END

  Please give your feedback if you liked it.

Nested Transaction -How it is important.

 Recently I was  encountered with issue  ,it was related with Transaction .I can share code with respect to adventurework database
Script:

SELECT Count(*)
FROM   SALESLT.SalesOrderDetail
WHERE  SalesOrderID IN ( 71774, 71780 )

BEGIN TRY
--Added try catch to avoid exception
    BEGIN TRAN
--First Transaction
    BEGIN TRAN
--Second Transaction
    BEGIN TRAN
--Third Transaction

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 5000
    WHERE  SalesOrderID = 71774

    COMMIT -- First Transaction End

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 1000
    WHERE  SalesOrderID = 71780

    COMMIT -- Second Transaction End

    UPDATE SALESLT.SalesOrderDetail
    SET    UnitPrice = 1000
    WHERE  SalesOrderID = 71781
--No Entry in Table So it will raise manual error

    IF @@ROWCOUNT <= 1
      RAISERROR('Call Rollback',16,1)
--it will go to Catch block

    COMMIT
END try

BEGIN catch
    PRINT 'Call Rollback'

    ROLLBACK
--Data will be rollback
END Catch

SELECT Count(*)
FROM   SALESLT.SalesOrderDetail
WHERE  SalesOrderID IN ( 71774, 71780 )

   Result of both query will be same as all transaction are rollback.
  Many of us can confuse with respect to result.




Summary:
   It has total  three transaction  out of which two transaction succeed  and one is failed .  Though inner transaction are committed without error but outer transaction got rollback..So all data got rollback. While implementing transaction  if it is required we need take care of this case.



First Database In Sql Server