How to find no of employees whose TotalWorkingDays is 0 in 4 weeks

• naresh0407 93367

  SSC-Addicted

  Points: 479

  More actions

  October 18, 2010 at 7:57 am

  #143766

  Hi All,

  This is my table.

  I have 4weeks data of 4 employees.Out of 4 weeks i need to find how many no of employees whose TotalWorkingdays is 0 .

  I need 2 results 1)first How many employees whose TotalWorkingDays is 0 for all 4 weeks

  2)How many employees whose TotalWorkingDays is 0 in any 1 week

  BothResult Seperatly just i want to return EmpCount

  From the below table

  1)EMpNo 104 TotalWorkingDays is 0 for all 4 weeks so return count 1 simiraly how many no of employees

  2)EmpNo 102,103 TotalWorkingDays is 0 for 1 week out of 4 weeks so return count 2 becuase 2 employees.

  create table TotalDays(TotalWorkingDays int,empNo int,atnDate datetime)

  Insert into TotalDays(TotalWorkingDays,empNo,atnDate)

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

  select '0','102','8/1/2010' union all

  select '6','103','8/1/2010' union all

  select '0','104','8/1/2010' union all

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

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

  select '5','103','8/8/2010' union all

  select '0','104','8/8/2010' union all

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

  select '4','102','8/15/2010' union all

  select '5','103','8/15/2010' union all

  select '0','104','8/22/2010' union all

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

  select '4','102','8/22/2010' union all

  select '0','103','8/22/2010' union all

  select '0','104','8/22/2010'

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 18, 2010 at 3:34 pm

  #1238756

  Kind of vague what you want for results but...

  select count(distinct empNo) from TotalDays group by empNo having SUM(totalWorkingDays) = 0

  select COUNT(empNo) from TotalDays where atnDate = '8/1/2010' and totalWorkingDays = 0

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/