Reset ranking based on flag

  • Hi all,

    Looking for some help with a query problem that's had me banging my head against a wall 🙂

    Basically I need to reset a ranking based on a flag in my dataset. I feel like I need to generate another column on which to do the grouping but I'm not sure how to go about it. My current query is this:

    SELECT

    ta.EmailAddress

    ,tb.CreatedAt

    ,CASE

    WHEN DATEDIFF(dd,LAG(tb.CreatedAt,1) OVER (PARTITION BY ta.EmailAddress ORDER BY tb.CreatedAt),tb.CreatedAt) > 30

    THEN 1

    ELSE 0

    END AS GroupResetFlag

    ,RANK() OVER(PARTITION BY ta.EmailAddress ORDER BY tb.CreatedAt) AS InitialRank

    FROM

    TableA ta

    INNER JOIN TableB tb

    ON tb.Id = ta.Id

    ORDER BY ta.EmailAddress, tb.CreatedAt

    The results look like this:

    EmailAddress | CreatedAt | GroupResetFlag | InitialRank

    joe@bloggs.com | 2012-09-04 14:00 | 0 | 1

    joe@bloggs.com | 2012-09-04 14:30 | 0 | 2

    joe@bloggs.com | 2012-09-05 12:51 | 0 | 3

    joe@bloggs.com | 2012-11-05 09:00 | 1 | 4

    joe@bloggs.com | 2012-11-06 11:22 | 0 | 5

    The ranking should be reset when the current 'CreatedAt' value is greater than 30 days after the previous 'CreatedAt' value for the same email. So I'm looking to get a result set like this:

    EmailAddress | CreatedAt | GroupResetFlag | InitialRank | NewRank

    joe@bloggs.com | 2012-09-04 14:00 | 0 | 1 | 1

    joe@bloggs.com | 2012-09-04 14:30 | 0 | 2 | 2

    joe@bloggs.com | 2012-09-05 12:51 | 0 | 3 | 3

    joe@bloggs.com | 2012-11-05 09:00 | 1 | 5 | 1

    joe@bloggs.com | 2012-11-06 11:22 | 0 | 6 | 2

    Any ideas on how to do this? I'm running SQL2012.

    Thanks in advance!

  • Could you please supply the CREATE TABLE statement and some sample data please?

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Sure, here you go:

    CREATE TABLE Request

    (

    RequestId INT NOT NULL,

    EmailAddress VARCHAR(50) NOT NULL

    );

    CREATE TABLE Quote

    (

    QuoteId INT NOT NULL,

    RequestId INT NOT NULL,

    CreatedAt DATETIME NOT NULL

    );

    INSERT INTO Request VALUES (1, 'joe@bloggs.com');

    INSERT INTO Request VALUES (2, 'bob@bloggs.com');

    INSERT INTO Quote VALUES (1,1,'2012-09-04 14:00');

    INSERT INTO Quote VALUES (2,1,'2012-09-04 14:30');

    INSERT INTO Quote VALUES (3,1,'2012-09-05 12:51');

    INSERT INTO Quote VALUES (4,1,'2012-11-09 09:00');

    INSERT INTO Quote VALUES (5,1,'2012-11-09 11:00');

    INSERT INTO Quote VALUES (6,2,'2012-09-04 15:00');

    INSERT INTO Quote VALUES (7,2,'2012-09-04 15:00');

    INSERT INTO Quote VALUES (8,2,'2012-12-30 19:30');

    INSERT INTO Quote VALUES (9,2,'2012-12-30 19:30');

    SELECT

    r.RequestId

    ,q.QuoteId

    ,r.EmailAddress

    ,q.CreatedAt

    ,CASE

    WHEN DATEDIFF(dd,LAG(q.CreatedAt,1) OVER (PARTITION BY r.EmailAddress ORDER BY q.CreatedAt),q.CreatedAt) > 30

    THEN 1

    ELSE 0

    END AS GroupResetFlag

    ,DENSE_RANK() OVER(PARTITION BY r.EmailAddress ORDER BY q.CreatedAt) AS InitialRank

    FROM

    Request r

    INNER JOIN Quote q

    ON r.RequestId = q.RequestId

    ORDER BY

    r.EmailAddress

    ,q.CreatedAt;

    I've included a slightly modified query to use the created tables.

  • Waiting for the pros to offer their solution but this is my attempt:

    with cte AS (

    select quoteid, requestid, createdat,

    ISNULL((select top 1 datediff(dd, b.createdat, a.createdat)

    from quote as b

    WHERE a.requestid <= b.requestid

    and datediff(dd, b.createdat, a.createdat) > 30), 0) as datedifference

    from quote as a)

    select b.EmailAddress, a.createdAt, ROW_NUMBER() OVER (PARTITION BY a.requestid, a.datedifference order by datedifference)

    from cte as a

    inner join Request as b

    on a.requestid = b.requestid

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • Here's another

    WITH CTE AS (

    SELECT

    r.RequestId

    ,q.QuoteId

    ,r.EmailAddress

    ,q.CreatedAt

    ,CASE

    WHEN DATEDIFF(dd,LAG(q.CreatedAt,1) OVER (PARTITION BY r.EmailAddress ORDER BY q.CreatedAt),q.CreatedAt) > 30

    THEN 1

    ELSE 0

    END AS GroupResetFlag

    ,DENSE_RANK() OVER(PARTITION BY r.EmailAddress ORDER BY q.CreatedAt,q.QuoteId) AS InitialRank

    FROM

    Request r

    INNER JOIN Quote q

    ON r.RequestId = q.RequestId)

    SELECT r.RequestId

    ,r.QuoteId

    ,r.EmailAddress

    ,r.CreatedAt

    ,r.GroupResetFlag

    ,r.InitialRank - ISNULL(ca.InitialRank-1,0) AS InitialRank

    FROM CTE r

    OUTER APPLY(SELECT TOP 1 * FROM CTE r2

    WHERE r2.EmailAddress = r.EmailAddress

    AND r2.GroupResetFlag = 1

    AND r2.CreatedAt <= r.CreatedAt

    ORDER BY r2.CreatedAt DESC, r2.QuoteId DESC) ca

    ORDER BY

    r.EmailAddress

    ,r.CreatedAt;

    ____________________________________________________

    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
  • Many thanks to both of you - both options work and have given my brain the kick in the right direction it needed at this end of the week! 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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