在特定日期范围内查找重复记录

Question

我有一个表格，其中有4列 序列号（nvarchar）、SID（nvarchar）、创建日期（日期）、CID（唯一且整数） 我想找到记录中重复的序列号和SID，并且这两个重复的序列号在180天的日期范围内。

请帮忙。

样例数据

Serial          SID     DateCreated             CID 
02302-25-0036   HONMD01 2017-05-01 00:00:00.000 1
02302-25-0036   HONMD01 2017-05-01 00:00:00.000 3
0264607         HONMD01 2017-05-01 00:00:00.000 65
0264607         HONMD01 2016-05-01 00:00:00.000 45
03118-09-0366   PRIVA00 2016-05-20 00:00:00.000 34
03118-09-0366   PRIVA00 2016-05-20 00:00:00.000 87
0969130         140439  2017-05-09 00:00:00.000 32
0969130         140439  2017-05-09 00:00:00.000 23
1049567         INIIL00 2017-04-12 00:00:00.000 76

- aman6496

---

请提供一些样本数据和期望的输出。 - Ven

你想知道当前行是否在最近180天内吗？ - Evgeny

添加了示例数据。 例如，对于序列号02302-25-0036，有两个具有相同SID的条目，且两者都在180天内，我们需要这些数据。如果两个日期相隔超过180天，则不需要该数据。 - aman6496

请将以下关于编程的内容从英语翻译成中文。仅返回翻译后的文字，请勿添加示例数据作为图像，这样复制粘贴将会很困难。 - Tim Schmelter

Answer 1

create table #Test (Serial nvarchar(20), [SID] nvarchar(10), DateCreated datetime, CID int)

Insert into #Test   values ('02302-25-0036', 'HONMD01', '2017-05-01 00:00:00.000', 1)
                    , ('02302-25-0036', 'HONMD01', '2017-05-01 00:00:00.000', 3)
                    , ('0264607', 'HONMD01', '2017-05-01 00:00:00.000', 65)
                    , ('0264607', 'HONMD01', '2016-05-01 00:00:00.000', 45)
                    , ('03118-09-0366', 'PRIVA00', '2016-05-20 00:00:00.000', 34)
                    , ('03118-09-0366', 'PRIVA00', '2016-05-20 00:00:00.000', 87)
                    , ('0969130', '140439', '2017-05-09 00:00:00.000', 32)
                    , ('0969130', '140439', '2017-05-09 00:00:00.000', 23)
                    , ('1049567', 'INIIL00', '2017-04-12 00:00:00.000', 76)

select distinct a.* 
from
(
select t.*
from #Test t
inner join (
    Select Serial, [SID]
    from #Test
    group by Serial, [SID]
    Having count(*)>=2
    ) d on d.Serial = t.Serial and t.SID= t.SID
) a
full outer join
(
select t.*
from #Test t
inner join (
    Select Serial, [SID]
    from #Test
    group by Serial, [SID]
    Having count(*)>=2
    ) d on d.Serial = t.Serial and t.SID= t.SID
) b on a.Serial = b.Serial and a.SID= b.SID
where datediff(d,a.DateCreated, b.DateCreated)<180

- cloudsafe

Answer 2

尝试做这个：

with cte as (
    select 
        serial,
        sid,
        dateCreated,
        cid,
        coalesce(max(dateCreated) over(partition by serial, sid order by cid, dateCreated asc rows between unbounded preceding and 1 preceding), '1900-01-01') as last,
        coalesce(min(dateCreated) over(partition by serial, sid order by cid, dateCreated asc rows between 1 following and unbounded following), '5999-01-01') as next
    from table_name
) 
select *
from cte 
where 
    datediff(day, last, dateCreated) >= 180
    and datediff(day, dateCreated, next) >= 180

- Evgeny

---

谢谢 Евгений Кондратенко - aman6496

Answer 3

这是一个具有挑战性的问题！我已经留下了最终输出，以便更容易地理解*(PreviousDate, rno)。以下是我的解决方法：

Create table #t(Serial nvarchar(100),SID nvarchar(100),DateCreated date,CID int)
Insert into #t values 
('02302-25-0036',   'HONMD01', '2017-05-01 00:00:00.000', 1),
('02302-25-0036',   'HONMD01', '2017-05-01 00:00:00.000', 3),
('0264607',         'HONMD01', '2017-05-01 00:00:00.000', 65),
('0264607',         'HONMD01', '2016-05-01 00:00:00.000', 45),
('03118-09-0366',   'PRIVA00', '2016-05-20 00:00:00.000', 34),
('03118-09-0366',   'PRIVA00', '2016-05-20 00:00:00.000', 87),
('0969130',         '140439',  '2017-05-09 00:00:00.000', 32),
('0969130',         '140439',  '2017-05-09 00:00:00.000', 23),
('1049567',         'INIIL00', '2017-04-12 00:00:00.000', 76)

Select iq2.*
FROM
 (Select iq.Serial, iq.SID, iq.DateCreated, iq.CID, iq.PreviousDate,
         ROW_NUMBER() OVER (PARTITION BY iq.Serial,iq.SID, CASE WHEN DATEDIFF(day, iq.DateCreated, iq.PreviousDate) <= 180 THEN 1 ELSE 0 END 
                            ORDER BY Serial,SID) rno
  FROM
      (select Serial,SID,DateCreated,CID,
              MAX(DateCreated) OVER (PARTITION BY Serial,SID ORDER BY Serial,SID) maxDate,
              DATEADD(day,-180,MAX(DateCreated) OVER (PARTITION BY Serial,SID ORDER BY Serial,SID)) PreviousDate
       from #t
    )iq
 )iq2 
where iq2.rno <> 1

输出：

Serial                    SID        DateCreated   CID         PreviousDate   rno
----------               -------     ----------   ----         -----------   ----
02302-25-0036            HONMD01     2017-05-01    3           2016-11-02     2
03118-09-0366            PRIVA00     2016-05-20    87          2015-11-22     2
0969130                  140439      2017-05-09    23          2016-11-10     2

注意：PreviousDate 是最大的 PreviousDate

- Prabhat G