How to find no of employees getting weekOff in the same day for across last 4,3,2 weeks

Question

Post reply

How to find no of employees getting weekOff in the same day for across last 4,3,2 weeks

naresh0407 93367

SSC-Addicted

Points: 479
More actions
October 14, 2010 at 7:40 am

#147284

Hi,
This is my table with employees data.Right now i gave only 1 employee data of 4 weeks.
How to find whether the employee is getting weekoff in the same day for all 4 weeks,3 weeks 2 weeks.similarly other employees and return count.
For example if the employee is getting weekOff on saturday for all 4 weeks then we can count it as 1.
I want reports like this.
Some employee may get weekOff for 2 days consistenly for 4 weeks say Saturday and SUnday then that result should be display in 3rd row.
If the employee is getting weekOff on saturday for last 3 weeks and 4th week on sunday then we should add count in 3weeks column i.e 2 row 3rd column.In 4th week column it will be 0.
ConsistencyOff Across4weeks 3weeks 2weeks
0 2% 2% 2%
1 98% 98% 98%
2 0% 0% 0%
CREATE TABLE Test1(
empno int,
MatchingOff varchar(10),
atnDate datetime
)
Insert into Test1(empno,MatchingOff,atnDate)
select '101','Off','8/1/2010' union all
select '101','16:00','8/2/2010' union all
select '101','14:00','8/3/2010' union all
select '101','17:00','8/4/2010' union all
select '101','16: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 '101','17:00','8/8/2010' union all
select '101','off','8/9/2010' union all
select '101','17:00','8/10/2010' union all
select '101','16: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','13:00','8/14/2010' union all
select '101','14:00','8/15/2010' union all
select '101','15:00','8/16/2010' union all
select '101','15:00','8/17/2010' union all
select '101','Off','8/18/2010' union all
select '101','16:00','8/192010' union all
select '101','15:00','8/202010' union all
select '101','17:00','8/21/2010' union all
select '101','14:00','8/22/2010' union all
select '101','15:00','8/23/2010' union all
select '101','15:00','8/24/2010' union all
select '101','15:00','8/25/2010' union all
select '101','Off','8/26/2010' union all
select '101','17:00','8/27/2010' union all
select '101','17:00','8/28/2010'

Viewing 8 posts - 1 through 7 (of 7 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Did you try running this code?

CREATE TABLE Test1(

empno int,

MatchingOff varchar(10),

atnDate datetime

)

SET IDENTITY_INSERT mytable ON

Insert into TestTable(empno,MatchingOff,atnDate)

There are 3 different tables referenced here... and it looks like the IDENTITY INSERT isn't needed since there isn't an identity column. The result is that your supplied code doesn't run... which makes it useless to us.

Do you have a PK on this table? Indexes?

Based on the sample data you have provided, what do you want for the results?

What do you define as a "week"? Is it the normal Sun-Sat calendar week, or do you use something else like Mon-Sun, Sat-Fri, etc.?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

Did you try the things suggestion on this other post you made on this same issue?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

Hi,

I modified the table structure.

I want find no of employees who are getting off on same day for across 4,3 and 2 weeks.Based on MatchinOff column i should whether the employee getting off on same day if yes return count.

If any doubts on my query please let me know

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Data copied and Before your editing at: Edited: Today @ 11:15 AM by naresh0407 93367

CREATE TABLE #Test1(Rownum INT IDENTITY(1,1),empno VARCHAR(3), MatchingOff varchar(10),

atnDate datetime)

/* added column Rownum to table for illustration purposes */

Insert into #Test1(empno,MatchingOff,atnDate)

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

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

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

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

select '101','16: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 '101','17:00','8/8/2010' union all

select '101','off','8/9/2010' union all

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

select '101','16: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','13:00','8/14/2010' union all

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

select '101','15:00','8/16/2010' union all

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

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

select '101','16:00','8/19/2010' union all --input error was 819/2010

select '101','15:00','8/20/2010' union all --input error was 820/2010

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

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

select '101','15:00','8/23/2010' union all

select '101','15:00','8/24/2010' union all

select '101','15:00','8/25/2010' union all

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

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

select '101','17:00','8/28/2010'

Running this code:

SELECT Rownum, DATENAME (dw ,atnDate ),MatchingOff,atnDate FROM #Test1

WHERE MatchingOff = 'off'

Results in:

Rownum(No column name)MatchingOffatnDate

1Sunday Off2010-08-01 00:00:00.000

9Monday off2010-08-09 00:00:00.000

18Wednesday Off2010-08-18 00:00:00.000

26Thursday Off2010-08-26 00:00:00.000

Please answer WayneS questions

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

Hi Wayne,

I tried it but i am not getting result.

In my table i have 4 weeks data i just want to display the no of employees who are getting off on same day for across last 4 weeks,3 weeks and 2 weeks.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Added some data (additional employees) to your original data:

select '102','Off','8/1/2010' union all --added

select '102','off','8/9/2010' union all --added

select '103','off','8/9/2010' union all --added

select '111','17:00','8/27/2010' union all --added

select '121','17:00','8/27/2010' union all --added

Then execited:

DECLARE @Nr AS DECIMAL(5,2)

SET @NR = (SELECT COUNT(DISTINCT(empno)) FROM #Test1)

SELECT DATENAME (dw ,atnDate ),MatchingOff,atnDate,COUNT(*)AS 'Emplyees off'

,@NR AS 'Total employees' ,(COUNT(*)/@Nr)*100 AS 'Percent off'

FROM #Test1 WHERE MatchingOff = 'off'

GROUP BY DATENAME (dw ,atnDate ),MatchingOff,atnDate

With this result:

MatchingOffatnDate Emplyees offTotal employeesPercent off

Sunday Off 2010-08-01 00:00:00.000 2 5.00 40.000000

Monday off 2010-08-09 00:00:00.000 3 5.00 60.000000

Wednesday Off2010-08-18 00:00:00.000 1 5.00 20.000000

ThursdayOff2010-08-26 00:00:00.000 1 5.00 20.000000

Is the above what you require?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

Hi,

CREATE TABLE #Test1(Rownum INT IDENTITY(1,1),empno VARCHAR(3), MatchingOff varchar(10),

atnDate datetime)

/* added column Rownum to table for illustration purposes */

Insert into #Test1(empno,MatchingOff,atnDate)

select '101','16:00','8/1/2010' union all

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

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

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

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

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

select '101','Off','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','16: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','Off','8/14/2010' union all

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

select '101','15:00','8/16/2010' union all

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

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

select '101','16:00','8/19/2010' union all --input error was 819/2010

select '101','15:00','8/20/2010' union all --input error was 820/2010

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

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

select '101','15:00','8/23/2010' union all

select '101','15:00','8/24/2010' union all

select '101','15:00','8/25/2010' union all

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

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

select '101','17:00','8/28/2010'

In the above table employee 101 is getting weekoff on same day for last 3 weeks(8/8/2010(sunday),8/15/2010(sunday),8/22/2010(sunday) and for 1st week it is on different day.So reports should be for across 4,3 and 2weeks like showm below.

when we consider last 4 weeks we are missing first week so 0% on across4 weks column and in across 3and 2weeks column it is added as 1%.

In the below reports Consistency 0,1,2 means 0 there is no consistency

1 means 1 day consistency(may be satrurday or any day) 2 days means Saturday and SUnday(it can be any 2 days).

`

2 means 2 days consistency in the above employee 101 is getting 2days consistent weekoff(saturday and sunday) for last 4 weeks except first week remaing 3 weeks we are getting weekoff consistently for 2 days across last 4 weeks.So 1% should be added in 3rd row like this.Across 4 weeks it is 0 because first week we are missing not falling on sameday.

across last 3 weeks we are getting conssitent week off on same 2 days(saturday and sunday)it can be any day.

2 18% 18% 18%

Consistency Across4 weeks 3weeks 2weeks

0 2% 2% 2%

1 80% 80% 80%

2 18% 18% 18%