How to find no of employees whose startTime differs by 0hrs,with 2hrs.

• naresh0407 93367

  SSC-Addicted

  Points: 479

  More actions

  October 20, 2010 at 8:33 am

  #143769

  Hi ALL,

  This is my table.

  Based on MatchingOff Column I need to find no of employee whose starttime is same for all 4weeks.

  No of employees whose starttime differs within 2hrs difference across 4 weeks.

  create table Testing7(empno int, MatchingOff varchar(10),atndate datetime)

  Insert into Testing7(empno,MatchingOff,atndate)

  select '101','Off','08/01/2010' union all

  select '101','Off','08/02/2010' union all

  select '101','17:00','08/03/2010' union all

  select '101','17:00','08/04/2010' union all

  select '101','17:00','08/05/2010' union all

  select '101','17:00','08/06/2010' union all

  select '101','17:00','08/07/2010' union all

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

  select '101','Off','08/09/2010' union all

  select '101','16:00','08/10/2010' union all

  select '101','16:00','08/11/2010' union all

  select '101','16:00','08/12/2010' union all

  select '101','18:00','08/13/2010' union all

  select '101','16:00','08/14/2010' union all

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

  select '101','Off','08/16/2010' union all

  select '101','16:00','08/17/2010' union all

  select '101','17:00','08/18/2010' union all

  select '101','16:00','08/19/2010' union all

  select '101','18:00','08/20/2010' union all

  select '101','16:00','08/21/2010' union all

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

  select '101','Off','08/23/2010' union all

  select '101','17:00','08/24/2010' union all

  select '101','17:00','08/25/2010' union all

  select '101','17:00','08/26/2010' union all

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

  select '101','17:00','08/28/2010' union all

  select '102','17:00','08/01/2010' union all

  select '102','17:00','08/02/2010' union all

  select '102','17:00','08/03/2010' union all

  select '102','17:00','08/04/2010' union all

  select '102','17:00','08/05/2010' union all

  select '102','Off','08/06/2010' union all

  select '102','Off','08/07/2010' union all

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

  select '102','16:00','08/09/2010' union all

  select '102','16:00','08/10/2010' union all

  select '102','16:00','08/11/2010' union all

  select '102','16:00','08/12/2010' union all

  select '102','Off','08/13/2010' union all

  select '102','Off','08/14/2010' union all

  select '102','17:00','08/15/2010' union all

  select '102','16:00','08/16/2010' union all

  select '102','16:00','08/17/2010' union all

  select '102','17:00','08/18/2010' union all

  select '102','16:00','08/19/2010' union all

  select '102','Off','08/20/2010' union all

  select '102','Off','08/21/2010' union all

  select '102','17:00','08/22/2010' union all

  select '102','17:00','08/23/2010' union all

  select '102','17:00','08/24/2010' union all

  select '102','17:00','08/25/2010' union all

  select '102','17:00','08/26/2010' union all

  select '102','17:00','08/27/2010' union all

  select '102','Off','08/28/2010' union all

  select '102','Off','08/29/2010' union all

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 20, 2010 at 1:33 pm

  #1239885

  I noticed this is at least the 4th thread you opened on the very same issue (with minor changes to the required output but all requests are related to the same concept)...

  The other threads I noticed:

  http://www.sqlservercentral.com/Forums/FindPost1005312.aspx

  http://www.sqlservercentral.com/Forums/FindPost1006226.aspx

  http://www.sqlservercentral.com/Forums/FindPost1006226.aspx

  You did provide the sample data and sometimes a vague description of your requested output.

  But in none of your posts we can find any line of code that would show us what you've tried so far and where you get stuck or what the final result would be based on the sample data provided.

  So you're leaving the impression that you're not looking for some help to understand a specific issue or to learn some new stuff. My personal impression is that you're trying to abuse this forum as some sort of free consulting/programming.

  If you need someone to write the code for you, you should hire a consultant or a freelancer.

  For those who think my reply is a little too harsh: take a look at the post history...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 20, 2010 at 1:38 pm

  #1239890

  LOL Lutz. 😀

  _______________________________________________________________

  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/

• LutzM

  SSC Guru

  Points: 107049

  More actions

  October 20, 2010 at 1:58 pm

  #1239905

  SeanLange (10/20/2010)

  ---

  LOL Lutz. 😀

  What's so funny about it? I found it rather sad... :crying:

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  October 20, 2010 at 2:04 pm

  #1239911

  I was laughing because you said exactly what I was thinking. I was going to let it rot since like you I noticed it was the same as the other posts. Same bad data structure, same strange requirements, same vague output.

  _______________________________________________________________

  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/