Retrieve rows only if the difference in two rows is less than 2

  • Hello All,

    I have a table that has data in below format:

    UserID PackageID DateCreated

    ABC 1111 2010-05-01

    ABC 1112 2010-05-02

    ABC 1116 2010-05-06

    DEF 1254 2009-04-01

    DEF 1456 2009-08-23

    GHI 9819 2010-02-21

    GHI 9845 2010-02-23

    ...

    ...

    I need to query out only those records where the difference in days(datecreated column) for each user is less than 3. For the above data, my output must be the following rows

    UserID PackageID DateCreated

    ABC 1111 2010-05-01

    ABC 1112 2010-05-02

    GHI 9819 2010-02-21

    GHI 9845 2010-02-23

    Do anyone know how to produce this kind of result?

  • Yes... Self Joined CTE with ROW_NUMBER() so you can join the rows with an offset of 1. Since you're brand new, you might want to take a look at the article at the first link in my signature line below. Using those methods to post have people tripping over each other to help you with a coded answer.

    And welcome aboard. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

    I tried to solve this by using CTE, but no luck.. 🙁 . Can I get some more ideas to approach this.

    Below is code what I tried.

    create table Test (UserID varchar(30),PackageID int,DateCreated datetime)

    go

    insert into Test

    values('ABC',1111,'2010-05-01'),

    ('ABC',1112,'2010-05-02'),

    ('ABC',1116,'2010-05-06'),

    ('DEF',1254,'2009-04-01'),

    ('DEF',1456,'2009-08-23'),

    ('GHI',9819,'2010-02-21'),

    ('GHI',9845,'2010-02-23')

    go

    With ct

    as

    (

    select UserID,PackageID,DateCreated,Row_number()Over(partition by UserID order by UserID)as RN from test

    )

    select t.UserID,t.PackageID,t.DateCreated

    from ct as c inner join test t on c.UserID = t.UserID

    where c.RN in (select MIN(RN) from ct ) and DATEDIFF(DD,c.DateCreated,t.DateCreated)< 3

    Thanks & Regards,
    MC

  • SELECT a.UserID,a.PackageID,a.DateCreated

    FROM Test a

    WHERE EXISTS (SELECT * FROM Test b

    WHERE b.UserID=a.UserID

    AND b.PackageID<>a.PackageID

    AND ABS(DATEDIFF(day,a.DateCreated,b.DateCreated))<=2)

    ORDER BY a.UserID,a.PackageID,a.DateCreated;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Hi Jeff, Thanks for the reply. It worked well.

  • Sample data:

    declare @t_table table

    (

    UserID VARCHAR(10),

    PackageID INT,

    DateCreated DATETIME

    );

    insert into @t_table (UserID, PackageID, DateCreated)

    select 'ABC', 1111, '2010-05-01' union

    select 'ABC', 1112, '2010-05-02' union

    select 'ABC', 1116, '2010-05-06' union

    select 'DEF', 1254, '2009-04-01' union

    select 'DEF', 1456, '2009-08-23' union

    select 'GHI', 9819, '2010-02-21' union

    select 'GHI', 9845, '2010-02-23';

    This is the CTE method:

    with cteTemp (RowID, UserID, PackageID, DateCreated)

    as

    (

    select ROW_NUMBER() OVER (ORDER BY UserID, DateCreated),

    UserID,

    PackageID,

    DateCreated

    from @t_table

    )

    select ct.UserID,

    ct.PackageID,

    ct.DateCreated

    from cteTemp ct

    join cteTemp ct_2

    on ct_2.UserID = ct.UserID

    and abs(ct_2.RowID - ct.RowID) = 1

    and ABS(DATEDIFF(dd, ct_2.DateCreated, ct.DateCreated)) < 3

    order by UserID, DateCreated

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Hi MC,

    Try this

    With ct

    as

    (

    select UserID,PackageID,DateCreated,Row_number()Over(partition by UserID order by UserID)as RN from test

    ) ,

    WITH ct2 AS (

    SELECT A.*

    FROM

    (select c.userID, C.packageID, c.datecreated, c1.packageID AS PackageID1, C1.datecreated AS Datecreated1,

    CASE WHEN DATEDIFF(dd,c.datecreated, c1.datecreated) < 3 THEN 'YES' ELSE 'NO' END AS Duplicate

    from ct as c inner join ct c1 on c.UserID = c1.UserID AND c.RN = C1.RN-1

    ) A

    WHERE A.duplicate = 'YES'

    )

    SELECT E.*

    FROM (

    SELECT userID, PackageID, datecreated FROM ct2

    UNION

    SELECT UserID, packageID1, datecreated1 FROM ct2 ) E

  • nmalepati (5/13/2010)


    Hi Jeff, Thanks for the reply. It worked well.

    Actually, that was Mark. Heh... I pass your thanks along.

    Nice job, Mark!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/13/2010)


    nmalepati (5/13/2010)


    Hi Jeff, Thanks for the reply. It worked well.

    Actually, that was Mark. Heh... I pass your thanks along.

    Nice job, Mark!

    Cheers Jeff.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Hi all,

    Many thanks for the reply...

    Thanks & Regards,
    MC

  • nmalepati (5/11/2010)


    Hello All,

    I have a table that has data in below format:

    UserID PackageID DateCreated

    ABC 1111 2010-05-01

    ABC 1112 2010-05-02

    ABC 1116 2010-05-06

    DEF 1254 2009-04-01

    DEF 1456 2009-08-23

    GHI 9819 2010-02-21

    GHI 9845 2010-02-23

    ...

    ...

    I need to query out only those records where the difference in days(datecreated column) for each user is less than 3. For the above data, my output must be the following rows

    UserID PackageID DateCreated

    ABC 1111 2010-05-01

    ABC 1112 2010-05-02

    GHI 9819 2010-02-21

    GHI 9845 2010-02-23

    Do anyone know how to produce this kind of result?

    select a.* from table1 a

    inner join table1 b

    on (a.userid = b.userid )

    where datediff(day,a.datecreated,b.datecreated) < 3

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply