Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 5th, 2008, 09:13 AM
Authorized User
 
Join Date: Oct 2006
Location: Kolkata, West Bengal, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default Store Procedure For Attendance

Hi Frnds,

          Can you please help me regarding a stored procedure which calculates monthly attendance for all employees.I am having a attendance table which has following fields:

EmployeeName
Attendace_Date
Status


          The start date & end date for a month will be user input.It will check day by day.If any date is absent then it status will be 'A', otherwise status will be 'P'.

          Please help me.

  #2 (permalink)  
Old January 5th, 2008, 09:22 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Something like this for attended days:
Code:
SELECT EmployeeName, COUNT(*) DaysAttended
FROM Attendance
WHERE    (Attendance_Date >= @StartDate OR @StartDate IS NULL)
     AND (Attendance_Date <= @EndDate OR @EndDate IS NULL)
     AND Status = 'P'
GROUP BY EmployeeName 
ORDER BY EmployeeName;
or this for all days:
Code:
SELECT EmployeeName, Status, COUNT(*) DaysAttended
FROM Attendance
WHERE    (Attendance_Date >= @StartDate OR @StartDate IS NULL)
     AND (Attendance_Date <= @EndDate OR @EndDate IS NULL)
GROUP BY EmployeeName, Status
ORDER BY EmployeeName, Status;
You will need to declare the two parameters, @StartDate and @EndDate, for your procedure.

--

Joe (Microsoft MVP - XML)
  #3 (permalink)  
Old January 8th, 2008, 03:37 AM
Authorized User
 
Join Date: Oct 2006
Location: Kolkata, West Bengal, India.
Posts: 63
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Joe,
           Thanks for your reply.But can you tell me one more thing? If any date is missing for a particular employee,how to display that day as "Absent" for that employee?

With Regards
Prasanta


  #4 (permalink)  
Old July 5th, 2012, 03:08 AM
Registered User
Points: 6, Level: 1
Points: 6, Level: 1 Points: 6, Level: 1 Points: 6, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is a very hard problem in my case. I need your opinion regarding something. How efficient would it be if I used triggers to transform data as attendance records are being inserted?
  #5 (permalink)  
Old July 5th, 2012, 03:10 AM
Registered User
Points: 6, Level: 1
Points: 6, Level: 1 Points: 6, Level: 1 Points: 6, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If you provide some more details I might be able to help you.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Store procedure help... RinoDM SQL Server 2000 7 August 11th, 2008 07:09 PM
Store procedure help ??? RinoDM SQL Server 2000 8 May 1st, 2008 03:03 PM
Create Store Procedure ?? kumiko SQL Language 7 January 4th, 2008 02:11 AM
Store Procedure sureshyuga SQL Server 2000 0 May 18th, 2007 01:49 AM
How do I Call store procedure kau_shuk VS.NET 2002/2003 1 September 7th, 2006 08:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.