SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 number of working days between two dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  09:52:40  Show Profile  Reply with Quote
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 - 07/31/2008 :  09:59:32  Show Profile  Reply with Quote
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')
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/31/2008 :  10:08:56  Show Profile  Reply with Quote

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
Time is always against us

Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  10:14:48  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/31/2008 :  10:16:19  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

elancaster
A very urgent SQL Yakette

United Kingdom
1208 Posts

Posted - 07/31/2008 :  10:16:45  Show Profile  Reply with Quote
VGuyz solution will not work on SQL2000 as it uses a CTE

Em
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  10:18:45  Show Profile  Reply with Quote
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
Time is always against us





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
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  10:21:42  Show Profile  Reply with Quote
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
Go to Top of Page

VGuyz
Posting Yak Master

121 Posts

Posted - 07/31/2008 :  10:46:11  Show Profile  Reply with Quote
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)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/31/2008 :  10:49:47  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/31/2008 :  10:50:31  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/31/2008 :  11:03:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  11:18:39  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/31/2008 :  11:23:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
http://www.sqlservercentral.com/articles/Advanced+Querying/howmanymoremondaysuntiliretire/2475/



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  11:50:14  Show Profile  Reply with Quote
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

Edited by - cardgunner on 07/31/2008 12:01:23
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  13:36:19  Show Profile  Reply with Quote
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

Edited by - cardgunner on 07/31/2008 13:36:49
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/31/2008 :  14:40:21  Show Profile  Reply with Quote
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
Go to Top of Page

cardgunner
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/31/2008 :  15:18:04  Show Profile  Reply with Quote
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
Go to Top of Page

Corneliavnp
Starting Member

Indonesia
2 Posts

Posted - 06/26/2013 :  14:21:13  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.17 seconds. Powered By: Snitz Forums 2000