Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reset ranking based on flag Expand / Collapse
Author
Message
Posted Thursday, September 05, 2013 5:27 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 35, Visits: 94
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!
Post #1491723
Posted Thursday, September 05, 2013 5:33 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:57 AM
Points: 704, Visits: 3,270
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

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
Post #1491726
Posted Thursday, September 05, 2013 6:10 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 35, Visits: 94
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.
Post #1491739
Posted Thursday, September 05, 2013 6:56 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:57 AM
Points: 704, Visits: 3,270
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

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
Post #1491761
Posted Thursday, September 05, 2013 7:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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;



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1491776
Posted Thursday, September 05, 2013 7:47 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:07 AM
Points: 35, Visits: 94
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!
Post #1491794
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse