January 5, 2016 at 7:54 am
Hi all!
If I in a very simple example writes:
select DATEDIFF(day,'20150104','20150110')+1
the result is 7 for this week.
But i only want to count monday - Friday, i.e. the result should be 5 instead.
The days could be found via something like:
set datefirst 7
select DATEPART(dw,'20150109')
where the result shold be in (1,2,3,4,5)
But does DATEDIFF support this?
best regards
Edvard Korsbæk
January 5, 2016 at 7:58 am
Edvard
Not directly, no. You could do some additional fancy date arithmetic to subtract Saturdays or Sundays, or you could create a Dates table, and join to that, counting only the rows that are not marked as weekends.
John
January 5, 2016 at 8:30 am
Try creating and using a date table...much simpler. This script will create an awesome one.
Full disclosure - I found this script here - I am not the author...
/****** Object: Table [dbo].[DateDimension] Script Date: 1/5/2016 9:27:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DateDimension](
[DateKey] [int] NOT NULL,
[Date] [date] NOT NULL,
[Day] [tinyint] NOT NULL,
[DaySuffix] [char](2) NOT NULL,
[Weekday] [tinyint] NOT NULL,
[WeekDayName] [varchar](10) NOT NULL,
[IsWeekend] [bit] NOT NULL,
[IsHoliday] [bit] NOT NULL,
[HolidayText] [varchar](64) SPARSE NULL,
[DOWInMonth] [tinyint] NOT NULL,
[DayOfYear] [smallint] NOT NULL,
[WeekOfMonth] [tinyint] NOT NULL,
[WeekOfYear] [tinyint] NOT NULL,
[ISOWeekOfYear] [tinyint] NOT NULL,
[Month] [tinyint] NOT NULL,
[MonthName] [varchar](10) NOT NULL,
[Quarter] [tinyint] NOT NULL,
[QuarterName] [varchar](6) NOT NULL,
[Year] [int] NOT NULL,
[MMYYYY] [char](6) NOT NULL,
[MonthYear] [char](7) NOT NULL,
[FirstDayOfMonth] [date] NOT NULL,
[LastDayOfMonth] [date] NOT NULL,
[FirstDayOfQuarter] [date] NOT NULL,
[LastDayOfQuarter] [date] NOT NULL,
[FirstDayOfYear] [date] NOT NULL,
[LastDayOfYear] [date] NOT NULL,
[FirstDayOfNextMonth] [date] NOT NULL,
[FirstDayOfNextYear] [date] NOT NULL,
PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE #dim
(
[date] DATE PRIMARY KEY,
[day] AS DATEPART(DAY, [date]),
[month] AS DATEPART(MONTH, [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]),
0)),
[MonthName] AS DATENAME(MONTH, [date]),
[week] AS DATEPART(WEEK, [date]),
[ISOweek] AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek] AS DATEPART(WEEKDAY, [date]),
[quarter] AS DATEPART(QUARTER, [date]),
[year] AS DATEPART(YEAR, [date]),
FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
Style112 AS CONVERT(CHAR(8), [date], 112),
Style101 AS CONVERT(CHAR(10), [date], 101)
);
DECLARE@StartDate DATE = '19900101',
@NumberOfYears INT = 50;
SET DATEFIRST 7;
SET DATEFORMAT MDY;
SET LANGUAGE US_ENGLISH;
DECLARE@CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
INSERT#dim
(
[date]
)
SELECT[y].[d]
FROM(SELECTd = DATEADD(DAY, [x].[rn] - 1, @StartDate)
FROM
(SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY [s1].[object_id])
FROM
[sys].[all_objects] AS s1
CROSS JOIN [sys].[all_objects] AS s2
-- on my system this would support > 5 million days
ORDER BY [s1].[object_id]
) AS x
) AS y;
INSERTdbo.DateDimension WITH (TABLOCKX)
SELECTDateKey = CONVERT(INT, Style112),
[Date] = [date],
[Day] = CONVERT(TINYINT, [day]),
DaySuffix = CONVERT(CHAR(2), CASEWHEN [day] / 10 = 1 THEN 'th'
ELSE CASE RIGHT([day], 1)
WHEN '1' THEN 'st'
WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd'
ELSE 'th'
END
END),
[Weekday] = CONVERT(TINYINT, [DayOfWeek]),
[WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
[IsWeekend] = CONVERT(BIT, CASEWHEN [DayOfWeek] IN (1, 7) THEN 1
ELSE 0
END),
[IsHoliday] = CONVERT(BIT, 0),
HolidayText = CONVERT(VARCHAR(64), NULL),
[DOWInMonth] = CONVERT(TINYINT, ROW_NUMBER() OVER (PARTITION BY FirstOfMonth,
[DayOfWeek] ORDER BY [date])),
[DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER (PARTITION BY [year],
[month] ORDER BY [week])),
WeekOfYear = CONVERT(TINYINT, [week]),
ISOWeekOfYear = CONVERT(TINYINT, ISOweek),
[Month] = CONVERT(TINYINT, [month]),
[MonthName] = CONVERT(VARCHAR(10), [MonthName]),
[Quarter] = CONVERT(TINYINT, [quarter]),
QuarterName = CONVERT(VARCHAR(6), CASE [quarter]
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END),
[Year] = [year],
MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),
MonthYear = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
FROM#dim
OPTION(MAXDOP 1);
GO
;
WITHxAS (SELECTDateKey,
[Date],
IsHoliday,
HolidayText,
FirstDayOfYear,
DOWInMonth,
[MonthName],
[WeekDayName],
[Day],
LastDOWInMonth = ROW_NUMBER() OVER (PARTITION BY FirstDayOfMonth,
[Weekday] ORDER BY [Date] DESC)
FROMdbo.DateDimension
)
UPDATEx
SET[x].[IsHoliday] = 1,
[x].[HolidayText] = CASEWHEN ([x].[Date] = [x].[FirstDayOfYear])
THEN 'New Year''s Day'
WHEN (
[x].[DOWInMonth] = 3
AND [x].[MonthName] = 'January'
AND [x].[WeekDayName] = 'Monday'
) THEN 'Martin Luther King Day' -- (3rd Monday in January)
WHEN (
[x].[DOWInMonth] = 3
AND [x].[MonthName] = 'February'
AND [x].[WeekDayName] = 'Monday'
) THEN 'President''s Day' -- (3rd Monday in February)
WHEN (
[x].[LastDOWInMonth] = 1
AND [x].[MonthName] = 'May'
AND [x].[WeekDayName] = 'Monday'
) THEN 'Memorial Day' -- (last Monday in May)
WHEN (
[x].[MonthName] = 'July'
AND [x].[Day] = 4
) THEN 'Independence Day' -- (July 4th)
WHEN (
[x].[DOWInMonth] = 1
AND [x].[MonthName] = 'September'
AND [x].[WeekDayName] = 'Monday'
) THEN 'Labour Day' -- (first Monday in September)
WHEN (
[x].[DOWInMonth] = 2
AND [x].[MonthName] = 'October'
AND [x].[WeekDayName] = 'Monday'
) THEN 'Columbus Day' -- Columbus Day (second Monday in October)
WHEN (
[x].[MonthName] = 'November'
AND [x].[Day] = 11
) THEN 'Veterans'' Day' -- Veterans' Day (November 11th)
WHEN (
[x].[DOWInMonth] = 4
AND [x].[MonthName] = 'November'
AND [x].[WeekDayName] = 'Thursday'
) THEN 'Thanksgiving Day' -- Thanksgiving Day (fourth Thursday in November)
WHEN (
[x].[MonthName] = 'December'
AND [x].[Day] = 25
) THEN 'Christmas Day'
END
WHERE([x].[Date] = [x].[FirstDayOfYear])
OR (
[x].[DOWInMonth] = 3
AND [x].[MonthName] = 'January'
AND [x].[WeekDayName] = 'Monday'
)
OR (
[x].[DOWInMonth] = 3
AND [x].[MonthName] = 'February'
AND [x].[WeekDayName] = 'Monday'
)
OR (
[x].[LastDOWInMonth] = 1
AND [x].[MonthName] = 'May'
AND [x].[WeekDayName] = 'Monday'
)
OR (
[x].[MonthName] = 'July'
AND [x].[Day] = 4
)
OR (
[x].[DOWInMonth] = 1
AND [x].[MonthName] = 'September'
AND [x].[WeekDayName] = 'Monday'
)
OR (
[x].[DOWInMonth] = 2
AND [x].[MonthName] = 'October'
AND [x].[WeekDayName] = 'Monday'
)
OR (
[x].[MonthName] = 'November'
AND [x].[Day] = 11
)
OR (
[x].[DOWInMonth] = 4
AND [x].[MonthName] = 'November'
AND [x].[WeekDayName] = 'Thursday'
)
OR (
[x].[MonthName] = 'December'
AND [x].[Day] = 25
);
GO
UPDATEd
SET[d].[IsHoliday] = 1,
[d].[HolidayText] = 'Black Friday'
FROMdbo.DateDimension AS d
INNER JOIN (SELECTDateKey,
[Year],
[DayOfYear]
FROMdbo.DateDimension
WHEREHolidayText = 'Thanksgiving Day'
) AS src
ON [d].[Year] = [src].[Year]
AND [d].[DayOfYear] = [src].[DayOfYear] + 1;
GO
CREATE FUNCTION dbo.GetEasterHolidays (@year INT)
RETURNS TABLE
WITHSCHEMABINDING
AS
RETURN
(
WITHxAS (SELECT[Date] = CONVERT(DATE, RTRIM(@year) + '0'
+ RTRIM([d].[Month]) + RIGHT('0' + RTRIM([d].[Day]), 2))
FROM(SELECT[m].[Month],
[Day] = [m].[DaysToSunday] + 28 - (31
* ([m].[Month]
/ 4))
FROM
(SELECT[Month] = 3 + ([dts].[DaysToSunday] + 40)
/ 44,
[dts].[DaysToSunday]
FROM
(SELECTDaysToSunday = [paschal].[paschal]
- ((@year + @year / 4
+ [paschal].[paschal] - 13)
% 7)
FROM
(SELECTpaschal = [epact].[epact]
- ([epact].[epact] / 28)
FROM
(SELECTepact = (24 + 19
* (@year % 19))
% 30
) AS epact
) AS paschal
) AS dts
) AS m
) AS d
)
SELECT[x].[Date],
HolidayName = 'Easter Sunday'
FROMx
UNION ALL
SELECTDATEADD(DAY, -2, [x].[Date]),
'Good Friday'
FROMx
UNION ALL
SELECTDATEADD(DAY, 1, [x].[Date]),
'Easter Monday'
FROMx
);
GO
;
WITHxAS (SELECT[d].[Date],
[d].[IsHoliday],
[d].[HolidayText],
h.HolidayName
FROMdbo.DateDimension AS d
CROSS APPLY dbo.GetEasterHolidays([d].[Year]) AS h
WHERE[d].[Date] = h.[Date]
)
UPDATEx
SET[x].[IsHoliday] = 1,
[x].[HolidayText] = HolidayName;
GO
UPDATE dd SET [dd].[IsHoliday] = 0, [dd].[HolidayText] = null
FROM dbo.DateDimension dd
WHERE [IsHoliday] = 1
AND [HolidayText] NOT IN (
'New Year''s Day',
'Memorial Day',
'Independence Day',
'Labour Day',
'Thanksgiving Day',
'Christmas Day'
)
GO
DROP TABLE [#dim];
GO
January 5, 2016 at 8:56 am
Ok, I think what you are asking is you only want the number of weekdays between dates, correct?
DECLARE @Start DATETIME
DECLARE @Stop DATETIME
DECLARE @NDAYS INT -- Number of Weekdays between dates
SET @Start = '2015/01/04' -- Start Date you wanted from your code
SET @Stop = '2015/01/10' -- Stop Date you wanted from your code
SELECT @NDAYS =
(DATEDIFF(dd, @Start, @Stop) + 1)
-(DATEDIFF(wk, @Start, @Stop) * 2)
-(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @Stop) = 'Saturday' THEN 1 ELSE 0 END)
January 5, 2016 at 10:11 pm
Thanks all!
It took me quite some time to understand the solution from 'SQLMark2012
', but it seems rock stable.
Best regards
Edvard Korsbæk
January 5, 2016 at 10:26 pm
Yes, its awesome:-)!
Just a little comment - Cant find Easter as holiday.
Could be done by the way.
Best regards
Edvard Korsbæk
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply