如何从一个日期范围得到的周末计数(How do I get count of weekend day

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

实施例＃1：

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

实施例＃2：

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

任何人都可以提出请。

Answer 1:

这里是

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

这里是你的结果

Answer 2:

今天有同样的问题。 而我来到这里。

如果你不想使用递归（CTE）或一段时间。 您可以使用数学加时情况：

DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE
SET @StartDate = Getdate()
SET @EndDate = GetDate() + 11

SELECT
    -- Full WE (*2 to get num of days Sa and So)             
    (((DATEDIFF(d,@StartDate,@EndDate)+1)/7)*2)
    +
    -- WE-Days in between; given that Saturday = 7 AND Sunday = 1
    -- what if startdate is sunday And you have remaining Days; you will always only get one WE-day
    CASE WHEN DATEPART(dw,@StartDate) = 1 AND (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 THEN 1
        -- If you have remaining days (Modulo 7 > 0) and the sum of number of starting day and remaining days is 8 (+1 for startingdate) then you have + 1 WE-day (its a saturday)  
        ELSE CASE WHEN (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 AND (((DATEDIFF(d,@StartDate,@EndDate)+1)%7) + DATEPART(dw,@StartDate)) = 8 THEN 1
            -- If the remaining days + the number of the weekday is are greater then 8 (+1 for startingdate) you have 2 days of the weekend in between.  
            ELSE CASE WHEN (DATEDIFF(d,@StartDate,@EndDate)+1)%7 > 0 AND (((DATEDIFF(d,@StartDate,@EndDate)+1)%7) + DATEPART(dw,@StartDate)) > 8 THEN 2
            -- you have no WE-days in between! Either because of the fact that you have a number that is divisable by 7 or because the remaining days are between 2 (Tuesday) and 6 (Friday)   
            ELSE 0
            END
        END
    END   AS TotalWEDays

我希望通过它的意见得到明显。 让我知道，如果它帮助。

Answer 3:

您可以使用日期部分http://msdn.microsoft.com/en-us/library/ms174420.aspx

一个例子;

WITH CTE as(
Select DATEPART(WeekDay,MyDate) as DP From Table Where Mydate > @StartDate and MyDate < @EndDate)
Select Count(*) as CT,DP From CTE
group by DP

周六将是7和周日将是1，所以你可以在下一个检查计数它们。

Answer 4:

尝试这个：

declare @startdate datetime = getdate()
declare @days int = 5
declare @cal table(dt datetime)

declare @counter int = 0
while @counter < @days
   begin
   insert into @cal values (@startdate + @counter) --Ideally should be dateadd(dd,@counter,@startdate)
   set @counter = @counter + 1
end

select count(*) from @cal
where datename(dw,dt) = 'Saturday' or datename(dw,dt) = 'Sunday'
--Ideally should be 
--where datename(dw,dt) = 1 or datename(dw,dt) = 7

演示

我们正在做的是建立天的名单从开始到结束的日期，然后计数从这些日期的周末。 表变量用于存储该列表。

2点是应该注意的：

1. 理论上，应该使用dateadd函数来执行日期时间计算，而不是+运算符。
2. 虽然我已经使用datename为清楚起见， datepart ，因为它给数值，而会更好datename给依赖于语言的值。

Answer 5:

尝试这个：

DECLARE @V_StartDate DATETIME = GETDATE(), @V_EndDate DATETIME = GETDATE() + 5;

WITH showDateCTE(DateCol)
AS
(
SELECT DateCol = @V_StartDate
UNION ALL
SELECT DATEADD(DAY, 1, DateCol) 
FROM showDateCTE 
WHERE DateCol < @V_EndDate - 1
)
SELECT COUNT(1) weekEndCount  
FROM showDateCTE  
WHERE DATENAME(dw, CONVERT(DATE, DateCol)) IN ('Saturday', 'Sunday');

Answer 6:

    declare @startdate datetime 
    declare @enddate datetime
    declare @weekendCnt int
    set @startdate = getdate()
    set @enddate = getdate()+8
    set @weekendCnt = 0

    while @startdate < @enddate
    begin
    PRINT @startdate
     if(datename(dw, @startdate) in( 'Saturday','Sunday'))
         begin
             set @weekendCnt = @weekendCnt + 1         
         end
         set @startdate = @startdate +1
     end
    print @weekendCnt

文章来源: How do I get count of weekend days from a range of dates