在SQL Server中一周的第一个BusinessDay [英] First BusinessDay of a week in sql server

问题描述

我有一张基于此表的独立假期表，我需要计算本周的第一个工作日。

通常一周的第一个工作日是星期一。假设星期一有假期，那么查询应该说星期二是一周的第一个工作日。如果星期一和星期二都是假日，那么查询应该说星期三是第一个业务
天。

请帮助我获得通用查询。

日期表包含整个2019（365行）数据，假日表有2019（10行）假期列表。

以下是我的查询，仅在周一称为'第一业务Day'

选择左边的情况（DATENAME（dw，[日期]），3）='星期一'和H.Date是空的和WeekDayType ='工作日' 然后'第一个工作日'结束日
来自[日期]

左加入假日H ON DD。[日期] = FHS.SQLDate

谢谢！

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-功能/

Hi,

i have an seperate holiday table based on this table i need to calculate first business day of this week.

Usually first business day of the week in a year is Monday. suppose if any holidays lies on Monday then query should be say Tuesday is a first business day of the week. If both Monday and Tuesday are Holiday then query should say wednesday is a first business day.

Kindly help me to get the generic query.

Date Table is having entire 2019 (365 rows) data and holiday table have 2019(10 rows) holiday list.

Below is my query which says only on Monday as 'First Business Day'

Select CASE WHEN LEFT(DATENAME(dw,[Date]),3) = 'Mon' AND H.Date IS NULL AND WeekDayType = 'Weekday'  THEN 'First Business Day' END
From [Date]
Left Join Holiday H ON DD.[Date] = FHS.SQLDate

Thanks!

解决方案

Use a standard calendar table:

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

这篇关于在SQL Server中一周的第一个BusinessDay的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！