SQL帮助-异常报告 [英] SQL help - exception report

问题描述

我有一个公司表(CompanyID，CompanyName)，日期表(Datekey int，date，isTradingHoliday 0/1)，事实表(id，datekey，companyID，StockClosePrice)

I have a company table (CompanyID, CompanyName), Date Table (Datekey int, date, isTradingHoliday 0/1), Fact table (id, datekey, companyID, StockClosePrice)

我需要一份例外情况报告.对于所有公司，所有交易日都将有一个StockClosePrice.我需要找到我没有数据的哪个日期和哪些公司.

I need an exception report. Expectation is for all the companies, for all trading days, there will be a StockClosePrice. I need to find for which date and for which companies on the trading days I don't have data.

所以基本上，我需要帮助写一个查询来查找事实表中我没有数据的2019年.

So basically, I need help to write a query to find for year 2019 for which days and for which companies I don't have data in the fact table.

推荐答案

如果我正确地遵循了您的规定，则可以交叉加入公司和日期，然后使用not exists过滤非交易性假期中的遗漏"事实:

If I followed you correctly, you can cross join companies and dates, and then filter on "missing" facts on non-trading holidays with not exists:

select c.*, d.*
from companies c
cross join dates d 
where 
    d.isTradingHoliday = 0
    and not exists (
        select 1 from facts f where f.datekey = d.datekey and f.companyID = c.companyID
    )

这篇关于SQL帮助-异常报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！