SQL Server 从当前日期开始计算天数，不包括另一个表中的天数

Question

我正在尝试编写一个查询，该查询允许我采用下表：

| Username | Startdate | Points |
|----------|-----------|--------|
| XXY      | 15-03-18  |      0 |
| YYX      | 12-02-18  |      1 |
| ZZY      | 10-06-18  |      2 |

并计算从当前日期开始的天数，不包括表 2 中用户缺席的天数。

| Username | Startdate | Enddate  |
|----------|-----------|----------|
| XXY      | 20-03-18  | 25-03-18 |

还有另一个问题，我需要两个表在当前日期之前的每一天都有一行。我该怎么做？

更新：我已经在表 1 DATEDIFF(DAY， Startdate，GETDATE((( 中尝试过这个，这给了我天数。但我需要从中减去 5 天(见表 2(

Answer 1

select 
datediff(day, table1.StartDate, getdate()) - datediff(day, table2.StartDate, table2.Enddate)
from table1 
join table2 on table1.Username = table2.Username

Answer 2

一种方法是执行以下操作：

USE Sandbox;
GO
CREATE TABLE Dates (SkipDate date);
GO
INSERT INTO Dates
VALUES ('20180605'),('20180617');
GO
WITH VTE AS (
SELECT CONVERT(date,S) StartDate, CONVERT(date, E) EndDate
FROM (VALUES('20180601','20180606'),
('20180601','20180618')) V(S,E))
SELECT StartDate,
EndDate,
DATEDIFF(DAY, StartDate, EndDate) AS DaysBetween,
DATEDIFF(DAY, StartDate, EndDate) - SkipDates AS DaysMinusSkipped
FROM VTE
CROSS APPLY (SELECT COUNT(*) AS SkipDates
FROM Dates
WHERE SkipDate >= StartDate
AND SkipDate <= EndDate) AS SD;
GO

如果你想创建一些东西，这意味着你不需要每次都编写那个 SQL，你可以创建一个表值函数：

CREATE FUNCTION dbo.DateDiffSkip (@StartDate date, @EndDate date)
RETURNS TABLE
AS RETURN
SELECT DATEDIFF(DAY, @StartDate, @EndDate) - COUNT(SkipDate) AS DateDiffSkip
FROM Dates D
WHERE D.SkipDate >= @StartDate
AND D.SkipDate <=  @EndDate;
GO

然后，您的 SQL 将是：

WITH VTE AS (
SELECT CONVERT(date,S) StartDate, CONVERT(date, E) EndDate
FROM (VALUES('20180601','20180606'),
('20180601','20180618')) V(S,E))
SELECT StartDate,
EndDate,
DATEDIFF(DAY, StartDate, EndDate) AS DaysBetween,
DDS.DateDiffSkip AS DaysMinusSkipped
FROM VTE
CROSS APPLY dbo.DateDiffSkip(StartDate, EndDate) DDS;
GO
--Clean up
DROP FUNCTION DateDiffSkip;
DROP TABLE Dates;

Answer 3

这可以使用多个 DATEDIFF 函数进行计算。这还会计算每个用户的多次缺勤：

declare @t table ([UserName] char(3), [StartDate] Date, [Points] int)
insert into @t values ('XXY', '2018-03-15', 0), ('YYX', '2018-02-12', 1), ('ZZY', '2018-06-10', 2)
declare @a table ([UserName] char(3), [StartDate] Date, [EndDate] Date)
insert into @a values ('XXY', '2018-03-20', '2018-03-25')
select t.*, datediff(d, t.[StartDate], cast(getdate() as date)) - isnull(sum(datediff(d, a.[StartDate], a.[EndDate])), 0) as [DaysNotAbsent]
from @t t
left join @a a on t.UserName = a.UserName
group by t.UserName, t.StartDate, t.Points