在特定日期范围内

Question

我有一个表，我有4列 串行（nvarchar的）查找重复的记录，SID（NVARCHAR），dateCreated会（日期），CID（独特和INT） 我想找到记录存在重复的地方系列和SID和其中2个副本系列落在日期范围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 

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 

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 

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 

PS：PreviousDate是MAX PreviousDate