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(
SET IDENTITY_INSERT mytable ON
Insert into TestTable(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
View Complete Post