如何获取公司未在Sql中记录的每个公司的日期

Question

我有一个包含以下列的表：

id, date, CompanyId, Value,..

我想为每个没有数据的公司选择日期

CompanyId | Date (company has no data)
----------+-----------------------------
x        | 2017-07-14
y        | 2017-07-05

Answer 1

使用日历表和使用开始日期例如-7

;WITH dates AS (
SELECT     distinct   Date
FROM          yourtable
WHERE        (Date > DATEADD(day, - 7, CONVERT(date, SYSDATETIME())))
),
companies AS (
SELECT        CompanyId
FROM           yourtable
WHERE        (Date = DATEADD(day, - 7, CONVERT(date, SYSDATETIME())))
)
SELECT distinct c.CompanyId, CAST(d.Date AS Date) Date
FROM yourtable c
CROSS JOIN dates d
LEFT JOIN yourtable t
ON c.CompanyId = t.CompanyId AND d.Date = t.Date
join CalenderTable a on d.Date=a.Date
WHERE    t.CompanyId IS NULL
ORDER BY    c.CompanyId, Date 

Answer 2

我们可以尝试使用日历表方法。下面标记为dates的第一个CTE在2017年生成所有日期。然后，我们将其交叉连接到另一个CTEcompanies，它会在您的表中找到所有独特的公司。通过将此交叉产品左键连接到您的表中，我们可以找到每个公司缺少数据的天数。

DECLARE @ENDDATE DATETIME
SET @START_DATE = '20170101'
SET @ENDDATE = '20171231'
;WITH dates AS (
SELECT @START_DATE Date
UNION ALL
SELECT Date + 1
FROM CTE_DATES
WHERE Date + 1 < @ENDDATE
),
companies AS (
SELECT DISTINCT CompanyId
FROM yourTable
)
SELECT c.CompanyId, CAST(d.Date AS Date) Date
FROM companies c
CROSS JOIN dates d
LEFT JOIN yourTable t
ON c.CompanyId = t.CompanyId AND d.Date = t.Date
WHERE
t.CompanyId IS NULL
ORDER BY
c.CompanyId, d.Date;

Answer 3

如果你只在表中查找日期，我会选择：

select c.companyId, d.date
from (select distinct companyId from t) c cross join
(select distinct date from t) d left join
t
on t.companyId = c.companyId and t.date = d.date
where t.companyId is null
order by c.companyId, d.date;

这个想法很简单。cross join创建表中公司和日期的所有组合。left join/where过滤掉现有的。注意缺少select distinct。这是不需要的，只会减慢查询速度。

如果你正在寻找一系列日期，无论它们是否在表中，那么日历表——就像蒂姆的答案一样——是正确的方法。