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:
SELECTta.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