SQL查询以获取每两周的数据

Question

输入图像描述此处我想知道开始双周的日期和结束日期。意味着我想从下表结构中获取每两周的数据。

create table #temp(
    EmployeeID int,
    TEDate datetime,
    EmpFNamenvarchar(100),
    EmpLName nvarchar(100)
)
go
insert into #temp (EmployeeID ,EmpFName,EmpLName,TEDate) 
Select 2019,'roz','Ahmad','2019-04-23' 
union all 
Select 2019,'roz','Ahmad','2019-04-17' 
union all 
Select 2019,'roz','Ahmad','2019-04-29' 
select * from #temp

如何做？我已经使用了以下方法，但最终仍然得到错误的结果。

Select *,
       DATEADD(WEEK, DATEPART(wk, TEDate), 
       DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4 - DATEPART(DW, DATEADD(WEEK, DATEPART(wk, TEDate), 
       DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4) + 1 AS [BiWEEK_START],
       DATEADD(WEEK, DATEPART(wk, TEDate), 
       DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4 - DATEPART(DW, DATEADD(WEEK, DATEPART(wk, TEDate), 
       DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4) + 14 AS [BiWEEK_END] 
from #temp

所需的结果：

4/17/2019
4/14/2019
4/27/2019
4/22/2019
4/14/2019
4/27/2019
4/23/2019
4/14/2019
4/27/2019
4/29/2019
4/28/2019
5/11/2019
5/3/2019
4/28/2019
5/11/2019
5/6/2019
4/28/2019
5/11/2019
5/8/2019
4/28/2019
5/11/2019
5/13/2019
5/12/2019
5/25/2019

Answer 1

好的，这是两个解决方案。问题是，您必须能够通过查看Tedate来告诉Tedate属于哪个BI周，否则您必须定义BI Weeks的列表或告诉您的查询从哪里开始。对于第二个查询，您应该可以在任何BI星期开始日期，并且应该可以工作。

DECLARE @bi_weeks TABLE
(
StartDate DATE,
EndDate DATE
);
INSERT INTO @bi_weeks VALUES
('20190317','20190330'),
('20190331','20190413'),
('20190414','20190427'),
('20190428','20190511'),
('20190512','20190525');

DECLARE @emp_data TABLE
(
    EmployeeID INT,
    TEDate DATETIME,
    EmpFName NVARCHAR(100),
    EmpLName NVARCHAR(100)
);

INSERT INTO @emp_data (EmployeeID ,EmpFName,EmpLName,TEDate) VALUES
(2019,'roz','Ahmad','2019-03-20'),
(2019,'roz','Ahmad','2019-04-01'),
(2019,'roz','Ahmad','2019-04-13'),
(2019,'roz','Ahmad','2019-04-23'),
(2019,'roz','Ahmad','2019-04-17'),
(2019,'roz','Ahmad','2019-04-29');
--This is the first way where we join to a list of known bi week start and end dates
SELECT ed.*, bw.*
FROM @emp_data ed
LEFT JOIN @bi_weeks bw ON ed.TEDate BETWEEN bw.StartDate AND bw.EndDate
ORDER BY 2

--This is the second way where we tell our query a known start date and then use it to
--calculate the rest of the start and end dates.
DECLARE @initial_bi_week_start_date DATE = '20190414';
With empdataCTE AS
(
SELECT *,
[initial_bi_week_start_date] = @initial_bi_week_start_date ,
[bi_week_start] = 
DATEADD(DAY,
            (DATEDIFF(DAY, @initial_bi_week_start_date, TEDate) / 14 +
                    IIF(TEDate < @initial_bi_week_start_date, 1, 0) * -1) * 14,
            @initial_bi_week_start_date)
FROM @emp_data ed
)
SELECT *, DATEADD(DAY, 13, [bi_week_start]) [bi_week_end] 
FROM empdataCTE;