.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> ASP.Net >> Post New QuestionBookmark and Share Subscribe to Forum

How to check whether the employee is getting weekOff for all 4 weeks

Posted By: Naresh     Posted Date: October 18, 2010    Points:2   Category :ASP.Net
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,atnDate)
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'



Responses
Author: Sajid P K             
Posted Date: October 18, 2010     Points: 5   

Naresh ,

Please Chek this...

SELECT emp  FROM 

Testing1

WHERE MatchingOff = 'Off'
GROUP BY emp
HAVING COUNT(MatchingOff) >= 4


Regards,
Sajid P K

Author: Naresh             
Posted Date: October 18, 2010     Points: 5   

Hi Sajid,


Employee will get WeekOff in every week.SO with the above query i will get employees who is getting weekoff in every week i.e count>=4.

But i want employees who are getting weekOff on sameday for all 4 weeks.Suppose if the employee is getting weekOff on Monday(it can be any day) for all 4 weeks then only we should count it as 1 otherwise 0.


Suppose if the employee is getting weekOff on Monday this week SUnday next week and Friday on last 2 weeks then it should be ignored.



Author: Sajid P K             
Posted Date: October 18, 2010     Points: 5   

Naresh ,

Please Chek this aswell...



SELECT emp FROM Testing1
WHERE MatchingOff = 'Off'
GROUP BY emp
HAVING count( DATENAME(DW,d)) - count(DISTINCT DATENAME(DW,d)) >= 4


Regards,
Sajid P K




Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend