number of working days between two dates

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

number of working days between two dates

Author

Topic

cardgunner

326 Posts

Posted - 2008-07-31 : 09:52:40

I'm not sure if the answer to this lies in the F_TABLE_DATE Function or not.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

I looked at the function, but I have NO idea what I'm looking at and what it does.

I have two dates a start date(@s_date) and end date(@e_date). I would like to know the number of working days that are between these two dates. I want to exclude Saturdays and Sundays and Some of the American Federal Holidays.

I say some because we are open and count some as working days and some not.

I can give some dates like New Years Day is always 1/1/???? but Thanksgiving is 11/??/????.

But in any case is my answer to finding the number of working days between two dates in the function above or is there something less complex that I may be able to grasp?

CardGunner

CardGunner

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 09:59:32

This would help u,
if there any issues let me know.

DECLARE @StartDate DATETIME,
@EndDate DATETIME
SELECT @StartDate = '01-July-2008',
@EndDate = '30-July-2008'
;WITH DATE (Date1)
AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', @StartDate), '19000101')
UNION ALL
SELECT DATEADD(DAY, 1, Date1)
FROM DATE
WHERE Date1 < @EndDate
)
SELECT
CONVERT(VARCHAR(15),d1.DATE1 ,110) as [Working Date],
DATENAME(weekday, d1.Date1) [Working Day]
from DATE d1 where (DATENAME(weekday, d1.Date1)) not in ('Saturday','Sunday')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:08:56

SELECT COUNT(*)
FROM   F_TABLE_DATE(@s_date, @e_date) d
       left JOIN holidays h ON d.[DATE] = h.[DATE]
WHERE  h.[DATE] IS NULL
AND    d.[DAY_OF_WEEK] BETWEEN 2 AND 6

KH
[spoiler]Time is always against us[/spoiler]

cardgunner

326 Posts

Posted - 2008-07-31 : 10:14:48

Thanks VG for the quick reply.

I took what you had and posted it in my SQl analyer and the following error came up Incorrect syntax near the keyword 'WITH'.

I tried taking it out ; and replacing it with , but no luck.

I'm pretty new to this so if I;m overlooking the obvious excuse me.

CardGunner

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:16:19

quote:
Originally posted by cardgunner

Thanks VG for the quick reply.

I took what you had and posted it in my SQl analyer and the following error came up Incorrect syntax near the keyword 'WITH'.

I tried taking it out ; and replacing it with , but no luck.

I'm pretty new to this so if I;m overlooking the obvious excuse me.

CardGunner

That code is for SQL Server 2005. Are you using 2005 or 2000 ?

KH
[spoiler]Time is always against us[/spoiler]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-31 : 10:16:45

VGuyz solution will not work on SQL2000 as it uses a CTE

Em

cardgunner

326 Posts

Posted - 2008-07-31 : 10:18:45

quote:

Originally posted by khtan

SELECT COUNT(*)
FROM   F_TABLE_DATE(@s_date, @e_date) d
       left JOIN holidays h ON d.[DATE] = h.[DATE]
WHERE  h.[DATE] IS NULL
AND    d.[DAY_OF_WEEK] BETWEEN 2 AND 6

KH
[spoiler]Time is always against us[/spoiler]

Thanks fior the reply KH.

I take it my answer is in the F_TABLE_DATE function?

Most of that TO ME is chinese.

Can I copy it and paste into my SQL analyzer, run it and it will work?

CardGunner

cardgunner

326 Posts

Posted - 2008-07-31 : 10:21:42

I found in excel there is a networkday function. =networkday(Date1, date2, holidays) and that worked fine for me in Excel. Does not work in SQL.

CardGunner

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-31 : 10:46:11

Hi cardgunner,

really sorry ya,
i have not think abt sql server 2005 or 2000

chk it out this query

SELECT CONVERT(VARCHAR(15),DATEADD(d,number,getdate()),110) as [Working Date],
DATENAME(weekday, DATEADD(d,number,getdate()))as [Working Day]
FROM master..spt_values
WHERE type='p'
AND DATEPART(dw,DATEADD(d,number,getdate())) NOT IN (1,7)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:49:47

quote:
Originally posted by cardgunner

I found in excel there is a networkday function. =networkday(Date1, date2, holidays) and that worked fine for me in Excel. Does not work in SQL.

CardGunner

Of-course it will not.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 10:50:31

quote:
Can I copy it and paste into my SQL analyzer, run it and it will work?

Can. What's stopping you ? Just try . . . . it won't bite

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:03:57

It you can calculate the number of days, that would be better than counting the days.
Try this

create function workhours_jeff(@StartDate datetime, @EndDate datetime) returns float as
begin

return CAST(
                (
                  (DATEDIFF(dd,@StartDate,@EndDate)+1)
                 -(DATEDIFF(wk,@StartDate,@EndDate)*2) 
                 -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
                 -(CASE WHEN DATENAME(dw,@EndDate) = 'Saturday' THEN 1 ELSE 0 END)
                 )
                -(CASE WHEN DATENAME(dw,@StartDate) = 'Sunday' or DATENAME(dw,@StartDate) = 'Saturday' THEN 0
                       ELSE CAST(CONVERT(CHAR(12),@StartDate,114) AS DATETIME) END)
                -(CASE WHEN DATENAME(dw,@EndDate) = 'Sunday' or DATENAME(dw,@EndDate) = 'Saturday' THEN 0 
                       ELSE (1.0-CAST(CONVERT(CHAR(12),@EndDate,114) AS DATETIME)) END)
        AS FLOAT) * 24
end
go

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-07-31 : 11:18:39

Ok

I copied the code below and ran it. I got some errors

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

Also I need to declare @FIRST_DATE, @LAST_DATE, @start_date, @end_date, and @DATE

Server: Msg 208, Level 16, State 11, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

Please be patient I have no idea exactly what a function is and a little idea what it does but unsure how it does it.

Also I have very little idea of what I'm doing.

 returns  @DATE table 
(
[DATE_ID]				[int]		not null 
	primary key clustered,
[DATE]					[datetime]	not null ,
[NEXT_DAY_DATE]				[datetime]	not null ,
[YEAR]					[smallint]	not null ,
[YEAR_QUARTER]				[int]	not null ,
[YEAR_MONTH]				[int]		not null ,
[YEAR_DAY_OF_YEAR]			[int]		not null ,
[QUARTER]				[tinyint]	not null ,
[MONTH]					[tinyint]	not null ,
[DAY_OF_YEAR]				[smallint]	not null ,
[DAY_OF_MONTH]				[smallint]	not null ,
[DAY_OF_WEEK]				[tinyint]	not null ,

[YEAR_NAME]				[varchar] (4)	not null ,
[YEAR_QUARTER_NAME]			[varchar] (7)	not null ,
[YEAR_MONTH_NAME]			[varchar] (8)	not null ,
[YEAR_MONTH_NAME_LONG]			[varchar] (14)	not null ,
[QUARTER_NAME]				[varchar] (2)	not null ,
[MONTH_NAME]				[varchar] (3)	not null ,
[MONTH_NAME_LONG]			[varchar] (9)	not null ,
[WEEKDAY_NAME]				[varchar] (3)	not null ,
[WEEKDAY_NAME_LONG]			[varchar] (9)	not null ,

[START_OF_YEAR_DATE]			[datetime]	not null ,
[END_OF_YEAR_DATE]			[datetime]	not null ,
[START_OF_QUARTER_DATE]			[datetime]	not null ,
[END_OF_QUARTER_DATE]			[datetime]	not null ,
[START_OF_MONTH_DATE]			[datetime]	not null ,
[END_OF_MONTH_DATE]			[datetime]	not null ,

[START_OF_WEEK_STARTING_SUN_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_SUN_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_MON_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_MON_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_TUE_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_TUE_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_WED_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_WED_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_THU_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_THU_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_FRI_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_FRI_DATE]		[datetime]	not null ,
[START_OF_WEEK_STARTING_SAT_DATE]	[datetime]	not null ,
[END_OF_WEEK_STARTING_SAT_DATE]		[datetime]	not null ,

[QUARTER_SEQ_NO]			[int]		not null ,
[MONTH_SEQ_NO]				[int]		not null ,

[WEEK_STARTING_SUN_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_MON_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_TUE_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_WED_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_THU_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_FRI_SEQ_NO]		[int]		not null ,
[WEEK_STARTING_SAT_SEQ_NO]		[int]		not null ,

[JULIAN_DATE]				[int]		not null ,
[MODIFIED_JULIAN_DATE]			[int]		not null ,

[ISO_DATE]				[varchar](10)	not null ,
[ISO_YEAR_WEEK_NO]			[int]		not null ,
[ISO_WEEK_NO]				[smallint]	not null ,
[ISO_DAY_OF_WEEK]			[tinyint]	not null ,
[ISO_YEAR_WEEK_NAME]			[varchar](8)	not null ,
[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME]	[varchar](10)	not null ,

[DATE_FORMAT_YYYY_MM_DD]		[varchar](10)	not null ,
[DATE_FORMAT_YYYY_M_D]			[varchar](10)	not null ,
[DATE_FORMAT_MM_DD_YYYY]		[varchar](10)	not null ,
[DATE_FORMAT_M_D_YYYY]			[varchar](10)	not null ,
[DATE_FORMAT_MMM_D_YYYY]		[varchar](12)	not null ,
[DATE_FORMAT_MMMMMMMMM_D_YYYY]		[varchar](18)	not null ,
[DATE_FORMAT_MM_DD_YY]			[varchar](8)	not null ,
[DATE_FORMAT_M_D_YY]			[varchar](8)	not null 
) 
as
begin
declare @cr			varchar(2)
select @cr			= char(13)+Char(10)
declare @ErrorMessage		varchar(400)
declare @START_DATE		datetime
declare @END_DATE		datetime
declare @LOW_DATE	datetime

declare	@start_no	int
declare	@end_no	int

-- Verify @FIRST_DATE is not null 
if @FIRST_DATE is null
	begin
	select @ErrorMessage =
		'@FIRST_DATE cannot be null'
	goto Error_Exit
	end

-- Verify @LAST_DATE is not null 
if @LAST_DATE is null
	begin
	select @ErrorMessage =
		'@LAST_DATE cannot be null'
	goto Error_Exit
	end

-- Verify @FIRST_DATE is not before 1754-01-01
IF  @FIRST_DATE < '17540101'	begin
	select @ErrorMessage =
		'@FIRST_DATE cannot before 1754-01-01'+
		', @FIRST_DATE = '+
		isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')
	goto Error_Exit
	end

-- Verify @LAST_DATE is not after 9997-12-31
IF  @LAST_DATE > '99971231'	begin
	select @ErrorMessage =
		'@LAST_DATE cannot be after 9997-12-31'+
		', @LAST_DATE = '+
		isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
	goto Error_Exit
	end

-- Verify @FIRST_DATE is not after @LAST_DATE
if @FIRST_DATE > @LAST_DATE
	begin
	select @ErrorMessage =
		'@FIRST_DATE cannot be after @LAST_DATE'+
		', @FIRST_DATE = '+
		isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+
		', @LAST_DATE = '+
		isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
	goto Error_Exit
	end

-- Set @START_DATE = @FIRST_DATE at midnight
select @START_DATE	= dateadd(dd,datediff(dd,0,@FIRST_DATE),0)
-- Set @END_DATE = @LAST_DATE at midnight
select @END_DATE	= dateadd(dd,datediff(dd,0,@LAST_DATE),0)
-- Set @LOW_DATE = earliest possible SQL Server datetime
select @LOW_DATE	= convert(datetime,'17530101')

-- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE
select	@start_no	= datediff(dd,@LOW_DATE,@START_DATE) ,
	@end_no	= datediff(dd,@LOW_DATE,@END_DATE)

-- Declare number tables
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

-- Declare table of ISO Week ranges
declare @ISO_WEEK table
(
[ISO_WEEK_YEAR] 		int		not null
	primary key clustered,
[ISO_WEEK_YEAR_START_DATE]	datetime	not null,
[ISO_WEEK_YEAR_END_DATE]	Datetime	not null
)

-- Find rows needed in number tables
declare	@rows_needed		int
declare	@rows_needed_root	int
select	@rows_needed		= @end_no - @start_no + 1
select  @rows_needed		=
		case
		when @rows_needed < 10
		then 10
		else @rows_needed
		end
select	@rows_needed_root	= convert(int,ceiling(sqrt(@rows_needed)))

-- Load number 0 to 16
insert into @num1 (NUMBER)
select NUMBER = 0 union all select  1 union all select  2 union all
select          3 union all select  4 union all select  5 union all
select          6 union all select  7 union all select  8 union all
select          9 union all select 10 union all select 11 union all
select         12 union all select 13 union all select 14 union all
select         15
order by
	1
-- Load table with numbers zero thru square root of the number of rows needed +1
insert into @num2 (NUMBER)
select
	NUMBER = a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER)
from
	@num1 a cross join @num1 b cross join @num1 c
where
	a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER) <
	@rows_needed_root
order by
	1

-- Load table with the number of rows needed for the date range
insert into @num3 (NUMBER)
select
	NUMBER = a.NUMBER+(@rows_needed_root*b.NUMBER)
from
	@num2 a
	cross join
	@num2 b
where
	a.NUMBER+(@rows_needed_root*b.NUMBER) < @rows_needed
order by
	1

declare	@iso_start_year	int
declare	@iso_end_year	int

select	@iso_start_year	= datepart(year,dateadd(year,-1,@start_date))
select	@iso_end_year	= datepart(year,dateadd(year,1,@end_date))

-- Load table with start and end dates for ISO week years
insert into @ISO_WEEK
	(
	[ISO_WEEK_YEAR],
	[ISO_WEEK_YEAR_START_DATE],
	[ISO_WEEK_YEAR_END_DATE]
	)
select
	[ISO_WEEK_YEAR] = a.NUMBER,
	[0ISO_WEEK_YEAR_START_DATE]	=
		dateadd(dd,(datediff(dd,@LOW_DATE,
		dateadd(day,3,dateadd(year,a.[NUMBER]-1900,0))
		)/7)*7,@LOW_DATE),
	[ISO_WEEK_YEAR_END_DATE]	=
		dateadd(dd,-1,dateadd(dd,(datediff(dd,@LOW_DATE,
		dateadd(day,3,dateadd(year,a.[NUMBER]+1-1900,0))
		)/7)*7,@LOW_DATE))
from
	(
	select
		NUMBER = NUMBER+@iso_start_year
	from
		@num3
	where
		NUMBER+@iso_start_year <= @iso_end_year
	) a
order by
	a.NUMBER

-- Load Date table
insert into @DATE
select
	[DATE_ID]			= a.[DATE_ID] ,
	[DATE]				= a.[DATE] ,

	[NEXT_DAY_DATE]			=
		dateadd(day,1,a.[DATE]) ,

	[YEAR]			=
		datepart(year,a.[DATE]) ,
	[YEAR_QUARTER]		=
		(10*datepart(year,a.[DATE]))+datepart(quarter,a.[DATE]) ,

	[YEAR_MONTH]		=
		(100*datepart(year,a.[DATE]))+datepart(month,a.[DATE]) ,
	[YEAR_DAY_OF_YEAR]		=
		(1000*datepart(year,a.[DATE]))+
		datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
	[QUARTER]		=
		datepart(quarter,a.[DATE]) ,
	[MONTH]		=
		datepart(month,a.[DATE]) ,
	[DAY_OF_YEAR]			=
		datediff(dd,dateadd(yy,datediff(yy,0,a.[DATE]),0),a.[DATE])+1 ,
	[DAY_OF_MONTH]			=
		datepart(day,a.[DATE]) ,
	[DAY_OF_WEEK]		=
		-- Sunday = 1, Monday = 2, ,,,Saturday = 7
		(datediff(dd,'17530107',a.[DATE])%7)+1  ,
	[YEAR_NAME]		=
		datename(year,a.[DATE]) ,
	[YEAR_QUARTER_NAME]	=
		datename(year,a.[DATE])+' Q'+datename(quarter,a.[DATE]) ,
	[YEAR_MONTH_NAME]	=
		datename(year,a.[DATE])+' '+left(datename(month,a.[DATE]),3) ,
	[YEAR_MONTH_NAME_LONG]	=
		datename(year,a.[DATE])+' '+datename(month,a.[DATE]) ,
	[QUARTER_NAME]		=
		'Q'+datename(quarter,a.[DATE]) ,
	[MONTH_NAME]		=
		left(datename(month,a.[DATE]),3) ,
	[MONTH_NAME_LONG]	=
		datename(month,a.[DATE]) ,
	[WEEKDAY_NAME]		=
		left(datename(weekday,a.[DATE]),3) ,
	[WEEKDAY_NAME_LONG]	=
		datename(weekday,a.[DATE]),

	[START_OF_YEAR_DATE]	=
		dateadd(year,datediff(year,0,a.[DATE]),0) ,
	[END_OF_YEAR_DATE]	=
		dateadd(day,-1,dateadd(year,datediff(year,0,a.[DATE])+1,0)) ,

	[START_OF_QUARTER_DATE]	=
		dateadd(quarter,datediff(quarter,0,a.[DATE]),0) ,
	[END_OF_QUARTER_DATE]	=
		dateadd(day,-1,dateadd(quarter,datediff(quarter,0,a.[DATE])+1,0)) ,

	[START_OF_MONTH_DATE]	=
		dateadd(month,datediff(month,0,a.[DATE]),0) ,
	[END_OF_MONTH_DATE]	=
		dateadd(day,-1,dateadd(month,datediff(month,0,a.[DATE])+1,0)),

	[START_OF_WEEK_STARTING_SUN_DATE]	=
		dateadd(dd,(datediff(dd,'17530107',a.[DATE])/7)*7,'17530107'),
	[END_OF_WEEK_STARTING_SUN_DATE]		=
		dateadd(dd,((datediff(dd,'17530107',a.[DATE])/7)*7)+6,'17530107'),

	[START_OF_WEEK_STARTING_MON_DATE]	=
		dateadd(dd,(datediff(dd,'17530101',a.[DATE])/7)*7,'17530101'),
	[END_OF_WEEK_STARTING_MON_DATE]		=
		dateadd(dd,((datediff(dd,'17530101',a.[DATE])/7)*7)+6,'17530101'),

	[START_OF_WEEK_STARTING_TUE_DATE]	=
		dateadd(dd,(datediff(dd,'17530102',a.[DATE])/7)*7,'17530102'),
	[END_OF_WEEK_STARTING_TUE_DATE]		=
		dateadd(dd,((datediff(dd,'17530102',a.[DATE])/7)*7)+6,'17530102'),

	[START_OF_WEEK_STARTING_WED_DATE]	=
		dateadd(dd,(datediff(dd,'17530103',a.[DATE])/7)*7,'17530103'),
	[END_OF_WEEK_STARTING_WED_DATE]		=
		dateadd(dd,((datediff(dd,'17530103',a.[DATE])/7)*7)+6,'17530103'),

	[START_OF_WEEK_STARTING_THU_DATE]	=
		dateadd(dd,(datediff(dd,'17530104',a.[DATE])/7)*7,'17530104'),
	[END_OF_WEEK_STARTING_THU_DATE]		=
		dateadd(dd,((datediff(dd,'17530104',a.[DATE])/7)*7)+6,'17530104'),

	[START_OF_WEEK_STARTING_FRI_DATE]	=
		dateadd(dd,(datediff(dd,'17530105',a.[DATE])/7)*7,'17530105'),
	[END_OF_WEEK_STARTING_FRI_DATE]		=
		dateadd(dd,((datediff(dd,'17530105',a.[DATE])/7)*7)+6,'17530105'),

	[START_OF_WEEK_STARTING_SAT_DATE]	=
		dateadd(dd,(datediff(dd,'17530106',a.[DATE])/7)*7,'17530106'),
	[END_OF_WEEK_STARTING_SAT_DATE]		=
		dateadd(dd,((datediff(dd,'17530106',a.[DATE])/7)*7)+6,'17530106'),

	[QUARTER_SEQ_NO]			= 
		datediff(quarter,@LOW_DATE,a.[DATE]),
	[MONTH_SEQ_NO]				=
		datediff(month,@LOW_DATE,a.[DATE]),

	[WEEK_STARTING_SUN_SEQ_NO]		=
		datediff(day,'17530107',a.[DATE])/7,
	[WEEK_STARTING_MON_SEQ_NO]		=
		datediff(day,'17530101',a.[DATE])/7,
	[WEEK_STARTING_TUE_SEQ_NO]		=
		datediff(day,'17530102',a.[DATE])/7,
	[WEEK_STARTING_WED_SEQ_NO]		=
		datediff(day,'17530103',a.[DATE])/7,
	[WEEK_STARTING_THU_SEQ_NO]		=
		datediff(day,'17530104',a.[DATE])/7,
	[WEEK_STARTING_FRI_SEQ_NO]		=
		datediff(day,'17530105',a.[DATE])/7,
	[WEEK_STARTING_SAT_SEQ_NO]		=
		datediff(day,'17530106',a.[DATE])/7,

	[JULIAN_DATE]			=
		datediff(day,@LOW_DATE,a.[DATE])+2361331,
	[MODIFIED_JULIAN_DATE]		=
		datediff(day,'18581117',a.[DATE]),
--/*

	[ISO_DATE]		=
		replace(convert(char(10),a.[DATE],111),'/','-') ,

	[ISO_YEAR_WEEK_NO]		=
		(100*b.[ISO_WEEK_YEAR])+
		(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

	[ISO_WEEK_NO]		=
		(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1 ,

	[ISO_DAY_OF_WEEK]		=
		-- Sunday = 1, Monday = 2, ,,,Saturday = 7
		(datediff(dd,@LOW_DATE,a.[DATE])%7)+1  ,

	[ISO_YEAR_WEEK_NAME]		=
		convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
		right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) ,

	[ISO_YEAR_WEEK_DAY_OF_WEEK_NAME]		=
		convert(varchar(4),b.[ISO_WEEK_YEAR])+'-W'+
		right('00'+convert(varchar(2),(datediff(dd,b.[ISO_WEEK_YEAR_START_DATE],a.[DATE])/7)+1),2) +
		'-'+convert(varchar(1),(datediff(dd,@LOW_DATE,a.[DATE])%7)+1) ,
--*/
	[DATE_FORMAT_YYYY_MM_DD]		=
		convert(char(10),a.[DATE],111) ,
	[DATE_FORMAT_YYYY_M_D]		= 
		convert(varchar(10),
		convert(varchar(4),year(a.[DATE]))+'/'+
		convert(varchar(2),day(a.[DATE]))+'/'+
		convert(varchar(2),month(a.[DATE]))),
	[DATE_FORMAT_MM_DD_YYYY]		= 
		convert(char(10),a.[DATE],101) ,
	[DATE_FORMAT_M_D_YYYY]		= 
		convert(varchar(10),
		convert(varchar(2),month(a.[DATE]))+'/'+
		convert(varchar(2),day(a.[DATE]))+'/'+
		convert(varchar(4),year(a.[DATE]))),
	[DATE_FORMAT_MMM_D_YYYY]		= 
		convert(varchar(12),
		left(datename(month,a.[DATE]),3)+' '+
		convert(varchar(2),day(a.[DATE]))+', '+
		convert(varchar(4),year(a.[DATE]))),
	[DATE_FORMAT_MMMMMMMMM_D_YYYY]	= 
		convert(varchar(18),
		datename(month,a.[DATE])+' '+
		convert(varchar(2),day(a.[DATE]))+', '+
		convert(varchar(4),year(a.[DATE]))),
	[DATE_FORMAT_MM_DD_YY]		=
		convert(char(8),a.[DATE],1) ,
	[DATE_FORMAT_M_D_YY]		= 
		convert(varchar(8),
		convert(varchar(2),month(a.[DATE]))+'/'+
		convert(varchar(2),day(a.[DATE]))+'/'+
		right(convert(varchar(4),year(a.[DATE])),2))
from
	(
	-- Derived table is all dates needed for date range
	select	top 100 percent
		[DATE_ID]	= aa.[NUMBER],
		[DATE]		=
			dateadd(dd,aa.[NUMBER],@LOW_DATE)
	from
		(
		select
			NUMBER = NUMBER+@start_no 
		from
			@num3
		where
			NUMBER+@start_no <= @end_no
		) aa
	order by
		aa.[NUMBER]
	) a
	join
	-- Match each date to the proper ISO week year
	@ISO_WEEK b
	on a.[DATE] between 
		b.[ISO_WEEK_YEAR_START_DATE] and 
		b.[ISO_WEEK_YEAR_END_DATE]
order by
	a.[DATE_ID]

return

Error_Exit:

-- Return a pesudo error message by trying to
-- convert an error message string to an int.
-- This method is used because the error displays
-- the string it was trying to convert, and so the
-- calling application sees a formatted error message.

declare @error int

set @error = convert(int,@cr+@cr+
'*******************************************************************'+@cr+
'* Error in function F_TABLE_DATE:'+@cr+'* '+
isnull(@ErrorMessage,'Unknown Error')+@cr+
'*******************************************************************'+@cr+@cr)

return

end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  SELECT  ON [dbo].[F_TABLE_DATE]  TO [public]
GO
set dateformat ydm
go
print 'Checksum with ydm'
go
select
	[Checksum] = checksum_agg(binary_checksum(*))
from
	dbo.F_TABLE_DATE ( '20000101','20101231' )
go
set dateformat ymd
go
print 'Checksum with ymd'
go
select
	[Checksum] = checksum_agg(binary_checksum(*))
from
	dbo.F_TABLE_DATE ( '20000101','20101231' )
go
set dateformat ymd
go
-- Sample select for date range
select *
from
	dbo.F_TABLE_DATE ( '20000101','20101231' )
order by 1

CardGunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:23:23

http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2008-07-31 : 11:50:14

Sorry I didn't bring in the first part so I copied now from

if exists (select * from dbo.sysobjects

to

dbo.F_TABLE_DATE ( '20000101','20101231' )
order by 1

and I get the following errors:
Server: Msg 137, Level 15, State 2, Procedure F_TABLE_DATE, Line 472
Must declare the variable '@start_date'.
Server: Msg 137, Level 15, State 1, Procedure F_TABLE_DATE, Line 473
Must declare the variable '@end_date'.
Server: Msg 208, Level 16, State 11, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ydm
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Checksum with ymd
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.F_TABLE_DATE'.
Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.F_TABLE_DATE'.

CardGunner

cardgunner

326 Posts

Posted - 2008-07-31 : 13:36:19

Wow!

Thanks KH(Khtan) for the nudge.

I changed @start_date and @end_date to @START_DATE and @END_DATE and everyhting ran.

I then took your select statement and added my info as below and it came up with 120 days which should be accurate.

 declare @s_date datetime
declare @e_date datetime
select @s_date='2008-03-11 00:00:00.000'
select @e_date='2008-08-26 00:00:00.000'

SELECT COUNT(*)
FROM   	F_TABLE_DATE(@s_date, @e_date) d left JOIN 
(select '2008-7-04 00:00:00.000' [DATE]
)  h ON d.[DATE] = h.[DATE]
WHERE  h.[DATE] IS NULL
AND    d.[DAY_OF_WEEK] BETWEEN 2 AND 6

I know what I did to get it but I don't know what it all means.

I looked and there is only a new function in my db right?

This is really great! Thank you very much.

CardGunner

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-31 : 14:40:21

I hope that you realize that the guery against F_TABLE_DATE is only giving you a count of weekdays.

If you want to exclude holidays, you will need to create a table with the holidays, and join to the output from your query to exclude holidays.

CODO ERGO SUM

cardgunner

326 Posts

Posted - 2008-07-31 : 15:18:04

Yup, that I got. I tested with just one date and it worked.

I may still have an issue cause I'm trying to find the number of working days from a datefield in a table to a set date. Where my results will be say 262 records long.

This works for two set dates @s_date and @e_date.

How do I do it for a 262 records?

CardGunner

Corneliavnp
Starting Member

2 Posts

Posted - 2013-06-26 : 14:21:13

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources