如何执行自联接以将表中的记录划分为事务组

Question

我有两个看起来像这样的表。 如何匹配/查找从 #tblregister 到 #tblheader 的统一数字，就像我给出的样本一样。 每个PID编号都应该有IN-BD（无清洁）和IP-BM（干净）。这意味着poolnum IN-BD将是第一个进行交易的，然后是IP-BM。

恢复的num可能会再次返回并创建一个具有相同统一编号的新pid#，然后他们将处理新的详细信息。谢谢。

Create table #tblRegister
(   esnnum nvarchar(50),
    recoverednum nvarchar(50), 
    receiptdate datetime, 
    pid nvarchar(10), 
    createddatetime datetime,
    unifiednumber nvarchar(50)
)
insert into #tblRegister (esnnum,recoverednum,receiptdate,pid, createddatetime, unifiednumber) values
('T008311518089249843905318439','B89249843905318439','2016-10-08 12:39:00.000','T008311518','2016-10-13 04:27:31.000','T49843905318439M'),
('T008311518089249843905318439','B89249843905318439','2016-11-05 11:11:00.000','T008524845','2016-11-16 08:50:51.000','T49843905318439M')

Create table #tblHeader
(   unifiednumber nvarchar(50),
    pid nvarchar(10), 
    poolnum nvarchar(10),
    startdate datetime,
    enddate datetime,
    linenum nvarchar(20)
)
insert into #tblHeader ( unifiednumber, pid, poolnum, startdate, enddate,linenum ) values
('T49843905318439M','P07037433','IN-BD','2016-10-13 16:19:46.000','2016-10-14 01:23:42.000','tb_tb-BD1'),
('T49843905318439M','P07370491','IP-BM','2016-11-07 11:14:30.000','2016-11-08 05:15:30.000','tb_BM3'),
('T49843905318439M','P07418769','IN-BD','2016-11-09 16:07:07.000','2016-11-13 04:10:30.000','tb_tb-BD1'),
('T49843905318439M','P07590987','IN-BD','2016-11-18 03:22:43.000','2016-11-18 22:41:50.000','tb_tb-BD1')

期望的结果：第一行已经完成了匹配的poolnum"IN-BD"和"IP-BM"。 第二行尚未完成。 它已与事务池 IN-BD 进行了"不清理"交易。

esnnum-------------------------recoverednum--------receiptdate-------------createddatetime-----------unifiednumber-----poolnum-A--pid-A--------linenum-A--------------startdate-----------enddate--------------poolnum-B--linenum-B---pid-B----
================================================================================================================================================================================================================================================
T008311518089249843905318439---B89249843905318439--2016-10-08 12:39:00.000--2016-10-13 04:27:31.000--T49843905318439M--IN-BD------P07037433---tb_tb-BD1---2016-11-9 16:19:46.000--2016-11-13 01:23:42.000-----'IP-BM'-----tb_BM3-----'P07370491'
T008311518089249843905318439---B89249843905318439--2016-11-05 11:11:00.000--2016-11-16 08:50:51.000--T49843905318439M--IN-BD------P07590987---tb_tb-BD1---2016-11-18 03:22:43.000--2016-11-18 22:41:50.000--------------------------------------

Answer 1

刚刚制作了脚本，根据此脚本的结果，第二行是正确的，但第一行的开始日期和结束日期应该是这个 '2016-11-09 16：07：07.000'，'2016-11-13 04：10：30.000' 不是捕获的那个。

我必须根据poolnum进行细分，并首先获得poolnum='IN-BD'。

;with cte as
(
 select unifiednumber, pid, poolnum, startdate, enddate, linenum,
  row_number() over(partition by unifiednumber order by startdate) row_num
 from #tblHeader 
 where poolnum='IN-BD'
)--, cte2 as
 select 
            rs1.unifiednumber, rs1.pid, rs1.poolnum, rs1.startdate, rs1.enddate, rs1.linenum,
            isnull(rs3.startdate,dateadd(year,-2,rs1.startdate)) previous_audit_date,
         isnull(rs2.startdate,dateadd(year,2,rs1.startdate)) next_audit_date,
                  rs1.row_num,
         rs2.row_num as rownum2,
         rs3.row_num as rownum3
into #data
from     cte rs1
         left join cte rs2 
            on rs1.unifiednumber = rs2.unifiednumber
            and rs1.row_num + 1 = rs2.row_num
         left join cte rs3
            on rs1.unifiednumber = rs3.unifiednumber
            and rs1.row_num - 1 = rs3.row_num

select tcps.esnnum, tcps.recoverednum, tcps.receiptdate, tcps.createddatetime, tcps.unifiednumber,
        pno.poolnum, pno.pid, pno.linenum, pno.startdate, pno.enddate
from #tblRegister tcps
         left join #data pno
                    on tcps.unifiednumber = pno.unifiednumber collate chinese_taiwan_stroke_ci_as
                    and tcps.createddatetime between pno.previous_audit_date and pno.startdate

Answer 2

请参阅以下示例数据作为参考。 希望现在清楚了。 谢谢。

register data:
itemcode      |ponum        |id2  |item |wrkctr |serialid |em_serial |date
---------------------------------------------------------------------------------------------
A5912947270138|P008279816   |S7843|A13  |FEntry |12345678F|aaaaaaaaaa|2016-10-07 06:20:54.000
A5912947270138|P008508629   |S8479|A13  |FEntry |12345678F|bbbbbbbbbb|2016-11-14 06:32:58.000
A5936181820133|P008281945   |S7809|A24  |FEntry |56789045D|ffffffffff|2016-10-07 14:48:13.000
A5936181820133|P008510773   |S8482|A24  |FEntry |56789045D|gggggggggg|2016-11-14 09:36:37.000

details data:
|line   |pool|  ponum   |itemid |location   |date                   |serialid
-----------------------------------------------------------------------------------------
L01     |PC  |P06965620 |pc13-t |Entry      |2016-10-08 08:34:53.000|12345678F  
L01     |PC  |P06965620 |pc13-t |Reject     |2016-10-08 08:44:45.000|12345678F  
L01     |PC  |P07628121 |pc24-t |Entry      |2016-11-19 17:03:27.000|56789045D  
L01     |PC  |P07628121 |pc24-T |Reject     |2016-11-19 17:37:18.000|56789045D  
L01     |PC  |P07891150 |pc24-t |Entry      |2016-12-06 17:07:40.000|56789045D  
L01     |PC  |P07891150 |pc24-t |Reject     |2016-12-06 17:17:18.000|56789045D

Desired Result:
|wrkctr |serialid |em_serial |date                   |date2                  |location1|date3                  |location2|itemid
---------------------------------------------------------------------------------------------------------------------------------
|FEntry |12345678F|aaaaaaaaaa|2016-10-07 06:20:54.000|2016-10-08 08:34:53.000|Entry    |2016-10-08 08:44:45.000|Reject   |pc13-t
|FEntry |12345678F|bbbbbbbbbb|2016-11-14 06:32:58.000|                       |         |                       |
|FEntry |56789045D|ffffffffff|2016-10-07 14:48:13.000|2016-11-19 17:03:27.000|Entry    |2016-11-19 17:37:18.000|Reject   |pc24-t    
|FEntry |56789045D|gggggggggg|2016-11-14 09:36:37.000|2016-12-06 17:17:18.000|Entry    |2016-12-06 17:17:18.000|Reject   |pc24-t