我正在使用sql server 2008 R2，我正在创建应用程序休假表单。当用户申请休假时，即开始日期&结束日期在两个日期之间，如果是假期，所有日期都应该跳过;每周从
CREATE TABLE #HolidayMaster
(
[HolidayID] [int] IDENTITY(1,1) NOT NULL,
[HolidayDescription] [nvarchar](50) NULL,
[HolidayDate] [date] NULL,
)
INSERT INTO #HolidayMaster
select 'New Year', '2016-01-01'
union
select 'National Developer Day', '2016-01-05'
CREATE TABLE #ShiftMaster
(
[ShiftID] [int] IDENTITY(1,1) NOT NULL,
[Sunday] [float] NULL,
[Monday] [float] NULL,
[Tuesday] [float] NULL,
[Wednesday] [float] NULL,
[Thursday] [float] NULL,
[Friday] [float] NULL,
[Saturday] [float] NULL
)
INSERT INTO #ShiftMaster
select 0,1,1,1,1,1,0
select *,DATENAME (dw,[HolidayDate]) as [DayName] from #HolidayMaster
select * from #ShiftMaster
drop table #HolidayMaster
drop table #ShiftMaster
Declare @LeaveStartDate date = '2013-01-01'
Declare @LeaveEndDate date = '2013-01-06'
--expected out put
DName Date Desc
Friday 2016-01-01 Holiday
Saturday 2016-01-02 WeeklyOff
Sunday 2016-01-03 WeeklyOff
Monday 2016-01-04 Working
Tuesday 2016-01-05 Holiday
Wednesday 2016-01-06 Working
您需要一个calendar表。我使用计数表来生成日期。
Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'
;WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
calendar(dates) as
(
SELECT Dateadd(dd, n - 1, @LeaveStartDate) AS Date
FROM (SELECT Row_number()OVER (ORDER BY n)
FROM Nbrs) D ( n )
WHERE n <= Datediff(day, @LeaveStartDate, @LeaveEndDate)+ 1
)
SELECT DName =d.day_name,
Date = c.dates,
CASE
WHEN HolidayDate IS NOT NULL THEN 'Holiday'
WHEN leav_iden = 0 THEN 'WeeklyOff'
WHEN HolidayDate IS NOT NULL
AND leav_iden = 0 THEN 'Holiday/WeeklyOff'
ELSE 'Working'
END AS [Desc]
FROM calendar c
JOIN (SELECT *
FROM #ShiftMaster
CROSS apply (VALUES ([Sunday],'Sunday'),
([Monday],'Monday'),
([Tuesday],'Tuesday'),
([Wednesday],'Wednesday'),
([Thursday],'Thursday'),
([Friday],'Friday'),
([Saturday],'Saturday') ) cs(leav_iden, day_name)) d
ON Datename(WEEKDAY, c.dates) = d.day_name
LEFT JOIN #HolidayMaster h
ON h.HolidayDate = c.dates
DName Date Desc
----- ---------- -------------
Sunday 2016-01-03 WeeklyOff
Monday 2016-01-04 Working
Tuesday 2016-01-05 Holiday
Wednesday 2016-01-06 Working
Friday 2016-01-01 Holiday
Saturday 2016-01-02 WeeklyOff
try
Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'
;with dts(dt) as (select @LeaveStartDate
union all
select dateadd(day,1,dt) from dts where dt<@LeaveEndDate),
shifmstr as (
select shiftID,'Sunday' as dname,[sunday] tp from #ShiftMaster union all
select shiftID,'Monday' ,Monday from #ShiftMaster union all
select shiftID,'Tuesday' ,Tuesday from #ShiftMaster union all
select shiftID,'Wednesday',Wednesday from #ShiftMaster union all
select shiftID,'Thursday',Thursday from #ShiftMaster union all
select shiftID,'Friday',Friday from #ShiftMaster union all
select shiftID,'Saturday',Saturday from #ShiftMaster)
select datename(dw,dt) DName,Dt,
case when c.HolidayDate is not null then 'Holiday'
when tp=1 then 'Working' else 'WeeklyOff' end descr
from dts a join shifmstr b on
datename(dw,a.dt)=b.dname left join
#HolidayMaster c on a.dt=c.HolidayDate
首先需要根据间隔生成日期。这里展示了一个很好的快速方法。然后，您可以与#HolidayMaster表进行LEFT JOIN，以检查日期是否为假日。
代码应该是这样的:Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'
;WITH Dates_CTE AS (
SELECT TOP (DATEDIFF(DAY, @LeaveStartDate, @LeaveEndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @LeaveStartDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT DATENAME (dw,[Date]) as [DayName],
[Date],
(CASE WHEN DATEPART(dw, [Date]) IN (1, 7) THEN 'WeeklyOff'
WHEN HM.HolidayID IS NOT NULL THEN 'Holiday' -- HM.Description can be used here to actually display holiday name
ELSE 'Working' END) [Desc]
FROM Dates_CTE D
LEFT JOIN #HolidayMaster HM ON HM.HolidayDate = D.[Date]
通常LEFT JOIN .. IS NULL检查比NOT EXISTS慢，所以另一种选择是删除LEFT JOIN并检查是否存在，但我发现这种形式更具可读性。
递归CTE生成日期范围之间的日期，在HolidayMaster上左连接和在ShiftMaster上左连接(我将ShiftMaster更改为具有要连接的日期名称)，然后根据值选择适当的描述:
CREATE TABLE #HolidayMaster
(
[HolidayID] [int] IDENTITY(1,1) NOT NULL,
[HolidayDescription] [nvarchar](50) NULL,
[HolidayDate] [date] NULL,
)
INSERT INTO #HolidayMaster
select 'New Year', '2016-01-01'
union
select 'National Developer Day', '2016-01-05'
CREATE TABLE #ShiftMaster
(
[ShiftID] [int] IDENTITY(1,1) NOT NULL,
[Day] nvarchar(20) NULL,
IsHoliday bit NULL
)
INSERT INTO #ShiftMaster
values ('Sunday', 0), ('Monday', 1), ('Tuesday', 1), ('Wednesday',1), ('Thursday', 1), ('Friday', 1), ('Saturday',0)
select *,DATENAME (dw,[HolidayDate]) as [DayName] from #HolidayMaster
select * from #ShiftMaster
Declare @LeaveStartDate date = '2016-01-01'
Declare @LeaveEndDate date = '2016-01-06'
;WITH Dates AS (
SELECT
[Date] = @LeaveStartDate
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < @LeaveEndDate
) SELECT
DATENAME(dw, [Date])
, [Date]
, CASE WHEN hm.HolidayDescription IS NULL THEN
CASE WHEN sm.IsHoliday = 1 THEN 'Working' ELSE 'Weekly Off' END
ELSE 'Holiday' END AS Description
FROM
Dates
left join #HolidayMaster hm on hm.HolidayDate = [Date]
left join #ShiftMaster sm on sm.Day = DATENAME(dw, [Date])
drop table #HolidayMaster
drop table #ShiftMaster
try this，
DECLARE @LeaveStartDate DATETIME = '2016-01-01'
DECLARE @LeaveEndDate DATETIME = '2016-01-06'
;WITH CTE
AS ( SELECT @LeaveStartDate AS LeaveDate
UNION ALL
SELECT LeaveDate + 1
FROM CTE
WHERE LeaveDate < @LeaveEndDate
)
SELECT * ,DATENAME(WEEKDAY, c.LeaveDate)
FROM CTE c
LEFT JOIN #HolidayMaster h ON c.LeaveDate = h.HolidayDate
WHERE h.HolidayDate IS NULL
AND EXISTS ( SELECT 1
FROM #ShiftMaster s
WHERE (DATENAME(WEEKDAY, c.LeaveDate) = 'Sunday' AND s.Sunday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Monday' AND s.Monday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Tuesday' AND s.Tuesday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Wednesday' AND s.Wednesday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Thursday' AND s.Thursday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Friday' AND s.Friday = 1)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Saturday' AND s.Saturday = 1)
)
如果你想获得所有的日期状态试试这个，
DECLARE @LeaveStartDate DATETIME = '2016-01-01'
DECLARE @LeaveEndDate DATETIME = '2016-01-06'
;WITH CTE
AS ( SELECT @LeaveStartDate AS LeaveDate
UNION ALL
SELECT LeaveDate + 1
FROM CTE
WHERE LeaveDate < @LeaveEndDate
)
SELECT
c.LeaveDate,
DATENAME(WEEKDAY, c.LeaveDate) AS [DayName],
CASE WHEN (DATENAME(WEEKDAY, c.LeaveDate) = 'Sunday' AND s.Sunday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Monday' AND s.Monday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Tuesday' AND s.Tuesday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Wednesday' AND s.Wednesday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Thursday' AND s.Thursday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Friday' AND s.Friday = 0)
OR (DATENAME(WEEKDAY, c.LeaveDate) = 'Saturday' AND s.Saturday = 0)
THEN 'WeeklyOff'
WHEN h.HolidayDate IS NOT NULL
THEN 'Holiday'
ELSE 'Working'
END AS [Status]
FROM CTE c
CROSS JOIN #ShiftMaster s
LEFT JOIN #HolidayMaster h ON c.LeaveDate = h.HolidayDate