Querying employee salary along with maximum salary of his department

I found a very interesting question from one of the user of SQL group, his problem statement was

“I need to find each employee salary along with the max salary of his department with a single line query”

It is very easy to find using SQL ‘Over by with Partition By clause’

Lets demonstrate it with an example. Here I am creating Emp and Dept table and inserting few records in it.

create table Dept1

(dept_id int identity(1,1) primary key,

dname varchar(20))

Command(s) completed successfully.

create table Emp2

(emp_id int identity(1,1) primary key,

emp_name varchar(20),

dept_id int references Dept1(dept_id),

salary float)

Command(s) completed successfully.

insert into Dept1

select 'HR' union all

select 'IT' union all

select 'Account'

insert into Emp2

select 'Sudeepta', 1, 1000 union all

select 'Susmita', 1, 3000 union all

select 'Smruti', 3, 10000 union all

select 'Richa', 3, 8000 union all

select 'Sanjana', 1, 4000 union all

select 'Sweta', 2,4000 union all

select 'Deepa', 2,7000

Now since the data are there in the table, lets write query to find each employee salary along with the maximum salary for his department.

select e.emp_name, d.dname,e.salary, max(e.salary) over (partition by d.dept_id) MaxSalary from

Emp2 e inner join Dept1 d

on e.dept_id = d.dept_id

emp_name             dname                salary                 MaxSalary

-------------------- -------------------- ---------------------- ----------

Sudeepta             HR                   1000                   4000

Susmita              HR                   3000                   4000

Sanjana              HR                   4000                   4000

Sweta                IT                   4000                   7000

Deepa                IT                   7000                   7000

Smruti               Account              10000                  10000

Richa                Account              8000                   10000

(7 row(s) affected)

So here we can from the output we have salary for each employee along with the maximum salary of his department. We have simply used aggregated function max along with ‘over with partition by clause’ which is taking d.dept_id to partition the record to find the max salary of his department.

I can’t sleep …until I die…My passion SQL :)

Post Reference: Vikram Aristocratic Elfin Share

Pagination prior to SQL Server 2012

Pagination is common requirement for any application, where we pull data in chunks depending upon the page number and size of page. Here in this article we will see how we can achieve pagination through SQL server prior to SQ: Server 2012,

Here we are declaring a table Employee with three fields.

create table Employees (

empid int NOT NULL,

empname varchar(25) NOT NULL,

salary decimal(15,2)

)

Command(s) completed successfully.

Lets insert few data in the table.

declare @i int = 0

while @i < 10000

begin

insert into Employees

select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000

set @i = @i + 1

end

Now since our table is ready, its time to enter implement pagination, here in the below script, we have taken 2 variable, @PageNo, which indicate which page to see for data and second one is @Size which says the number of records in each page. Here we are using Row_Number ranking function, Approach is

• ·         We are giving a row number in sequence to all rows of table using ROW_NUMBER Rank function.
• ·         Then according to the value of page number and page size, we are extracting the records.
• ·         Eg. If @PageNo is 4 and row @PageSize is 5 then the row to return should  have 20-25 as row numbers.

declare @pageNo int

set @pageNo=1

declare @Size int

set @Size=5

select * from (select ROW_NUMBER ()  over ( order by empid asc ) as rowno , employees.* from Employees ) s

where s.rowno > (@pageNo*@Size) and s.rowno <=((@pageNo+1)*@Size)

rowno                empid       empname                   salary

-------------------- ----------- ------------------------- ---------

1                    10          abc5                      80.35

2                    11          abc9                      287.90

3                    12          abc5                      815.79

4                    13          abc5                      433.73

5                    14          abc4                      645.19

That’s all one need to do to implement Pagination in SQL Server Prior to SQL Server 2012.

Everything gets better when you have tea and SQL Server J

Post Reference: Vikram Aristocratic Elfin Share

Printing next 5 Sunday, Part-3

Read Prev Post   : Printing next 5 Sunday, Part-1

                         : Printing next 5 Sunday, Part-2

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

This is again another approach to find the next 5 Sunday, here we removed the CTE which we used in the last post, and replace it with Rank function.

declare @dt  datetime

declare @howMany integer =5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

select top (@howMany)  dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt) as weekdays,

DATENAME(dw, DATEADD(d, 7 * (cast(ROW_NUMBER() over (order by name) as int) - 1), @dt)) as Weekdays

from sys.objects

date                    Weekdays

----------------------- ------------------------------

2014-07-06 00:00:00.000 Sunday

2014-07-13 00:00:00.000 Sunday

2014-07-20 00:00:00.000 Sunday

2014-07-27 00:00:00.000 Sunday

2014-08-03 00:00:00.000 Sunday

Let’s dissect the code and understand each statement.

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

Lets divide this statement further more

cast(ROW_NUMBER() over (order by name) as int) – 1

This will give list of numbers starts from 0,1,2,3…….

Here below line will multiply 7*(0,1,2,3….) and add the number of days in multiple of 7 to the @dt date

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

So if the @dt is 2014-07-06

In first Iteration

select dateadd(d,7*(0),'2014-07-06')

-----------------------

2014-07-06 00:00:00.000

In second Iteration

select dateadd(d,7*(1),'2014-07-06')

-----------------------

2014-07-13 00:00:00.000

I am a coder, I have given my life to you, give me smile now! J

 

Post Reference: Vikram Aristocratic Elfin Share