使用基于集合的方法获取给定日期范围之间的特定日期

In-order to get all the Sunday dates and Saturday dates between given date ranges I have used a iterative solution as below,

为了获得给定日期范围之间的所有星期日日期和星期六日期,我使用了如下的迭代解决方案,

create  TABLE #Dayweeks (id int IDENTITY(1,1),StartWeek DATETIME, EndWeek DATETIME)

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'



WHILE (@wkstartdate <= @wkenddate)
    BEGIN
         INSERT INTO #Dayweeks
         (
         StartWeek, EndWeek
         )
         SELECT
         @wkstartdate, DATEADD(wk,DATEDIFF(wk,0,@wkstartdate),6)-1

         SELECT @wkstartdate = DATEADD(dd,7,@wkstartdate)
    END

I wonder how to achieve this using set based approach. Is there any way to get the above result by using set based approach?

我想知道如何使用基于集合的方法实现这一目标。有没有办法通过使用基于集合的方法获得上述结果?

The output I got using iterative solution is given below.

我使用迭代解得到的输出如下。

3 个解决方案

#1

Check this with CTE :

用CTE检查一下:

Declare @DateFrom DateTime ='2011-07-01',
            @DateTo DateTime = '2011-07-31'
;WITH CTE(dt)
AS
(
      Select @DateFrom
      Union All
      Select DATEADD(d,1,dt)FROM CTE
      Where dt<@DateTo
)
Select 
     DATENAME(dw,dt) day, dt 
from CTE
where DATENAME(dw,dt)In('Sunday' , 'Saturday')

--To understand more, comment above select and run this.
select * from 
(
    select 'Sunday' day,dt from CTE
    where DATENAME(dw,dt)In('Sunday' )
    union
    select 'Saturday',dt from CTE
    where DATENAME(dw,dt)In('Saturday' )

) a  order by dt

Check this link to understand both approach.

检查此链接以了解这两种方法。

#2

There really is no "set-based" approach when you are starting with an empty set. You can replace your code with a recursive CTE. You can get the start dates by doing:

当你开始使用空集时,确实没有“基于集合”的方法。您可以使用递归CTE替换代码。您可以通过以下方式获取开始日期:

with weeks as (
      select @wkstartdate as dte
      union all
      select dateadd(weeks, 1, dte)
      from dte
      where dte < @wkenddate
)
insert into #Dayweeks(Startweek, EndWeek)
    select dte, dateadd(day, 6, dte)
    from weeks
option (maxrecursion 0);

Note that this does not verify the day of the week requirements. It just counts weeks from the first day.

请注意,这不会验证星期几的要求。从第一天算起几周。

#3

This should solve it using a tally table:

这应该使用计数表解决它:

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
, alldays as
(
  SELECT
    top (datediff(d, @wkstartdate, @wkenddate)) 
      cast(dateadd(d, N-1, @wkstartdate) as date) day
  FROM tally
)
SELECT day
FROM alldays
WHERE datediff(d, 0, day) % 7 in(5,6)

EDIT Improved version:

编辑改进版:

DECLARE @wkstartdate datetime = '2015-12-06',
        @wkenddate   datetime = '2016-04-05'

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT
  CAST(DATEADD(d, 0, N) as DATE) WEEKEND
FROM tally
WHERE 
  N between datediff(d, 0, @wkstartdate) and datediff(d, 0, @wkenddate)
  AND N % 7 in(5,6)

Result:

day
2015-12-06
2015-12-12
2015-12-13
...
...
2016-04-03

#1