Finding value

blues · August 13, 2015, 8:58pm

Hi

Here is my data

Emp Name Emp ID Sal Year
John 1 100 2013
John 1 110 2014
John 1 120 2015
Sam 2 120 2013
Sam 2 110 2014
Sam 2 100 2015
Tim 3 120 2013
Tim 3 120 2014
Tim 3 100 2015
Steve 4 100 2013
Steve 4 150 2014
Steve 4 160 2015

I need to get value John based on the criteria that he has increase in the salary for 3 consecutive years

bitsmed · August 13, 2015, 9:22pm

Try this:

select distinct a.[emp name]
  from yourtable as a
       inner join yourtable as b
               on b.[emp id]=a.[emp id]
              and b.[year]=a.[year]+1
              and b.sal>a.sal
       inner join yourtable as c
               on c.[emp id]=b.[emp id]
              and c.[year]=b.[year]+1
              and c.sal>b.sal

hhrr · August 14, 2015, 7:55am

Hi

Please find one way of doing this .. using Lead function available from Sql Server 2012

/*
drop table #Temp
create table #Temp
(
EmpName varchar(100) null,
EmpID int null,
Sal int null,
Year int null
)
insert into #Temp select 'John',1,100,2013
insert into #Temp select 'John',1,110,2014
insert into #Temp select 'John',1,120,2015
insert into #Temp select 'Sam',2,120,2013
insert into #Temp select 'Sam',2,110,2014
insert into #Temp select 'Sam',2,100,2015
insert into #Temp select 'Tim',3,120,2013
insert into #Temp select 'Tim',3,120,2014
insert into #Temp select 'Tim',3,100,2015
insert into #Temp select 'Steve',4,100,2013
insert into #Temp select 'Steve',4,150,2014
insert into #Temp select 'Steve',4,160,2015

select * from #Temp
*/
;
WITH abc_cte
AS (
SELECT EmpName
,Year
,sal
,count(empname) OVER (PARTITION BY EmpName ORDER BY (SELECT 1)) AS count
,CASE WHEN (sal < lead(sal) OVER (PARTITION BY EmpName ORDER BY year)) THEN 1 ELSE 0 END AS diff
FROM #Temp
)
,def_cte
AS (
SELECT empname
,sum(diff) AS sum
,count
FROM abc_cte
GROUP BY empname,count
HAVING (count - 1) = sum(diff) )
SELECT * FROM def_cte

hhrr · August 14, 2015, 9:00am

Hi

I posted above

This is a simpler way of doing it

/*
drop table #Temp
create table #Temp
(
EmpName varchar(100) null,
EmpID int null,
Sal int null,
Year int null
)
insert into #Temp select 'John',1,100,2013
insert into #Temp select 'John',1,110,2014
insert into #Temp select 'John',1,120,2015
insert into #Temp select 'Sam',2,120,2013
insert into #Temp select 'Sam',2,110,2014
insert into #Temp select 'Sam',2,100,2015
insert into #Temp select 'Tim',3,120,2013
insert into #Temp select 'Tim',3,120,2014
insert into #Temp select 'Tim',3,100,2015
insert into #Temp select 'Steve',4,100,2013
insert into #Temp select 'Steve',4,150,2014
insert into #Temp select 'Steve',4,160,2015

select * from #Temp
*/
;

WITH abc_cte
AS (
SELECT EmpName
,CASE WHEN (sal > lead(sal) OVER (PARTITION BY EmpName ORDER BY year)) THEN 1 ELSE 0 END AS diff
FROM #Temp
)
select empname from abc_cte group by empname having sum(diff) = 0

indent preformatted text by 4 spaces

bitsmed · August 14, 2015, 5:38pm

Doesn't work if you add this to the sample data:

insert into #Temp select 'John',1,105,2012

even though 2013, 2014 and 2015 for John is consecutive years and salery is increasing.

Another thing - if sample data is:

insert into #Temp select 'John',1,100,2012
insert into #Temp select 'John',1,110,2014
insert into #Temp select 'John',1,120,2015

John will still be show, even though years are not consecutive

hhrr · August 15, 2015, 8:02am

Hi

I wrote new query .. this should work

drop table #Temp
create table #Temp
(
EmpName varchar(100) null,
EmpID int null,
Sal int null,
Year int null
)
insert into #Temp select 'John',1,105,2012
insert into #Temp select 'John',1,100,2013
insert into #Temp select 'John',1,110,2014
insert into #Temp select 'John',1,120,2015
insert into #Temp select 'Sam',2,120,2013
insert into #Temp select 'Sam',2,110,2014
insert into #Temp select 'Sam',2,100,2015
insert into #Temp select 'Tim',3,120,2013
insert into #Temp select 'Tim',3,120,2014
insert into #Temp select 'Tim',3,100,2015
insert into #Temp select 'Steve',4,100,2013
insert into #Temp select 'Steve',4,150,2014
insert into #Temp select 'Steve',4,160,2015

;
WITH abc_cte
AS (
SELECT EmpName
,CASE
WHEN year + 1 = isnull(Lead(year, 1) OVER (PARTITION BY empname ORDER BY year), 1) AND year + 2 = isnull(Lead(year, 2) OVER (PARTITION BY empname ORDER BY year), 1)
THEN CASE
WHEN sal < isnull(Lead(sal, 1) OVER (PARTITION BY empname ORDER BY year ), 1) AND isnull(Lead(sal, 1) OVER (PARTITION BY empname ORDER BY year), 1) < isnull(Lead(sal, 2) OVER (PARTITION BY empname ORDER BY year), 1)
THEN 'yes'
ELSE 'no'
END
END AS yesno
FROM #Temp
)
SELECT empname FROM abc_cte WHERE yesno = 'yes'

rds207 · August 15, 2015, 2:41pm

Thank you all !!

hhrr · August 19, 2015, 6:41am

Hi

I am posted above .. same thing again with nice formatting

;
WITH abc_cte
AS (
	SELECT EmpName
		,CASE 
			WHEN year + 1 = isnull(Lead(year, 1) OVER (PARTITION BY empname ORDER BY year), 1) 
			     AND 
			     year + 2 = isnull(Lead(year, 2) OVER (PARTITION BY empname ORDER BY year), 1)
				THEN CASE 
						WHEN sal  < isnull(Lead(sal, 1) OVER (PARTITION BY empname ORDER BY year), 1) 
						     AND 
						     isnull(Lead(sal, 1) OVER (	PARTITION BY empname ORDER BY year), 1) < isnull(Lead(sal, 2) OVER (PARTITION BY empname ORDER BY year), 1)
							THEN 'yes'
						ELSE 'no'
						END
			END AS yesno
	FROM #Temp
	)
SELECT empname
FROM abc_cte
WHERE yesno = 'yes'

/*
drop table #Temp
create table #Temp
(
EmpName varchar(100) null,
EmpID int null,
Sal int null,
Year int null
)
insert into #Temp select 'John',1,105,2012
insert into #Temp select 'John',1,100,2013
insert into #Temp select 'John',1,110,2014
insert into #Temp select 'John',1,120,2015
insert into #Temp select 'Sam',2,120,2013
insert into #Temp select 'Sam',2,110,2014
insert into #Temp select 'Sam',2,100,2015
insert into #Temp select 'Tim',3,120,2013
insert into #Temp select 'Tim',3,120,2014
insert into #Temp select 'Tim',3,100,2015
insert into #Temp select 'Steve',4,100,2013
insert into #Temp select 'Steve',4,150,2014
insert into #Temp select 'Steve',4,160,2015
*/