continuous employee - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

continuous employee

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2015-03-10 : 15:15:54

I need to find the employees who are continous there for 5 months without any breakup between months.

Query Tried:


Declare @CE Table
(
Employee INT,
StDate datetime,
EndDate datetime
)

insert into @CE(Employee,StDate,EndDate)
select 1,'2014-01-01 00:00:00:000','2014-01-31 00:00:00:000'
union all
select 1,'2014-02-01 00:00:00:000','2014-02-28 00:00:00:000'
union all
select 1,'2014-03-01 00:00:00:000','2014-03-31 00:00:00:000'
union all
select 1,'2014-04-01 00:00:00:000','2014-04-30 00:00:00:000'
union all
select 1,'2014-05-01 00:00:00:000','2014-05-31 00:00:00:000'

union all

select 2,'2014-01-01 00:00:00:000','2014-01-31 00:00:00:000'
union all
select 2,'2014-03-01 00:00:00:000','2014-03-31 00:00:00:000'
union all
select 2,'2014-04-01 00:00:00:000','2014-04-30 00:00:00:000'
union all
select 2,'2014-05-01 00:00:00:000','2014-05-31 00:00:00:000'


union all

select 3,'2014-05-01 00:00:00:000','2014-05-31 00:00:00:000'
union all
select 3,'2014-06-01 00:00:00:000','2014-06-30 00:00:00:000'
union all
select 3,'2014-07-01 00:00:00:000','2014-07-31 00:00:00:000'
union all
select 3,'2014-08-01 00:00:00:000','2014-08-31 00:00:00:000'
union all
select 3,'2014-09-01 00:00:00:000','2014-09-30 00:00:00:000'

select * from @CE
order by Employee

I need to get the final result set as:

Employee
--------
1
3
can you please help me to find the query for this?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-10 : 16:36:10

This worked for me:


with addlag as 
(
	select *
		 , lg = case datediff(day, isnull(lag(enddate) over(partition by employee order by stdate), stdate-1), stdate)
					when 1 then row_number() over (partition by employee order by stdate) else 1 end
	from @ce
)

select employee, min(stdate), max(enddate), count(*) n
from 
(
	select employee, stdate, enddate,
		   seq = row_number() over (partition by employee order by stdate, enddate) - lg
	from addlag
) _
group by employee, seq
having count(*) = 5

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2015-03-10 : 17:12:50

Thanks for your immediate reply.

lag is not regcognized built in function error is thrown.
im using sqlserver 2008 version

quote:

Originally posted by gbritton

This worked for me:


with addlag as 
(
	select *
		 , lg = case datediff(day, isnull(lag(enddate) over(partition by employee order by stdate), stdate-1), stdate)
					when 1 then row_number() over (partition by employee order by stdate) else 1 end
	from @ce
)

select employee, min(stdate), max(enddate), count(*) n
from 
(
	select employee, stdate, enddate,
		   seq = row_number() over (partition by employee order by stdate, enddate) - lg
	from addlag
) _
group by employee, seq
having count(*) = 5

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-10 : 18:32:09

Pity you didn't say you were on 2008. Query is a little harder but same idea.

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2015-03-10 : 18:37:21

can you give some hint to derive this logic?

quote:
Originally posted by gbritton

Pity you didn't say you were on 2008. Query is a little harder but same idea.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-10 : 19:21:05

the LAG function works by looking at the previous record in the given sort order. Say I have a table foo. I can do something like this:


with foobar as
(
    select *, rn=row_number() over(partition by something order by something_else)
    from foo
)
select f1.col1
     , case when f2.col2 = f1.col2 
           then 'same in previous row' 
           else 'different from previous row' 
       end as same_different
from foo f1
join foo f2 on f2.rn = f1.rn-1

YOu can use the same sort of thing to do the equivalent of the LAG function. It just runs slower (because of the self-join)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources