Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Calculate leave days between Startdate and Enddate
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/16/2007 :  05:59:27  Show Profile  Visit ndindi22's Homepage  Reply with Quote


Can someone plz help me.

I'm working on leave application, have to calculate number of leave days available, starting from Startdate to Enddate of a contract. Where an employee get 1 day leave after 17 days from startdate of contract. How do I calculate the leave days, that accrue every after 17 days by 1.

I'm using ASP and SQL Server 2000 (Query Analyzer)

ndindi22

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 08/16/2007 :  06:03:55  Show Profile  Visit ditch's Homepage  Reply with Quote
do weekends and public holidays need to be accounted for?
you probably also need to take into account wether the person uses up some of his/her leave during the time calculated.

Look up DATEDIFF in Books OnLine and see what you come up with.
Try code it up and if you are still battling - post your code and examples of data here.



Duane.
Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/16/2007 :  06:19:11  Show Profile  Visit ndindi22's Homepage  Reply with Quote
Thanx man, weekends are excluded, but public holidays are included. I don't know how and where to strart on this.... I'll try the DATEIFF

ndindi22
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 08/16/2007 :  06:23:33  Show Profile  Visit ditch's Homepage  Reply with Quote
Another pointer that I can give you then would be to use a Time Dimension - it is a table with calendar dates that indicate when public holidays and weekends are - there is a lot of info on this - use google :)

Duane.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 08/16/2007 :  09:32:56  Show Profile  Reply with Quote
you are better off with a calendar table.

Alternatively you can make use of the function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE


KH
Time is always against us

Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/16/2007 :  10:21:46  Show Profile  Visit ndindi22's Homepage  Reply with Quote
Thanx man, i did check the link. but it's really confusing for me. Where do i start.

ndindi22
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 08/16/2007 :  10:29:55  Show Profile  Reply with Quote
if you have a calendar table (which you really should have) just replace the F_TABLE_DATE with a calendar table. You can also use F_TABLE_DATE to generate dates into your Calendar table




DECLARE @start_date	datetime,
	@end_date	datetime

SELECT	@start_date	= '2007-01-01',
	@end_date	= '2007-12-31'

SELECT	COUNT(*)
FROM	F_TABLE_DATE(@start_date, @end_date)
WHERE	WEEKDAY_NAME NOT IN ('Sat', 'Sun')



KH
Time is always against us

Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/16/2007 :  10:35:11  Show Profile  Visit ndindi22's Homepage  Reply with Quote
How do i get the calendar table?

ndindi22
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 08/16/2007 :  10:36:58  Show Profile  Reply with Quote
quote:
Originally posted by ndindi22

How do i get the calendar table?

ndindi22



create it !. Use the CREATE TABLE syntax


KH
Time is always against us

Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/16/2007 :  10:40:54  Show Profile  Visit ndindi22's Homepage  Reply with Quote
okey!

ndindi22
Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/17/2007 :  04:22:22  Show Profile  Visit ndindi22's Homepage  Reply with Quote
Hi guys

This is what i've
SELECT
(DATEDIFF(dd, ContractStartDate, ContractEnddate) + 1)
-(DATEDIFF(wk, ContractStartDate, ContractEnddate) * 2)
-(CASE WHEN DATENAME(dw, ContractStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, ContractEnddate) = 'Saturday' THEN 1 ELSE 0 END)
/17
from contract

and it's calculating fine days between ContractStartDate and ContractEnddate. But now I want to have another column that is going to calculate no of leave days, every after 17 woking days the employee gets 1 day leave and it accrue by 1 every after 17 woking days.

Can someone plz help....

ndindi22
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
652 Posts

Posted - 08/19/2007 :  11:46:27  Show Profile  Reply with Quote
Like I said on that "other" forum, just change the column names in the formula.

Now, if you want to account for an employee missing days, then we'll need a wee bit more information about how you store employee attendance and the like.

--Jeff Moden
Go to Top of Page

ndindi22
Starting Member

South Africa
14 Posts

Posted - 08/20/2007 :  07:10:45  Show Profile  Visit ndindi22's Homepage  Reply with Quote
Thanx Jeff

I did a blunder last time forgot the brackets, now it's working fine.
Now I need a leave cycle column, if an employee took leave using the form, it should calculate how many leave days available if no leave days available should add to unpaid leave column unpaid leave

ndindi22
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
652 Posts

Posted - 08/20/2007 :  09:08:24  Show Profile  Reply with Quote
Yes, you've said that a couple of times... and still haven't provided any additional information that has been requested.

If you don't have an attendance system that keeps track of the number of hours/days an employee worked, you'll need to build that first. If you do have one, then you need to provide us with enough information about where and how it stores attendance information before we can help you.


--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000