问题描述
扩展:SQL 帮助 - 异常报告
在我之前的帖子中,我请求肝:
In my previous post I requested hep for:
我有:
- 一个公司表(CompanyID、CompanyName),
- 日期表(Datekey int, date, isTradingHoliday 0/1),
- 事实表(id、datekey、c??ompanyID、StockClosePrice)
我需要帮助编写查询以查找事实表中没有数据的日期和公司.
I need help to write a query to find for which days and for which companies I don't have data in the fact table.
下面的 SQL 完成工作
SQL below does the job
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)
我注意到有些公司的StockClosePrice 是 0.00 - 我想将它们包含在异常报告中.
I have noticed for some companies, for some days, StockClosePrice is 0.00 - I want to include those in the exception report.
感谢任何帮助
推荐答案
我想你只是想在子查询中多一个条件:
I think you just want one more condition in the subquery:
where d.isTradingHoliday = 0 and not exists (select 1 from facts f where f.datekey = d.datekey and f.companyID = c.companyID and f.StockClosePrice <> 0.00 )