Sql-Server
幫助加入日期表
有一個從使用者那裡獲取兩個參數（BeginDate、EndDate）的現有報告。當使用者輸入兩個日期範圍時，它會在 LeadTime 列中生成兩個日期之間差異的結果。
我需要 LeadTime 列僅計算結果中的星期一至星期五。現在，它只是減去兩列（訂單日期和發貨日期）之間的差異，差異 = LeadTime。我試圖弄清楚如何不計算週末。
即： LeadTime = BeginDate 和 EndDate 之間發生的工作日數。
(@BeginDate datetime, @EndDate datetime, @PostingGroup varchar(50)) AS /* declare @BeginDate datetime = '6/1/2017', @EndDate datetime = '6/30/2017', */ SELECT [Order Date], [Shipment Date], DATEDIFF(D,[Order Date],[Shipment Date]) AS LeadTime, CONVERT(varchar,Order_Number) + '-' + CONVERT(VARCHAR,[Line No_]) AS SalesOrder, [Document Type], [Bill-to Customer No_] AS BillToCustomerNumber, [Bill-to Name], [Ship-to Name], Item, [Posting Group], Quantity, [Unit Price], Sales, --added field [Description] as per Ticket #7860 gpineda 5/29/2019 [Description] FROM [dbo].[BI_Sales] join BI_Sales on DateTable. WHERE Company = 'LLC' AND [Order Date] BETWEEN @BeginDate AND @EndDate AND ([Posting Group] = @PostingGroup OR @PostingGroup = 'ALL') AND [Document Type] <> 'Credit Memo' AND Line_Type = 'Item' AND DATEDIFF(D,[Order Date],[Shipment Date]) <= 7 AND Order_Number <> '' ORDER BY 5我的日期表看起來像這樣：
CREATE TABLE [dbo].[DimDate] ( [DateKey] INT primary key, [Date] DATETIME, [FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format [FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday [DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7 [DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7 [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month [DayOfWeekInYear] VARCHAR(2), [DayOfQuarter] VARCHAR(3), [DayOfYear] VARCHAR(3), [WeekOfMonth] VARCHAR(1),-- Week Number of Month [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter [WeekOfYear] VARCHAR(2),--Week Number of the Year [Month] VARCHAR(2), --Number of the Month 1 to 12 [MonthName] VARCHAR(9),--January, February etc [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter [Quarter] CHAR(1), [QuarterName] VARCHAR(9),--First,Second.. [Year] CHAR(4),-- Year value of Date stored in Row [YearName] CHAR(7), --CY 2012,CY 2013 [MonthYear] CHAR(10), --Jan-2013,Feb-2013 [MMYYYY] CHAR(6), [FirstDayOfMonth] DATE, [LastDayOfMonth] DATE, [FirstDayOfQuarter] DATE, [LastDayOfQuarter] DATE, [FirstDayOfYear] DATE, [LastDayOfYear] DATE, [IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday [IsWeekday] BIT,-- 0=Week End ,1=Week Day [HolidayUSA] VARCHAR(50),--Name of Holiday in US [IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday [HolidayUK] VARCHAR(50) Null --Name of Holiday in UK ) GO /********************************************************************************************/ --Specify Start Date and End date here --Value of Start Date Must be Less than Your End Date DECLARE @StartDate DATETIME = '01/01/2015' --Starting value of Date Range DECLARE @EndDate DATETIME = '01/01/2030' --End Value of Date Range --Temporary Variables To Hold the Values During Processing of Each Date of Year DECLARE @DayOfWeekInMonth INT, @DayOfWeekInYear INT, @DayOfQuarter INT, @WeekOfMonth INT, @CurrentYear INT, @CurrentMonth INT, @CurrentQuarter INT /*Table Data type to store the day of week count for the month and year*/ DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT) INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0) INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0) --Extract and assign various parts of Values from Current Date to Variable DECLARE @CurrentDate AS DATETIME = @StartDate SET @CurrentMonth = DATEPART(MM, @CurrentDate) SET @CurrentYear = DATEPART(YY, @CurrentDate) SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) /********************************************************************************************/ --Proceed only if Start Date(Current date ) is less than End date you specified above WHILE @CurrentDate < @EndDate BEGIN /*Begin day of week logic*/ /*Check for Change in Month of the Current date if Month changed then Change variable value*/ IF @CurrentMonth != DATEPART(MM, @CurrentDate) BEGIN UPDATE @DayOfWeek SET MonthCount = 0 SET @CurrentMonth = DATEPART(MM, @CurrentDate) END /* Check for Change in Quarter of the Current date if Quarter changed then change Variable value*/ IF @CurrentQuarter != DATEPART(QQ, @CurrentDate) BEGIN UPDATE @DayOfWeek SET QuarterCount = 0 SET @CurrentQuarter = DATEPART(QQ, @CurrentDate) END /* Check for Change in Year of the Current date if Year changed then change Variable value*/ IF @CurrentYear != DATEPART(YY, @CurrentDate) BEGIN UPDATE @DayOfWeek SET YearCount = 0 SET @CurrentYear = DATEPART(YY, @CurrentDate) END -- Set values in table data type created above from variables UPDATE @DayOfWeek SET MonthCount = MonthCount + 1, QuarterCount = QuarterCount + 1, YearCount = YearCount + 1 WHERE DOW = DATEPART(DW, @CurrentDate) SELECT @DayOfWeekInMonth = MonthCount, @DayOfQuarter = QuarterCount, @DayOfWeekInYear = YearCount FROM @DayOfWeek WHERE DOW = DATEPART(DW, @CurrentDate) /*End day of week logic*/ /* Populate Your Dimension Table with values*/ INSERT INTO [dbo].[DimDate] SELECT CONVERT (char(8),@CurrentDate,112) as DateKey, @CurrentDate AS Date, CONVERT (char(10),@CurrentDate,103) as FullDateUK, CONVERT (char(10),@CurrentDate,101) as FullDateUSA, DATEPART(DD, @CurrentDate) AS DayOfMonth, --Apply Suffix values like 1st, 2nd 3rd etc.. CASE WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th' WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st' WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd' WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd' ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th' END AS DaySuffix, DATENAME(DW, @CurrentDate) AS DayName, DATEPART(DW, @CurrentDate) AS DayOfWeekUSA, -- check for day of week as Per US and change it as per UK format CASE DATEPART(DW, @CurrentDate) WHEN 1 THEN 7 WHEN 2 THEN 1 WHEN 3 THEN 2 WHEN 4 THEN 3 WHEN 5 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 END AS DayOfWeekUK, @DayOfWeekInMonth AS DayOfWeekInMonth, @DayOfWeekInYear AS DayOfWeekInYear, @DayOfQuarter AS DayOfQuarter, DATEPART(DY, @CurrentDate) AS DayOfYear, DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS WeekOfMonth, (DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) / 7) + 1 AS WeekOfQuarter, DATEPART(WW, @CurrentDate) AS WeekOfYear, DATEPART(MM, @CurrentDate) AS Month, DATENAME(MM, @CurrentDate) AS MonthName, CASE WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1 WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2 WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3 END AS MonthOfQuarter, DATEPART(QQ, @CurrentDate) AS Quarter, CASE DATEPART(QQ, @CurrentDate) WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END AS QuarterName, DATEPART(YEAR, @CurrentDate) AS Year, 'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName, LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MonthYear, RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth, CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, (DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, @CurrentDate)))) AS LastDayOfMonth, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter, DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter, CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS FirstDayOfYear, CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, @CurrentDate))) AS LastDayOfYear, NULL AS IsHolidayUSA, CASE DATEPART(DW, @CurrentDate) WHEN 1 THEN 0 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 1 WHEN 5 THEN 1 WHEN 6 THEN 1 WHEN 7 THEN 0 END AS IsWeekday, NULL AS HolidayUSA, Null, Null SET @CurrentDate = DATEADD(DD, 1, @CurrentDate) END有人可以幫助我如何加入此表以及使用已經存在的報告會是什麼樣子嗎？同樣，在比較訂單日期和發貨日期之間的日期時，我需要它只包括工作日。
任何幫助將非常感激。
這個db<>fiddle展示了解決這個問題的兩種方法的模型（使用一些高度簡化的表）。
首先，您可以創建一個函式，通過對周末（在我的範例中為假期）進行條件檢查來計算日期之間的天數。該函式簡單地將訂單和發貨日期作為參數，計算這些日期之間的周末和節假日的數量，然後從兩個日期之間的總天數中減去這些日期。
CREATE FUNCTION fnCalculateLeadTime (@StartDate DATETIME, @EndDate DATETIME) RETURNS INT AS BEGIN DECLARE @Weekends INT = 0, @Holidays INT = 0, @LeadTime INT SELECT @Weekends = COUNT(*) FROM DimDate WHERE [Date] >= @StartDate AND [Date] <= @EndDate AND [DayOfWeek] IN (1, 7) SELECT @Holidays = COUNT(*) FROM DimDate WHERE [Date] >= @StartDate AND [Date] <= @EndDate AND [IsHoliday] = 1 SELECT @LeadTime = DATEDIFF(DAY, @StartDate, @EndDate)-@Weekends-@Holidays+1 RETURN(@LeadTime) END第二種解決方案使用 CROSS APPLY 將 DimDate 值應用於 BI 銷售數據，然後使用 COUNT 聚合計算日期 9 之間具有相同周末和假日條件的天數。
SELECT b.ID, OrderDate, ShipmentDate, COUNT(d.ID) AS LeadTime FROM BISales b CROSS APPLY DimDate d WHERE d.IsHoliday <> 1 AND d.DayOfWeek NOT IN (1, 7) AND d.[Date] BETWEEN b.OrderDate and b.ShipmentDate GROUP BY b.ID, OrderDate, ShipmentDate第二種解決方案可能會比使用 UDF 執行得更好，但是，因為它聚合了可能被證明更難以包含在現有報告查詢中的值。