October 25, 2012 at 2:11 pm
Sarab_SQLGeek (10/25/2012)
I knw its very old thread but leaving this query link for someone who needs it still nowCalculate Leave working days in a table column as a default value--updated
If you are using SQL here is the query which can help you:
The link doesn't work.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2012 at 2:30 pm
The link is http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a
[url=http:// http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a%5DScript%5B/url%5D
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
October 25, 2012 at 2:46 pm
Sarab_SQLGeek (10/25/2012)
[url=http:// http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a%5DScript%5B/url%5D%5B/quote%5D
You might want to check your site on that... there's no script even though you say one is available.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2012 at 3:16 pm
Jeff Moden (10/25/2012)
Sarab_SQLGeek (10/25/2012)
The link is http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a[url=http:// http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a%5DScript%5B/url%5D%5B/quote%5D
You might want to check your site on that... there's no script even though you say one is available.
Jeff the link as posted would not work but the actual link was buried in there. It just pointed to a txt file. Here are the contents:
/*
Query: Calculate Leave working days in a table column as a default value
Prepared by: Sarabpreet Singh Anand
Contact: sarabpreet.anand@gmail.com
Version: 1B--updated 26-10-2012 12:20 AM IST
Website: http://www.sarabpreet.com
Twitter: @Sarab_SQLGeek
*/
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;
IF OBJECT_ID (N'dbo.LeaveWD', N'FN') IS NOT NULL
DROP FUNCTION dbo.LeaveWD;
GO
Create FUNCTION dbo.LeaveWD (@Leave_Start datetime, @Leave_End datetime)
RETURNS int
AS
BEGIN
Declare @a as DateTime
set @a = @Leave_Start
declare @weekday int
set @weekday = 0
WHILE @a <= @Leave_End
BEGIN
IF (convert(int,DATEPART(dw,@a))) not in (1,7)
Begin
SET @weekday = (@weekday + 1)
End
set @a = DATEADD(d,1,@a)
END
RETURN(@weekday)
END
GO
--to test
--SELECT dbo.LeaveWD('2012-10-20 00:01:44.613','2012-10-28 00:01:44.613');
Create table test_leaves
(
leave_working_day as (dbo.LeaveWD(Leave_Start,Leave_End)),
Leave_Start datetime,
Leave_End datetime
)
--sample data insert test
--insert into test_leaves(Leave_Start,Leave_End) values('2012-10-20 00:01:44.613','2012-10-28 00:01:44.613')
--getting results out of table
--select * from test_leaves
_______________________________________________________________
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/
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply