| Author |
 Topic  |
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
 Posted - 07/31/2008 : 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 - 07/31/2008 : 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)
Singapore
17684 Posts |
Posted - 07/31/2008 : 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
Time is always against us
|
|
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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)
Singapore
17684 Posts |
Posted - 07/31/2008 : 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
Time is always against us
|
|
|
|
elancaster
A very urgent SQL Yakette
United Kingdom
1208 Posts |
Posted - 07/31/2008 : 10:16:45
|
VGuyz solution will not work on SQL2000 as it uses a CTE
Em |
|
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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
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 |
|
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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 - 07/31/2008 : 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)
Singapore
17684 Posts |
Posted - 07/31/2008 : 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
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
17684 Posts |
Posted - 07/31/2008 : 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
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30332 Posts |
Posted - 07/31/2008 : 11:03:57
|
It you can calculate the number of days, that would be better than counting the days.
Try thiscreate 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
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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
Sweden
30332 Posts |
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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 |
Edited by - cardgunner on 07/31/2008 12:01:23 |
|
|
|
cardgunner
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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 |
Edited by - cardgunner on 07/31/2008 13:36:49 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts |
Posted - 07/31/2008 : 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
Constraint Violating Yak Guru
USA
326 Posts |
Posted - 07/31/2008 : 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
Indonesia
2 Posts |
Posted - 06/26/2013 : 14:21:13
|
| unspammed |
|
|
| |
Topic |
|
|
|
number of working days between two dates
