June 23, 2010 at 12:57 am
Hi,
Can you help me with a function that calculate turn around time in hours and using only this hours:
Monday to Friday is 07H00 to 18H00
Saturday is 08H00 t0 13H00
Sunday excluded.
Will highly appreciate any help.
Thanks indvance
June 23, 2010 at 3:36 am
This was removed by the editor as SPAM
June 23, 2010 at 4:27 am
Hi Thanks you very much for the respond.
I just have a problem with your code. When I try dates that includes weekends, I got this message:
(Start Saturday 12H00 and End Monday 09H00)
select @Start = '2010-06-19 12:00:00', @end = '2010-06-22 08:00:00'
Results
Msg 242, Level 16, State 3, Line 14
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
(1 row(s) affected)
Again when I try Start and End date that happend on the same date, I got a negative figure
select @Start = '2010-06-21 12:00:00', @end = '2010-06-21 13:59:00'
Results
-21
Please assist.
June 23, 2010 at 5:38 am
This was removed by the editor as SPAM
June 23, 2010 at 5:52 am
Note that this has syntax issues with what stewart posted if you are not using SQL 2008; 2005 hates you for a few things (no += operator, can't assign a default value to local variable, no DATE datatype)
That is a nice solution stewart! i never thought i might need elapsed business time, but this idea's going into my snippets.
Thanks stewart.
Lowell
June 23, 2010 at 5:57 am
*NOTE* I didn't write any of this.
Function 1
CREATE FUNCTION dbo.F_table_date (@FIRST_DATE DATETIME,
@LAST_DATE DATETIME)
/*
Function: dbo.F_TABLE_DATE
This function returns a date table containing all dates
from @FIRST_DATE through @LAST_DATE inclusive.
@FIRST_DATE must be less than or equal to @LAST_DATE.
The valid date range is 1754-01-01 through 9997-12-31.
If any input parameters are invalid, the fuction will produce
an error.
The table returned by F_TABLE_DATE contains a date and
columns with many calculated attributes of that date.
It is designed to make it convenient to get various commonly
needed date attributes without having to program and test
the same logic in many applications.
F_TABLE_DATE is primarily intended to load a permanant
date table, but it can be used directly by an application
when the date range needed falls outside the range loaded
in a permanant table.
If F_TABLE_DATE is used to load a permanant table, the create
table code can be copied from this function. For a permanent
date table, most columns should be indexed to produce the
best application performance.
Column Descriptions
------------------------------------------------------------------
DATE_ID
Unique ID = Days since 1753-01-01
DATE
Date at Midnight(00:00:00.000)
NEXT_DAY_DATE
Next day after DATE at Midnight(00:00:00.000)
Intended to be used in queries against columns
containing datetime values (1998-12-13 14:35:16)
that need to join to a DATE.
Example:
from
MyTable a
join
DATE b
ona.DateTimeCol >= b. DATEand
a.DateTimeCol < b.NEXT_DAY_DATE
YEAR
Year number in format YYYY, Example = 2005
YEAR_QUARTER
Year and Quarter number in format YYYYQ, Example = 20052
YEAR_MONTH
Year and Month number in format YYYYMM, Example = 200511
YEAR_DAY_OF_YEAR
Year and Day of Year number in format YYYYDDD, Example = 2005364
QUARTER
Quarter number in format Q, Example = 4
MONTH
Month number in format MM, Example = 11
DAY_OF_YEAR
Day of Year number in format DDD, Example = 362
DAY_OF_MONTH
Day of Month number in format DD, Example = 31
DAY_OF_WEEK
Day of week number, Sun=1, Mon=2, Tue=3, Wed=4, Thu=5, Fri=6, Sat=7
YEAR_NAME
Year name text in format YYYY, Example = 2005
YEAR_QUARTER_NAME
Year Quarter name text in format YYYY QQ, Example = 2005 Q3
YEAR_MONTH_NAME
Year Month name text in format YYYY MMM, Example = 2005 Mar
YEAR_MONTH_NAME_LONG
Year Month long name text in format YYYY MMMMMMMMM,
Example = 2005 September
QUARTER_NAME
Quarter name text in format QQ, Example = Q1
MONTH_NAME
Month name text in format MMM, Example = Mar
MONTH_NAME_LONG
Month long name text in format MMMMMMMMM, Example = September
WEEKDAY_NAME
Weekday name text in format DDD, Example = Tue
WEEKDAY_NAME_LONG
Weekday long name text in format DDDDDDDDD, Example = Wednesday
START_OF_YEAR_DATE
First Day of Year that DATE is in
END_OF_YEAR_DATE
Last Day of Year that DATE is in
START_OF_QUARTER_DATE
First Day of Quarter that DATE is in
END_OF_QUARTER_DATE
Last Day of Quarter that DATE is in
START_OF_MONTH_DATE
First Day of Month that DATE is in
END_OF_MONTH_DATE
Last Day of Month that DATE is in
*** Start and End of week columns allow selections by week
*** for any week start date needed.
START_OF_WEEK_STARTING_SUN_DATE
First Day of Week starting Sunday that DATE is in
END_OF_WEEK_STARTING_SUN_DATE
Last Day of Week starting Sunday that DATE is in
START_OF_WEEK_STARTING_MON_DATE
First Day of Week starting Monday that DATE is in
END_OF_WEEK_STARTING_MON_DATE
Last Day of Week starting Monday that DATE is in
START_OF_WEEK_STARTING_TUE_DATE
First Day of Week starting Tuesday that DATE is in
END_OF_WEEK_STARTING_TUE_DATE
Last Day of Week starting Tuesday that DATE is in
START_OF_WEEK_STARTING_WED_DATE
First Day of Week starting Wednesday that DATE is in
END_OF_WEEK_STARTING_WED_DATE
Last Day of Week starting Wednesday that DATE is in
START_OF_WEEK_STARTING_THU_DATE
First Day of Week starting Thursday that DATE is in
END_OF_WEEK_STARTING_THU_DATE
Last Day of Week starting Thursday that DATE is in
START_OF_WEEK_STARTING_FRI_DATE
First Day of Week starting Friday that DATE is in
END_OF_WEEK_STARTING_FRI_DATE
Last Day of Week starting Friday that DATE is in
START_OF_WEEK_STARTING_SAT_DATE
First Day of Week starting Saturday that DATE is in
END_OF_WEEK_STARTING_SAT_DATE
Last Day of Week starting Saturday that DATE is in
*** Sequence No columns are intended to allow easy offsets by
*** Quarter, Month, or Week for applications that need to look at
*** Last or Next Quarter, Month, or Week. Thay can also be used to
*** generate dynamic cross tab results by Quarter, Month, or Week.
QUARTER_SEQ_NO
Sequential Quarter number as offset from Quarter starting 1753/01/01
MONTH_SEQ_NO
Sequential Month number as offset from Month starting 1753/01/01
WEEK_STARTING_SUN_SEQ_NO
Sequential Week number as offset from Week starting Sunday, 1753/01/07
WEEK_STARTING_MON_SEQ_NO
Sequential Week number as offset from Week starting Monday, 1753/01/01
WEEK_STARTING_TUE_SEQ_NO
Sequential Week number as offset from Week starting Tuesday, 1753/01/02
WEEK_STARTING_WED_SEQ_NO
Sequential Week number as offset from Week starting Wednesday, 1753/01/03
WEEK_STARTING_THU_SEQ_NO
Sequential Week number as offset from Week starting Thursday, 1753/01/04
WEEK_STARTING_FRI_SEQ_NO
Sequential Week number as offset from Week starting Friday, 1753/01/05
WEEK_STARTING_SAT_SEQ_NO
Sequential Week number as offset from Week starting Saturday, 1753/01/06
JULIAN_DATE
Julian Date number as offset from noon on January 1, 4713 BCE
to noon on day of DATE in system of Joseph Scaliger
MODIFIED_JULIAN_DATE
Modified Julian Date number as offset from midnight(00:00:00.000) on
1858/11/17 to midnight(00:00:00.000) on day of DATE
ISO_DATE
ISO 8601 Date in format YYYY-MM-DD, Example = 2004-02-29
ISO_YEAR_WEEK_NO
ISO 8601 year and week in format YYYYWW, Example = 200403
ISO_WEEK_NO
ISO 8601 week of year in format WW, Example = 52
ISO_DAY_OF_WEEK
ISO 8601 Day of week number,
Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7
ISO_YEAR_WEEK_NAME
ISO 8601 year and week in format YYYY-WNN, Example = 2004-W52
ISO_YEAR_WEEK_DAY_OF_WEEK_NAME
ISO 8601 year, week, and day of week in format YYYY-WNN-D,
Example = 2004-W52-2
DATE_FORMAT_YYYY_MM_DD
Text date in format YYYY/MM/DD, Example = 2004/02/29
DATE_FORMAT_YYYY_M_D
Text date in format YYYY/M/D, Example = 2004/2/9
DATE_FORMAT_MM_DD_YYYY
Text date in format MM/DD/YYYY, Example = 06/05/2004
DATE_FORMAT_M_D_YYYY
Text date in format M/D/YYYY, Example = 6/5/2004
DATE_FORMAT_MMM_D_YYYY
Text date in format MMM D, YYYY, Example = Jan 4, 2006
DATE_FORMAT_MMMMMMMMM_D_YYYY
Text date in format MMMMMMMMM D, YYYY, Example = September 3, 2004
DATE_FORMAT_MM_DD_YY
Text date in format MM/DD/YY, Example = 06/05/97
DATE_FORMAT_M_D_YY
Text date in format M/D/YY, Example = 6/5/97
*/
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
Function 2
CREATE FUNCTION Fn_workinghour (@start_date DATETIME,
@end_date DATETIME)
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @work_calender AS TABLE (
[day_number] [VARCHAR] (50),
[day_name] [VARCHAR] (50),
[begin_time] [DATETIME],
[end_time] [DATETIME],
[duration] [REAL] )
INSERT INTO @work_calender
SELECT 1,
'Monday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 2,
'Tuesday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 3,
'Wednesday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 4,
'Thursday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 5,
'Friday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 6,
'Saturday',
'8:00:00 AM',
'1:00:00 PM',
18000
UNION ALL
SELECT 7,
'Sunday',
'12:00:00 AM',
'12:00:00 AM',
0
DECLARE @total_hours DECIMAL(10, 2)
SELECT @total_hours = SUM(CASE
WHEN Dateadd(DAY, Datediff(DAY, 0, @start_date
),
0)
=
Dateadd(DAY, Datediff(DAY, 0,
@end_date), 0) THEN
Datediff(SECOND, @start_date, @end_date)
WHEN [DATE] = Dateadd(DAY, Datediff(DAY, 0,
@start_date), 0
) THEN
CASE
WHEN
@start_date > [DATE] + begin_time THEN
Datediff(SECOND, @start_date, [DATE] +
end_time)
ELSE duration
END
WHEN [DATE] = Dateadd(DAY, Datediff(DAY, 0,
@end_date), 0)
THEN
CASE
WHEN
@end_date < [DATE] + end_time THEN
Datediff(SECOND, [DATE] + begin_time,
@end_date)
ELSE duration
END
ELSE duration
END) / 60.0 / 60.0
FROM F_table_date(@start_date, @end_date) d
INNER JOIN @work_calender c
ON d.weekday_name_long = c.day_name
RETURN @total_hours
END
Execution and Output
DECLARE @table AS TABLE(
startday DATETIME,
endday DATETIME)
INSERT INTO @table
SELECT '22-Jun-2010 13:00:00', '22-Jun-2010 13:15:00'
UNION ALL SELECT '22-Jun-2007 13:00:00', '23-Feb-2010 13:15:00'
UNION ALL SELECT '21-Jun-2010 13:00:00', '23-Jun-2010 13:00:00'
SELECT dbo.fn_workinghour(startday, endday) FROM @table
Ouput
/*
---------------------------------------
0.25
8367.25
22.00
*/
June 23, 2010 at 6:06 am
Something like this....
Create Table Calendar
(
DateCol datetime Primary key,
DayNo tinyint)
go
with cte
as
(
Select top (3000) Row_number() over (order by (Select null)) as RowN
from syscolumns a cross join syscolumns b
)
insert into Calendar(DateCol,DayNo)
Select dateadd(dd,RowN,'20080101'),
datePart(dw,dateadd(dd,RowN,'20080101'))-1 -- 0 Should be sunday
from cte
go
Create Table WorkHrs
(
DayNum tinyint Primary key,
StartHH tinyint,
EndHH tinyint
)
go
insert into WorkHrs values(1,7,18)
insert into WorkHrs values(2,7,18)
insert into WorkHrs values(3,7,18)
insert into WorkHrs values(4,7,18)
insert into WorkHrs values(5,7,18)
insert into WorkHrs values(6,8,13)
go
Declare @StartDateTime DateTime
Declare @EndDateTime DateTime
Select @StartDateTime= '20100101 3:00'
Select @EndDateTime= '20100106 14:00'
Declare @StartScanTime DateTime
Declare @EndScanTime DateTime
Select @StartScanTime = DATEDIFF(DAY,0,@StartDateTime)
Select @EndScanTime = DATEDIFF(DAY,0,@EndDateTime)
;with CTEHrs
as
(
Select DateCol,
case when DateCol = @StartScanTime and datePart(hh,@StartDateTime) > StartHH
then datePart(hh,@StartDateTime) else startHH end as StartHH,
case when DateCol = @EndScanTime and datePart(hh,@EndDateTime) < EndHH
then datePart(hh,@EndDateTime) else EndHH end as EndHH
from Calendar
join WorkHrs
on Calendar.DayNo = WorkHrs.DayNum
where DateCol between @StartScanTime and @EndScanTime
)
Select SUM(EndHH-StartHH)
from CTEHrs
June 23, 2010 at 6:11 am
This was removed by the editor as SPAM
June 23, 2010 at 7:39 am
pitso.maceke (6/23/2010)
...I just have a problem with your code. When I try dates that includes weekends, I got this message:
(Start Saturday 12H00 and End Monday 09H00)
...
First of all you have problem with your question! In a future, it would be helpfull to provide the script which sets up the sample of data for your case and expected results for your input data.
Otherwise, it is not clear what you are really asking about. I've only figured it out from other people's answers.
Stewartc...,
Are you sure that DATEPART(dw,@start) = 7 is Saturday everywhere in the world? Sorry, but this is not the case, it's really depends on DATEFIRST settings (google SET DATEFIRST and @@DATEFIRST function). Using DATENAME function will be a safer option...
Now the code:
-- here we populate the working hours table
-- I believe it would be better to create it as permanent config table!
;with wdcte
as
(
select top 7 DATENAME(WEEKDAY,GETDATE() - ROW_NUMBER() OVER(ORDER BY (select null))) AS WD
from sys.columns
)
select WD
,case when WD = 'Saturday' then '08:00:00' -- start time for Saturday
else '07:00:00' -- start time for Monday-Friday
end as startTime
,case when WD = 'Saturday' then '13:00:00' -- end time for Saturday
else '18:00:00' -- end time for Monday-Friday
end as endTime
into #tWD
from wdcte
where WD != 'Sunday' -- Sundays excluded
--
declare @startDate datetime
declare @endDate datetime
set @startDate = '20100610'
set @endDate = '20100616 10:15:00'
-- get large working dataset of dates starting from @startDate , hopefully it will contain enough days
;with dts
as
(
select cast(convert(varchar(8),@startDate,112) as datetime) + ROW_NUMBER() OVER(ORDER BY (select null)) - 1 as dt
from sys.columns s1 cross join sys.columns s2
)
-- get the relevant date range
, dtrange
as
(
select dt, datename(weekday,dt) as wd
from dts
where dt <= convert(varchar(8),@endDate,112) -- we only interesting the dates upto the @endDate (including)
)
-- for each day in the range we need to set proper start and end times!
, timeinrange
as
(
select
case when dr.dt = convert(varchar(8),@startDate,112)
then
case when cast(convert(varchar(8),dr.dt,112) + ' ' + t.startTime as datetime) > @startDate
then cast(convert(varchar(8),dr.dt,112) + ' ' + t.startTime as datetime)
else @startDate
end
else cast(convert(varchar(8),dr.dt,112) + ' ' + t.startTime as datetime)
end dtST
,case when dr.dt = convert(varchar(8),@endDate,112)
then
case when cast(convert(varchar(8),dr.dt,112) + ' ' + t.endTime as datetime) < @endDate
then cast(convert(varchar(8),dr.dt,112) + ' ' + t.endTime as datetime)
else @endDate
end
else cast(convert(varchar(8),dr.dt,112) + ' ' + t.endTime as datetime)
end dtET
from dtrange dr
join #tWD t
on t.wd = dr.wd
)
select cast(sum(datediff(second,dtST,dtET))/3600 as varchar(10)) + ' Hrs ' +
cast(sum(datediff(second,dtST,dtET))/60 - sum(datediff(second,dtST,dtET))/3600 * 60 as varchar(10)) + ' Mins'
from timeinrange
where dtET>dtST
I have left something for you to do:
1. "Good" to add check if the number of days in the generated range (sys.columns cross join) is enough
2. "May be" replace CTEs with temp tables to increase performance
3. Wrap it into user defined function if you want.
In general, it is possible to calculate it "in-line" for a set of dates.
But if you want a function, CLR would do that calculation much faster than UDF.
August 3, 2010 at 9:19 am
I was searching too for a code like this and based on the last post of Eugene (A BIG THANK YOU FOR YOUR CODE!!!), I've play a little with it and, just like you was suggesting, I've create one or two physical tables in order to improve calculation speed.
All merits are for Eugene.
DECLARE @startDate datetime
DECLARE @endDate datetime
SET @startDate = '20100802 19:30:00'
SET @endDate = '20100803 08:05:00'
--IF object_id('dbo.tblBusinessHours') IS NOT NULL DROP TABLE dbo.tblBusinessHours;
--IF object_id('dbo.tblCalendar') IS NOT NULL DROP TABLE dbo.tblCalendar;
-- http://www.sqlservercentral.com/Forums/Topic941533-392-1.aspx
-- Create a table containing the Business hours.
--
-- The code below will generate a table having this content :
--
-- Day startTime endTime
-- ------------------------------ --------- --------
-- Monday 08:00:00 18:00:00
-- Friday 08:00:00 18:00:00
-- Thursday 08:00:00 18:00:00
-- Wednesday 08:00:00 18:00:00
-- Tuesday 08:00:00 18:00:00
-- Create a business hours table (prefer a table instead a CTE for performance purpose)
IF object_id('dbo.tblBusinessHours') IS NULL
BEGIN
WITH wdcte AS (
SELECT TOP 7 DATENAME(WEEKDAY,GETDATE() - ROW_NUMBER() OVER(ORDER BY (select null))) AS [DayName]
FROM sys.columns
)
SELECT [DayName],
CASE WHEN [DayName] = 'Saturday' THEN '08:00:00' ELSE '08:00:00' END AS startTime, -- start time for Saturday / Monday-Friday
CASE WHEN [DayName] = 'Saturday' THEN '13:00:00' ELSE '18:00:00' END AS endTime -- end time for Saturday / Monday-Friday
INTO dbo.tblBusinessHours
FROM wdcte
WHERE [DayName] NOT IN ('Saturday','Sunday'); -- We don't work Saturday & Sunday; but this can change ;-)
END;
-- Create a calendar table (prefer a table instead a CTE for performance purpose)
IF object_id('dbo.tblCalendar') IS NULL
BEGIN
CREATE TABLE [dbo].[tblCalendar](
[Day] [datetime] NOT NULL,
[DayName] [varchar](10) NOT NULL,
CONSTRAINT [PK_tblCalendar] PRIMARY KEY CLUSTERED (
[Day] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @dteFirst AS DateTime
SET @dteFirst = '20050101 00:00:00'
INSERT INTO [dbo].[tblCalendar] ([Day], [DayName])
SELECT cast(convert(varchar(8),@dteFirst,112) AS datetime) + ROW_NUMBER() OVER(ORDER BY (SELECT null)) - 1 AS [Day], cast('' as varchar(10)) As [DayName]
FROM sys.columns s1 CROSS JOIN sys.columns s2;
UPDATE dbo.tblCalendar SET [DayName]=datename(weekday,[Day]);
END;
-- --------------------------------------------------
-- --------------------------------------------------
-- --------------------------------------------------
-- Take every dates between the interval @startDate and @endDate and, for each days in the range,
-- determine the start/end hour to take in consideration.
--
-- Let's consider the following example :
--
-- @startDate = '20100802 19:30:00' (this is a Monday)
-- @endDate = '20100806 08:15:00' (this is a Friday)
--
-- normal business hours : from 08'00 AM till 06'00 PM (Monday till Friday)
--
-- The timeinrange CTE will then compute these rows :
--
-- dateStart dateEnd
-- ----------------------- -----------------------
-- 2010-08-03 08:00:00.000 2010-08-03 18:00:00.000
-- 2010-08-04 08:00:00.000 2010-08-04 18:00:00.000
-- 2010-08-05 08:00:00.000 2010-08-05 18:00:00.000
-- 2010-08-06 08:00:00.000 2010-08-06 08:15:00.000
--
-- Why ?
--
-- The start date '20100802 19:30:00' falls after the end of normal business hours and then the date of 2nd August won't be counted.
-- The end date '20100806 08:15:00' will only count for 15 minutes since the normal business hours start at 8'00 AM.
WITH timeinrange AS (
SELECT
CASE WHEN cal.[Day] = convert(varchar(8),@startDate,112)
THEN
CASE WHEN cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.startTime as datetime) > @startDate
THEN cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.startTime as datetime)
ELSE @startDate
END
ELSE cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.startTime as datetime)
END dateStart,
CASE WHEN cal.[Day] = convert(varchar(8),@endDate,112)
THEN
CASE WHEN cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.endTime as datetime) < @endDate
THEN cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.endTime as datetime)
ELSE @endDate
END
ELSE cast(convert(varchar(8),cal.[Day],112) + ' ' + bus.endTime as datetime)
END dateEnd
FROM dbo.tblCalendar cal JOIN dbo.tblBusinessHours bus on bus.[DayName] = cal.[DayName]
WHERE cal.[Day] BETWEEN convert(varchar(8),@startDate,112) and convert(varchar(8),@endDate,112)
)
-- Now that the CTE return the correct time period, the SQL just need to return the wanted information
SELECT
convert(varchar(10),
right('0'+cast(sum(datediff(second,dateStart,dateEnd))/3600 AS varchar(10)),2) + ':' +
right('0'+cast(sum(datediff(second,dateStart,dateEnd))/60 - sum(datediff(second,dateStart,dateEnd))/3600 * 60 AS varchar(10)),2) + ':00',108) As ElapsedTime
FROM timeinrange
WHERE dateEnd>dateStart;
Christophe
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply