使用外部应用程序根据详细信息表中的第一个结果为每一行查找一条记录

Question

我有两个表；问题的PROBLEM（作为主问题）与问题组和PROBLEM_LOG（作为细节问题）

问题

PROBLEM_ID      PROBLEM          PGROUP
1               Line Down        Network
2               Node Down        Network
3               hardware error   Hardware
4               PM               Hardware
5               disk error       Hardware

问题日志

TERM        PROBLEM           DOWN_DATE
S1DIP0314   Service           15-03-26
S1DIP0314   PM                15-01-01
S1DIP0314   Service           15-01-02
S1DIP0314   disk error        15-01-06
S1DIP0314   Hardware error    15-01-28

起初，我发现TERM具有PROBLEM = 'PM'（在这种情况下，我将使用dateadd+7天）

SELECT  TERM,PROBLEM,DOWN_DATE
  FROM [PROBLEM_LOG]
  WHERE PROBLEM = 'PM'

但我的问题是，当我从有PM + 7天的日期找到一个problem = 'PM'时，是否有其他PGROUP = 'Hardware'在我的例子中，7天内有1个以上的问题出现'Disk Error'
结果应该是这个

TERM        PROBLEM       DOWN_DATE
S1DIP0314   PM            15-01-01
S1DIP0314   disk error    15-01-06

当我发现更多问题时，在我发现的最后一个问题后的30天内发现更多问题

TERM        PROBLEM           DOWN_DATE
S1DIP0314   PM                15-01-01
S1DIP0314   disk error        15-01-06
S1DIP0314   Hardware error    15-01-28

我必须使用外部应用程序来实现这个条件

我试过这个

SELECT pt.term,
       pts.problem,
       pts.down_date
  FROM PROBLEM_LOG pt
       LEFT OUTER JOIN ATM ON pt.TERM = atm.term
       OUTER APPLY( 
                    SELECT px.term,
                           px.problem,
                           px.down_date
                      FROM problem_log px
                           LEFT JOIN problem pf ON pf.PROBLEM = px.problem
                      WHERE px.PROBLEM = pt.problem
                        AND PGROUP = 'Hardware' ) pts
  WHERE pt.PROBLEM = 'PM'
  AND ('20' + pt.DOWN_DATE + ' ' + down_time) <= DATEADD( day,7,('20' + pt.DOWN_DATE + ' ' + pt.DOWN_TIME))) pts;

Answer 1

我认为您需要同一组的所有问题日志，这样您就可以使用这样的简单查询：

Declare @Term varchar(10) = 'S1DIP0314'
Declare @Problem varchar(10) = 'PM'
select pt.TERM, pt.PROBLEM, pt.DOWN_DATE 
from problem_log pt
where pt.TERM = @Term 
    AND pt.PROBLEM IN (SELECT p.PROBLEM FROM problem p 
                       WHERE p.PGROUP = (SELECT p1.PGROUP FROM problem p1 
                                          WHERE p1.PROBLEM = @Problem))