query to display no of employees who are getting week off in same day

Question

Post reply

query to display no of employees who are getting week off in same day

naresh0407 93367

SSC-Addicted

Points: 479
More actions
October 13, 2010 at 7:05 am

#227839

Hi All,
This is my table.
I have 2 weeks data.I want to find if the employee 101 and 102 are getting weekOff in the sameday for across last 2 weeks.Find no of employees who are getting weekoff in the same day.
Please send me query for this.
create table Testing(empno int,MatchingOff varchar(10),atnDate datetime)
Insert into Testing(empno,MatchingOff,atnDate)
select '101','Off','8/1/2010' union all
select '101','17:00','8/2/2010' union all
select '101','17:00','8/3/2010' union all
select '101','17:00','8/4/2010' union all
select '101','17:00','8/5/2010' union all
select '101','17:00','8/6/2010' union all
select '101','17:00','8/7/2010' union all
select '102','17:00','8/1/2010' union all
select '102','off','8/2/2010' union all
select '102','17:00','8/3/2010' union all
select '102','17:00','8/4/2010' union all
select '102','off','8/5/2010' union all
select '102','17:00','8/6/2010' union all
select '102','17:00','8/7/2010' union all
select '101','Off','8/8/2010' union all
select '101','17:00','8/9/2010' union all
select '101','17:00','8/10/2010' union all
select '101','17:00','8/11/2010' union all
select '101','17:00','8/12/2010' union all
select '101','17:00','8/13/2010' union all
select '101','17:00','8/14/2010' union all
select '102','17:00','8/8/2010' union all
select '102','off','8/9/2010' union all
select '102','17:00','8/10/2010' union all
select '102','17:00','8/11/2010' union all
select '102','off','8/12/2010' union all
select '102','17:00','8/13/2010' union all
select '102','17:00','8/14/2010'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Start from a calendar table and filter between start date and end date

Left join that to your "days off" table based on the date only.

Group by date

having count(*) > 1

Now if you need more data, put that first query in a cte and join it back on the base table to get a list of the employees with conflicting holidays.

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 2

Hi,

I am not clear,can u elaborate it.

I should get data from single table.Should i need to use joins.

This is my table column.I should check MatchingOff if the matchingOff column is off then i should check next week data if the Off is falling on same day.

EmpNo atnDate MatchingOff

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'm sure that's not your final query because I've always had to present this in a calendar form to the final user but that gives you an idea :

create table Testing(empno int,MatchingOff varchar(10),atnDate datetime)

Insert into Testing(empno,MatchingOff,atnDate)

select '101','Off','8/1/2010' union all

select '101','17:00','8/2/2010' union all

select '101','17:00','8/3/2010' union all

select '101','17:00','8/4/2010' union all

select '101','17:00','8/5/2010' union all

select '101','17:00','8/6/2010' union all

select '101','17:00','8/7/2010' union all

select '102','17:00','8/1/2010' union all

select '102','off','8/2/2010' union all

select '102','17:00','8/3/2010' union all

select '102','17:00','8/4/2010' union all

select '102','off','8/5/2010' union all

select '102','17:00','8/6/2010' union all

select '102','17:00','8/7/2010' union all

select '101','Off','8/8/2010' union all

select '101','17:00','8/9/2010' union all

select '101','17:00','8/10/2010' union all

select '101','17:00','8/11/2010' union all

select '101','17:00','8/12/2010' union all

select '101','17:00','8/13/2010' union all

select '101','17:00','8/14/2010' union all

select '102','17:00','8/8/2010' union all

select '102','off','8/8/2010' union all

select '102','17:00','8/10/2010' union all

select '102','17:00','8/11/2010' union all

select '102','off','8/12/2010' union all

select '102','17:00','8/13/2010' union all

select '102','17:00','8/14/2010'

SELECT TM.* FROM dbo.Testing TM INNER JOIN (

SELECT atnDate FROM dbo.Testing WHERE MatchingOff = 'off' GROUP BY atnDate HAVING COUNT(*) > 1) dtTM

ON TM.atnDate = dtTM.atnDate AND TM.MatchingOff = 'off'

DROP TABLE Testing