SQL查询查找最后一个工作日返回13个月 [英] SQL query to find last business day back 13 month
本文介绍了SQL查询查找最后一个工作日返回13个月的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要一个sql查询，返回输出，如下表所示，对于给定日期的字段&lastbsp; LastBusDayBack13Mth_Dt字段。
i need a sql query which return output as like below table for field LastBusDayBack13Mth_Dt for a given Date field.
谢谢！
推荐答案
嗨RavSup，
Hi RavSup,
请参阅：
IF OBJECT_ID('TempDb..#CalendarTable') IS NOT NULL
DROP TABLE #CalendarTable
CREATE TABLE #CalendarTable
(
[Date] DATE,
BusinessDay VARCHAR(64),
[DayOfWeek] VARCHAR(128)
)
;WITH CTE AS
(
SELECT CAST('2017-12-1' AS DATE) AS V_DATE
UNION ALL
SELECT DATEADD(DAY,1, V_DATE) FROM CTE WHERE V_DATE<'2019-02-05'
)
INSERT INTO #CalendarTable
SELECT V_DATE,CASE WHEN LEFT(DATENAME(WEEKDAY, V_DATE),3) IN ('Sat','Sun') THEN 'Not Business Day' ELSE 'Business Day'END, LEFT(DATENAME(WEEKDAY, V_DATE),3) FROM CTE
option (maxrecursion 0)
UPDATE #CalendarTable SET BusinessDay='Not Business Day' WHERE MONTH([Date])=1 AND DAY([Date])=1
--Query
SELECT
T.[Date],
T.BusinessDay,
T.[DayOfWeek],
T1.MAX_DATE AS LastBusDayBack13Mth_Dt
FROM #CalendarTable T
CROSS APPLY (SELECT MAX([Date]) AS MAX_DATE FROM #CalendarTable WHERE BusinessDay='Business Day' AND [Date]<=EOMONTH(T.[Date],-13)) T1
WHERE T.[Date]>='2019-01-01'
--Output
/*
Date BusinessDay DayOfWeek LastBusDayBack13Mth_Dt
---------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------
2019-01-01 Not Business Day Tue 2017-12-29
2019-01-02 Business Day Wed 2017-12-29
2019-01-03 Business Day Thu 2017-12-29
2019-01-04 Business Day Fri 2017-12-29
2019-01-05 Not Business Day Sat 2017-12-29
2019-01-06 Not Business Day Sun 2017-12-29
2019-01-07 Business Day Mon 2017-12-29
2019-01-08 Business Day Tue 2017-12-29
2019-01-09 Business Day Wed 2017-12-29
2019-01-10 Business Day Thu 2017-12-29
2019-01-11 Business Day Fri 2017-12-29
2019-01-12 Not Business Day Sat 2017-12-29
2019-01-13 Not Business Day Sun 2017-12-29
2019-01-14 Business Day Mon 2017-12-29
2019-01-15 Business Day Tue 2017-12-29
2019-01-16 Business Day Wed 2017-12-29
2019-01-17 Business Day Thu 2017-12-29
2019-01-18 Business Day Fri 2017-12-29
2019-01-19 Not Business Day Sat 2017-12-29
2019-01-20 Not Business Day Sun 2017-12-29
2019-01-21 Business Day Mon 2017-12-29
2019-01-22 Business Day Tue 2017-12-29
2019-01-23 Business Day Wed 2017-12-29
2019-01-24 Business Day Thu 2017-12-29
2019-01-25 Business Day Fri 2017-12-29
2019-01-26 Not Business Day Sat 2017-12-29
2019-01-27 Not Business Day Sun 2017-12-29
2019-01-28 Business Day Mon 2017-12-29
2019-01-29 Business Day Tue 2017-12-29
2019-01-30 Business Day Wed 2017-12-29
2019-01-31 Business Day Thu 2017-12-29
2019-02-01 Business Day Fri 2018-01-31
2019-02-02 Not Business Day Sat 2018-01-31
2019-02-03 Not Business Day Sun 2018-01-31
2019-02-04 Business Day Mon 2018-01-31
2019-02-05 Business Day Tue 2018-01-31
*/最诚挚的问候，
将
这篇关于SQL查询查找最后一个工作日返回13个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
