SQL 帮助:异常报告 - 扩展

问题描述

扩展: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
                 )