SQL Query显示工单的顺序

首先对不好的主题线抱歉.

编辑:这里的查询重复OrderNumbers我需要查询不重复OrderNumbers编辑:缩短问题,并提供一个更清晰的问题

我有一张表,其中包含已执行的所有工单的记录.有两种类型的订单.安装和故障呼叫.我的查询是查找在安装后30天内发生的所有故障呼叫,并将该故障呼叫(TC)与正确的安装(IN)相匹配.因此,故障呼叫日期必须在安装后发生,但不会超过30天.此外,如果同一帐户有两个安装和两个故障呼叫都在30天内发生,并且它们按顺序发生,结果必须反映出来.我遇到的问题是我得到一个安装订单,匹配两个不同的故障呼叫(TC)和一个与两个不同的安装(IN)匹配的故障呼叫(TC)

在SQL Fiddle的示例中,请密切关注安装订单号1234567810和故障呼叫订单号1234567890,您将看到我遇到的问题. http://sqlfiddle.com/#!3/811df/8

select b.accountnumber,
       MAX(b.scheduleddate) as OriginalDate,
       b.workordernumber as OriginalOrder,
       b.jobtype as OriginalType,
       MIN(a.scheduleddate) as NewDate,
       a.workordernumber as NewOrder,
       a.jobtype as NewType 
from (
      select workordernumber,accountnumber,jobtype,scheduleddate 
      from workorders 
      where jobtype = 'TC'
     ) a join 
     (
       select workordernumber,accountnumber,jobtype,scheduleddate 
       from workorders  
       where jobtype = 'IN'
     ) b
on a.accountnumber = b.accountnumber 
group by b.accountnumber,
         b.scheduleddate,
         b.workordernumber,
         b.jobtype, 
         a.accountnumber,
         a.scheduleddate,
         a.workordernumber,
         a.jobtype
having MIN(a.scheduleddate) > MAX(b.scheduleddate) and 
      DATEDIFF(day,MAX(b.scheduleddate),MIN(a.scheduleddate)) < 31

我正在寻找结果的示例.感谢您在为我指路的过程中提供的任何帮助.