如何从一系列日期算起周末天数 [英] How do I get count of weekend days from a range of dates

问题描述

我必须在开始日期和日期之间找出星期六和星期日的总数。结束日期

示例＃1：

  StartDate = Getdate ），EndDate = GetDate（）+ 5  - 结果应为2. 
  

示例＃2： / p>

  StartDate = Getdate（），EndDate = GetDate（）+ 10  - 结果应为4. 
  

任何人都可以建议。

解决方案

p>这里是

  DECLARE @STARTDATE DATE = '01 / JAN / 2014'
 DECLARE @ENDDATE DATE = '01 / MAR / 2014'
 
; WITH CTE as 
（
 SELECT CAST（@STARTDATE AS DATE）as as DAYS）
 UNION ALL 
 SELECT DATEADD（DAY，1，[DAYS]）[DAYS] 
 FROM CTE 
 WHERE [DAYS]< CAST（@ENDDATE AS DATE）
）
 SELECT DISTINCT COUNT [DAYS]）OVER（DATENAME（WEEKDAY，[DAYS]））CNT，
 DATENAME（WEEKDAY，[DAYS]）WD 
 FROM CTE 
 WHERE DATENAME（WEEKDAY，[DAYS] ）='SATURDAY'或DATENAME（WEEKDAY，[DAYS]）='SUNDAY'
 ORD ER BY DATENAME（WEEKDAY，[DAYS]）
  

• SQL FIDDLE

这是您的结果

I have to find out total number of saturday and sunday between Start Date & End Date.

Example #1:

StartDate = Getdate(), EndDate = GetDate() + 5      -- result should be 2.

Example #2:

StartDate = Getdate(), EndDate = GetDate() + 10     -- result should be 4.

Can anyone suggest please.

解决方案

Here it is

DECLARE @STARTDATE DATE='01/JAN/2014'    
DECLARE @ENDDATE DATE='01/MAR/2014'

;WITH  CTE as
(
    SELECT  CAST(@STARTDATE AS DATE) as [DAYS] 
    UNION ALL
    SELECT DATEADD(DAY,1,[DAYS]) [DAYS]
    FROM    CTE
    WHERE   [DAYS] < CAST(@ENDDATE AS DATE)
)
SELECT DISTINCT COUNT([DAYS]) OVER(PARTITION BY DATENAME(WEEKDAY,[DAYS])) CNT,
DATENAME(WEEKDAY,[DAYS]) WD
FROM CTE 
WHERE DATENAME(WEEKDAY,[DAYS]) = 'SATURDAY' OR DATENAME(WEEKDAY,[DAYS]) = 'SUNDAY'
ORDER BY DATENAME(WEEKDAY,[DAYS]) 

• SQL FIDDLE

Here is your result

这篇关于如何从一系列日期算起周末天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！