| Author |
Topic |
|
ndindi22
Starting Member
14 Posts |
 Posted - 2007-08-16 : 05:59:27
|
 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
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2007-08-16 : 06:03:55
|
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.  |
 |
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-16 : 06:19:11
|
| 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 DATEIFFndindi22 |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2007-08-16 : 06:23:33
|
| 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. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-16 : 10:21:46
|
| Thanx man, i did check the link. but it's really confusing for me. Where do i start.ndindi22 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-16 : 10:29:55
|
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 datetimeSELECT @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[spoiler]Time is always against us[/spoiler] |
|
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-16 : 10:35:11
|
| How do i get the calendar table?ndindi22 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-16 : 10:36:58
|
quote:
Originally posted by ndindi22
How do i get the calendar table?ndindi22
create it !. Use the CREATE TABLE syntax
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-16 : 10:40:54
|
| okey!ndindi22 |
|
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-17 : 04:22:22
|
| Hi guysThis 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)/17from contractand 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 |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-08-19 : 11:46:27
|
| 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 |
|
|
|
ndindi22
Starting Member
14 Posts |
Posted - 2007-08-20 : 07:10:45
|
| Thanx JeffI 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 leavendindi22 |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-08-20 : 09:08:24
|
| 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 |
|
|
|
|
Calculate leave days between Startdate and Enddate