query to find out no of employees who are getting weekOff on sameday

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • NareshN
    New Member
    • Aug 2010
    • 45
    #1

    query to find out no of employees who are getting weekOff on sameday

    Hi


    I have 4 weeks data of employees.Based on matchingOff column i need to find no of employee who are getting weekOff on sameday for all 4weeks.Suppose emp 101 is getting weekOff on 8/1/2010(Sunday) if the employee gets weekOf on sameDay(i.e sunday) for all 4 weeks then count will be 1.Simiraly how many employees are getting weekoff like that and increment count.

    WeekOff can be any day.If the employee gets weekoff on Monday for all 4 weeks then also count will be 1.Simiraly how many employees.

    Please tell me how to query for this.



    Insert into Testing1(empno, MatchingOff,atn Date)
    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','17:00',' 8/5/2010' union all
    select '102','17:00',' 8/6/2010' union all
    select '102','17:00',' 8/7/2010' union all
    select '103','17:00',' 8/1/2010' union all
    select '103','15:00',' 8/2/2010' union all
    select '103','15:00',' 8/3/2010' union all
    select '103','Off','8/4/2010' union all
    select '103','17:00',' 8/5/2010' union all
    select '103','17:00',' 8/6/2010' union all
    select '103','17:00',' 8/7/2010' union all
    select '104','17:00',' 8/1/2010' union all
    select '104','15:00',' 8/2/2010' union all
    select '104','15:00',' 8/3/2010' union all
    select '104','15:00',' 8/4/2010' union all
    select '104','Off','8/5/2010' union all
    select '104','17:00',' 8/6/2010' union all
    select '104','17:00',' 8/7/2010' union all
    select '101','Off','8/8/2010' union all
    select '101','16:00',' 8/9/2010' union all
    select '101','16:00',' 8/10/2010' union all
    select '101','Off','8/11/2010' union all
    select '101','16:00',' 8/12/2010' union all
    select '101','18:00',' 8/13/2010' union all
    select '101','16:00',' 8/14/2010' union all
    select '102','Off','8/8/2010' union all
    select '102','Off','8/9/2010' union all
    select '102','16:00',' 8/10/2010' union all
    select '102','Off','8/11/2010' union all
    select '102','16:00',' 8/12/2010' union all
    select '102','18:00',' 8/13/2010' union all
    select '102','16:00',' 8/14/2010' union all
    select '103','Off','8/8/2010' union all
    select '103','16:00',' 8/9/2010' union all
    select '103','16:00',' 8/10/2010' union all
    select '103','Off','8/11/2010' union all
    select '103','16:00',' 8/12/2010' union all
    select '103','18:00',' 8/13/2010' union all
    select '103','16:00',' 8/14/2010' union all
    select '104','Off','8/8/2010' union all
    select '104','16:00',' 8/9/2010' union all
    select '104','16:00',' 8/10/2010' union all
    select '104','17:00',' 8/11/2010' union all
    select '104','Off','8/12/2010' union all
    select '104','18:00',' 8/13/2010' union all
    select '104','16:00',' 8/14/2010' union all
    select '101','Off','8/15/2010' union all
    select '101','16:00',' 8/16/2010' union all
    select '101','16:00',' 8/17/2010' union all
    select '101','Off','8/18/2010' union all
    select '101','16:00',' 8/19/2010' union all
    select '101','18:00',' 8/20/2010' union all
    select '101','16:00',' 8/21/2010' union all
    select '102','Off','8/15/2010' union all
    select '102','Offs','8/16/2010' union all
    select '102','16:00',' 8/17/2010' union all
    select '102','Off','8/18/2010' union all
    select '102','16:00',' 8/19/2010' union all
    select '102','18:00',' 8/20/2010' union all
    select '102','16:00',' 8/21/2010' union all
    select '103','Off','8/15/2010' union all
    select '103','16:00',' 8/16/2010' union all
    select '103','16:00',' 8/17/2010' union all
    select '103','Off','8/18/2010' union all
    select '103','16:00',' 8/19/2010' union all
    select '103','18:00',' 8/20/2010' union all
    select '103','16:00',' 8/21/2010' union all
    select '104','Off','8/15/2010' union all
    select '104','16:00',' 8/16/2010' union all
    select '104','16:00',' 8/17/2010' union all
    select '104','16:00',' 8/18/2010' union all
    select '104','Off','8/19/2010' union all
    select '104','18:00',' 8/20/2010' union all
    select '104','16:00',' 8/21/2010' union all
    select '101','Off','8/22/2010' union all
    select '101','17:00',' 8/23/2010' union all
    select '101','17:00',' 8/24/2010' union all
    select '101','17:00',' 8/25/2010' union all
    select '101','17:00',' 8/26/2010' union all
    select '101','17:00',' 8/27/2010' union all
    select '101','17:00',' 8/28/2010' union all
    select '102','17:00',' 8/22/2010' union all
    select '102','off','8/23/2010' union all
    select '102','17:00',' 8/24/2010' union all
    select '102','17:00',' 8/25/2010' union all
    select '102','17:00',' 8/26/2010' union all
    select '102','17:00',' 8/27/2010' union all
    select '102','17:00',' 8/28/2010' union all
    select '103','Off','8/22/2010' union all
    select '103','16:00',' 8/23/2010' union all
    select '103','16:00',' 8/24/2010' union all
    select '103','Off','8/25/2010' union all
    select '103','16:00',' 8/26/2010' union all
    select '103','18:00',' 8/27/2010' union all
    select '103','16:00',' 8/28/2010' union all
    select '104','17:00',' 8/22/2010' union all
    select '104','15:00',' 8/23/2010' union all
    select '104','15:00',' 8/24/2010' union all
    select '104','15:00',' 8/25/2010' union all
    select '104','Off','8/26/2010' union all
    select '104','17:00',' 8/27/2010' union all
    select '104','17:00',' 8/28/2010'
    Tags: None
    • ck9663
      Recognized Expert Specialist
      • Jun 2007
      • 2878
      #2
      This code will count how many an employee took the day off per day (not date)...

      Code:
      with Testing1 (empno,MatchingOff,atnDate)
as
(
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','17:00','8/5/2010' union all
select '102','17:00','8/6/2010' union all
select '102','17:00','8/7/2010' union all
select '103','17:00','8/1/2010' union all
select '103','15:00','8/2/2010' union all
select '103','15:00','8/3/2010' union all
select '103','Off','8/4/2010' union all
select '103','17:00','8/5/2010' union all
select '103','17:00','8/6/2010' union all
select '103','17:00','8/7/2010' union all
select '104','17:00','8/1/2010' union all
select '104','15:00','8/2/2010' union all
select '104','15:00','8/3/2010' union all
select '104','15:00','8/4/2010' union all
select '104','Off','8/5/2010' union all
select '104','17:00','8/6/2010' union all
select '104','17:00','8/7/2010' union all
select '101','Off','8/8/2010' union all
select '101','16:00','8/9/2010' union all
select '101','16:00','8/10/2010' union all
select '101','Off','8/11/2010' union all
select '101','16:00','8/12/2010' union all
select '101','18:00','8/13/2010' union all
select '101','16:00','8/14/2010' union all
select '102','Off','8/8/2010' union all
select '102','Off','8/9/2010' union all
select '102','16:00','8/10/2010' union all
select '102','Off','8/11/2010' union all
select '102','16:00','8/12/2010' union all
select '102','18:00','8/13/2010' union all
select '102','16:00','8/14/2010' union all
select '103','Off','8/8/2010' union all
select '103','16:00','8/9/2010' union all
select '103','16:00','8/10/2010' union all
select '103','Off','8/11/2010' union all
select '103','16:00','8/12/2010' union all
select '103','18:00','8/13/2010' union all
select '103','16:00','8/14/2010' union all
select '104','Off','8/8/2010' union all
select '104','16:00','8/9/2010' union all
select '104','16:00','8/10/2010' union all
select '104','17:00','8/11/2010' union all
select '104','Off','8/12/2010' union all
select '104','18:00','8/13/2010' union all
select '104','16:00','8/14/2010' union all
select '101','Off','8/15/2010' union all
select '101','16:00','8/16/2010' union all
select '101','16:00','8/17/2010' union all
select '101','Off','8/18/2010' union all
select '101','16:00','8/19/2010' union all
select '101','18:00','8/20/2010' union all
select '101','16:00','8/21/2010' union all
select '102','Off','8/15/2010' union all
select '102','Offs','8/16/2010' union all
select '102','16:00','8/17/2010' union all
select '102','Off','8/18/2010' union all
select '102','16:00','8/19/2010' union all
select '102','18:00','8/20/2010' union all
select '102','16:00','8/21/2010' union all
select '103','Off','8/15/2010' union all
select '103','16:00','8/16/2010' union all
select '103','16:00','8/17/2010' union all
select '103','Off','8/18/2010' union all
select '103','16:00','8/19/2010' union all
select '103','18:00','8/20/2010' union all
select '103','16:00','8/21/2010' union all
select '104','Off','8/15/2010' union all
select '104','16:00','8/16/2010' union all
select '104','16:00','8/17/2010' union all
select '104','16:00','8/18/2010' union all
select '104','Off','8/19/2010' union all
select '104','18:00','8/20/2010' union all
select '104','16:00','8/21/2010' union all
select '101','Off','8/22/2010' union all
select '101','17:00','8/23/2010' union all
select '101','17:00','8/24/2010' union all
select '101','17:00','8/25/2010' union all
select '101','17:00','8/26/2010' union all
select '101','17:00','8/27/2010' union all
select '101','17:00','8/28/2010' union all
select '102','17:00','8/22/2010' union all
select '102','off','8/23/2010' union all
select '102','17:00','8/24/2010' union all
select '102','17:00','8/25/2010' union all
select '102','17:00','8/26/2010' union all
select '102','17:00','8/27/2010' union all
select '102','17:00','8/28/2010' union all
select '103','Off','8/22/2010' union all
select '103','16:00','8/23/2010' union all
select '103','16:00','8/24/2010' union all
select '103','Off','8/25/2010' union all
select '103','16:00','8/26/2010' union all
select '103','18:00','8/27/2010' union all
select '103','16:00','8/28/2010' union all
select '104','17:00','8/22/2010' union all
select '104','15:00','8/23/2010' union all
select '104','15:00','8/24/2010' union all
select '104','15:00','8/25/2010' union all
select '104','Off','8/26/2010' union all
select '104','17:00','8/27/2010' union all
select '104','17:00','8/28/2010' 
)
select 
empno, datename(dw,atnDate), count(*)
from Testing1 
where MatchingOff = 'Off' 
group by empno, datename(dw,atnDate)
order by 1
      The CTE is for code testing purposes only...

      You can start from here...

      Happy Coding!!!

      ~~ CK

        Comment

        Previous template Next
        Working...