如何使用SQL查询选择日期和每两个星期的薪资期间

Question

我如何选择6/20/2014和每个支付日期(每两周)之前这个日期到2010年初？我也希望能够选择6/20/2014和每个支付日期(每两周)之后，这个日期到2020年年底。

谢谢!

Answer 1

创建一个数字表

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO NumbersTest
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)

然后

declare @startDate date = '6/20/2014'

--Dates from 1/1/2010 to 6/20/2014
select DATEADD(wk, -2 * (n.Number - 1), @startDate)
from NumbersTest n
where DATEADD(wk, -2 * (n.Number - 1), @startDate) >= '1/1/2010'

--Dates from 6/20/2014 to 12/31/2020
select DATEADD(wk, 2 * (n.Number - 1), @startDate)
from NumbersTest n
where DATEADD(wk, 2 * (n.Number - 1), @startDate) <= '12/31/2020'

SQL Fiddle

Answer 2

下面的SQL可以给出您问题的第一部分的答案。可以使用相同的主体来获得第二部分的答案。

declare @startDate as date = '20140620';

with myCte1 as
(
    select @startDate as dt
    union all
    select DATEADD(week, -2, dt)
    from myCte1
    where dt > '20100112'
)

select m.dt
from myCte1 as m
option (maxrecursion 1000)