Question

我继承了我为报告制度而努力的一个文件库。该系统以采购订单为中心,其创建、转让、确认和确认。这些国家是一个进步的国家。我所审查的内容如下:

OR (CreateJob.endtime is NULL and DATEDIFF(hour, buy_date, getdate()) > Vendor.expected_create_hours)
OR (TransmitJob.endtime is NULL and DATEDIFF(hour, CreateJob.endtime, getdate()) > Vendor.expected_transmit_hours)
OR (AcknowledgeJob.endtime is NULL and DATEDIFF(hour, TransmitJob.endtime, getdate()) > Vendor.expected_acknowledge_hours)
OR (ConfirmJob.endtime is NULL and DATEDIFF(hour, AcknowledgeJob.endtime, getdate()) > Vendor.expected_confirm_hours)

我发现的是,由于工作失败,不可能有一个“创造机会”。然而,工作可以再执行一次采购令,因此,有时某个特定业务主管实际成立,但由于兄弟后来失败,工作没有结束。这就造成了一种情况,即各州在竞选公职职位方面仍然取得进展,但是由于“创造机会”是全国人民党,它仍然显示这一问题报告。

因此,首先,上述制度和结构有一些明显的弊端,但这是一个与我目前处理的问题不同的问题。

看来,我可以通过基本上把逐步检查作为报告和陈述加以补充来确定报告。因此,对“联合企业”的首次检查也:

(     (CreateJob.endtime is NULL and DATEDIFF(hour, buy_date, getdate()) > Vendor.expected_create_hours) 
  AND (TransmitJob.endtime is NULL and DATEDIFF(hour, CreateJob.endtime, getdate()) > Vendor.expected_transmit_hours) 
  AND (AcknowledgeJob.endtime is NULL and DATEDIFF(hour, TransmitJob.endtime, getdate()) > Vendor.expected_acknowledge_hours) 
  AND (ConfirmJob.endtime is NULL and DATEDIFF(hour, AcknowledgeJob.endtime, getdate()) > Vendor.expected_confirm_hours))

令人ug笑皆是,开始把物品混入肥料之中。我能否以某种方式在以下表格中做到等同? 基本是某种宏观或临时制度?

CreateFailed = (CreateJob.endtime is NULL and DATEDIFF(hour, buy_date, getdate()) > Vendor.expected_create_hours)
TransmitFailed = (TransmitJob.endtime is NULL and DATEDIFF(hour, CreateJob.endtime, getdate()) > Vendor.expected_transmit_hours)
AcknowledgeFailed = (AcknowledgeJob.endtime is NULL and DATEDIFF(hour, TransmitJob.endtime, getdate()) > Vendor.expected_acknowledge_hours)
ConfirmFailed = (ConfirmJob.endtime is NULL and DATEDIFF(hour, AcknowledgeJob.endtime, getdate()) > Vendor.expected_confirm_hours)


OR (CreateFailed AND TransmitFailed AND AcknowledgeFailed AND ConfirmFailed)
OR (TransmitFailed AND AcknowledgeFailed AND ConfirmFailed)
OR (AcknowledgeFailed AND ConfirmFailed)
OR (ConfirmFailed)

如果在我试图做些什么时有一个共同的术语或名称,也许有人会把它添加到标题中?

Answer 1

[is] there is a common term or name for what I am trying to do...?

摘要

页: 1 创建Failed = ......, TransmitFailed = ......, 等逻辑化成自己的VIEW或CTE, 那么,你的主要询问只能看这些表格中是否有工作,例如(pseudokou):

SELECT Jobs.job_ID, ...
  FROM Jobs
 WHERE EXISTS (
               SELECT * 
                 FROM CreatedFailedJobs AS T1
                WHERE Jobs.job_ID = T1.job_ID
               )
       OR EXISTS (
                  SELECT * 
                    FROM TransmitFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  )
       OR EXISTS (
                  SELECT * 
                    FROM AcknowledgeFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  )
       OR EXISTS (
                  SELECT * 
                    FROM ConfirmFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  );

在这里,CTE的想法得到了扩大(pseudokou):

WITH CreateFailedJobs (job_ID, ...)
AS
(
 SELECT Jobs.job_ID, ...
   FROM ...
  WHERE CreateJob.endtime is NULL 
        AND DATEDIFF(hour, .SomeTable.buy_date, getdate()) 
               > Vendor.expected_create_hours
), 
TransmitFailedJobs (job_ID, ...)
AS
(
 SELECT Jobs.job_ID, ...
   FROM ...
  WHERE TransmitJob.endtime is NULL 
        AND DATEDIFF(hour, CreateJob.endtime, getdate()) 
               > Vendor.expected_transmit_hours
), 
AcknowledgeFailedJobs (job_ID, ...)
AS
(
 SELECT Jobs.job_ID, ...
   FROM ...
  WHERE AcknowledgeJob.endtime is NULL 
        AND DATEDIFF(hour, TransmitJob.endtime, getdate()) 
               > Vendor.expected_acknowledge_hours
), 
ConfirmFailedJobs (job_ID, ...)
AS
(
 SELECT Jobs.job_ID, ...
   FROM ...
  WHERE ConfirmJob.endtime is NULL 
        AND DATEDIFF(hour, AcknowledgeJob.endtime, getdate()) 
               > Vendor.expected_confirm_hours)
)
SELECT Jobs.job_ID, ...
  FROM Jobs
 WHERE EXISTS (
               SELECT * 
                 FROM CreatedFailedJobs AS T1
                WHERE Jobs.job_ID = T1.job_ID
               )
       OR EXISTS (
                  SELECT * 
                    FROM TransmitFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  )
       OR EXISTS (
                  SELECT * 
                    FROM AcknowledgeFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  )
       OR EXISTS (
                  SELECT * 
                    FROM ConfirmFailedJobs AS T1
                   WHERE Jobs.job_ID = T1.job_ID
                  );

我不敢肯定,这对业绩是好的,但可读性是本报告可以脱节的目标。

Answer 2

另一点——该法典是否摘自你的生产法典? 如果是的话,你可以仅用<代码>替换整块。 ConfirmFailed conditions,因为它是所有的共同点。 WHERE 的条件如下:

虽然它直接回答了你提出的问题,但简化你的代码的方法是,在预处理阶段(视你所处的环境而定)计算每条<代码>未填/代码”的数值一次——这可能是一个临时表格或观点。这样,所有逻辑都处于一个位置——如果在质询中需要不止一次的价值的话,这一点就特别有用——例如,在<条码>、SlectT<条/代码>和<条码>中。

友情链接