I have EMP table in that I am storing the employee salary.
Employees are not getting fixed salary each month. We have requirements like get the previous 2
records and next 2 records form the EMP table when they get the salary 18k.
For example if a employee get the 18k sal in May so we need
to get the records of this employee March, April , May , June and July (previous
2 records and next 2 records).
See the table structure
CREATE TABLE [dbo].[Emp](
[EmpID]
[int] NULL,
[EmpName]
[nvarchar](50) NULL,
[EmpAdd]
[nvarchar](50) NULL,
[Dept]
[nvarchar](50) NULL,
[Sal]
[int] NULL,
[Date]
[date] NULL
)
|
Now I am inserting some records
insert into emp values (3,'Jay','Chennai','SE',12000,'1/31/2016')
insert into emp values (3,'Jay','Chennai','SE',16000,'2/29/2016')
insert into emp values (3,'Jay','Chennai','SE',18000,'3/31/2016')
insert into emp values (3,'Jay','Chennai','SE',13000,'4/30/2016')
insert into emp values (3,'Jay','Chennai','SE',11000,'5/31/2016')
insert into emp values (3,'Jay','Chennai','SE',12800,'6/30/2016')
insert into emp values (3,'Jay','Chennai','SE',13089,'7/31/2016')
insert into emp values (3,'Jay','Chennai','SE',12998,'8/31/2016')
insert into emp values (3,'Jay','Chennai','SE',15455,'9/30/2016')
insert into emp values (3,'Jay','Chennai','SE',14555,'10/31/2016')
insert into emp values (3,'Jay','Chennai','SE',15455,'11/30/2016')
insert into emp values (3,'Jay','Chennai','SE',17034,'12/31/2016')
|
I have inserted some more records for 3 employees.
select * from emp where sal=18000
|
For example EmpId =1 is getting 18k salary on April. Our
requirement is to get the previous 2 records and next 2 records (erg. Feb, March, April, May and June).
We can get the desirer output using below sql script
WITH CET_FinalResult
AS (select em.EmpID,DATEPART(MM,em.Date) as curMonth from emp em
where sal=18000)
, SecondCTE
AS (
select e.EmpID,e.EmpName,e.EmpAdd,e.Dept,e.Sal,e.Date from emp e
inner join CET_FinalResult
CR on CR.EmpID=e.EmpID
and DATEPART(MM,e.Date) in (CR.curMonth-2,CR.curMonth-1, CR.curMonth,CR.curMonth+1,CR.curMonth+2)
)
SELECT * FROM SecondCTE
|
See the output below
Get the desires output.
Thanks!
