Ranking Based on dates
-
Hi,
I am looking for a SQL which can group and rank the output based on date sequence. I can get the output using the loop, hoping some one has solution without looping.
Sample data:
CREATE TABLE #1
(
NAME VARCHAR(10),
DT_START DATETIME,
DT_END DATETIME,
LOCATION VARCHAR(10)
)
INSERT #1
SELECT 'Jack', '1997-12-18', '1998-12-08', 'BANGALORE'
UNION ALL SELECT 'Jack', '1998-12-09', '1999-10-13', 'BANGALORE'
UNION ALL SELECT 'Jack', '1999-10-14', '2000-07-26', 'BANGALORE'
UNION ALL SELECT 'Jack', '2000-07-27', '2001-03-26', 'BANGALORE'
UNION ALL SELECT 'Jack', '2001-03-27', '2002-07-01', 'CHENNAI'
UNION ALL SELECT 'Jack', '2002-07-02', '2003-01-29', 'CHENNAI'
UNION ALL SELECT 'Jack', '2003-01-30', '2003-04-30', 'MUMBAI'
UNION ALL SELECT 'Jack', '2003-05-01', '2004-01-29', 'MUMBAI'
UNION ALL SELECT 'Jack', '2004-01-30', '2004-03-15', 'BANGALORE'
UNION ALL SELECT 'Jack', '2004-03-16', '2005-01-31', 'BANGALORE'
Output:
NAME DT_START DT_END LOCATION LocationRank
Jack 12/18/1997 12/8/1998 BANGALORE 1
Jack 12/9/1998 10/13/1999 BANGALORE 1
Jack 10/14/1999 7/26/2000 BANGALORE 1
Jack 7/27/2000 3/26/2001 BANGALORE 1
Jack 3/27/2001 7/1/2002 CHENNAI 2
Jack 7/2/2002 1/29/2003 CHENNAI 2
Jack 1/30/2003 4/30/2003 MUMBAI 3
Jack 5/1/2003 1/29/2004 MUMBAI 3
Jack 1/30/2004 3/15/2004 BANGALORE 4
Jack 3/16/2004 1/31/2005 BANGALORE 4
Note : the last rows even though has location as Bangalore it has been given the next rank.
-
I believe the article from Jeff Moden http://qa.sqlservercentral.com/articles/T-SQL/71550/[/url] is exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Crazy Nash, I'm no performance expert by any means, nor an index pro, and as you don't give any information with respect to your indexes, there are no guarantees with this beyond your sample data. However, I believe this will give you your desired output based on your sample data. A couple of questions, though. Is there more than one person, or is Jack the only traveller? You have defined your date columns as DATETIME, but do not supply any time elements. Are there instances where Jack could maybe start something with two different clients in the same city on the same day? If so, this may not work. I'm eager to see how some of the gurus would solve this.
WITH rCTE AS
(
SELECT
NAME,
DT_START,
DT_END,
LOCATION,
CAST(1 AS INT) AS LocationRank
FROM #1 a
WHERE DT_START =
(
SELECT MIN(DT_START) AS MIN_DT
FROM #1 b
WHERE a.NAME = b.NAME
)
UNION ALL
SELECT
b.NAME,
b.DT_START,
b.DT_END,
b.LOCATION,
LocationRank = CASE WHEN r.LOCATION = b.LOCATION THEN LocationRank
ELSE LocationRank + 1
END
FROM #1 b INNER JOIN rCTE r
ON r.NAME = b.NAME
AND b.DT_START =
(
SELECT NEXT_START FROM
(
SELECT
DT_START AS NEXT_START,
ROW_NUMBER() OVER (PARTITION BY c.NAME ORDER BY c.DT_START) AS RN
FROM #1 c
WHERE c.NAME = r.NAME
AND c.DT_START > r.DT_START
) t1
WHERE RN = 1
)
)
SELECT
*
FROM rCTE OPTION (MAXRECURSION 0)
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Here is my solution.
CREATE TABLE #Test
(
NAME VARCHAR(10),
DT_START DATETIME,
DT_END DATETIME,
LOCATION VARCHAR(10)
);
INSERT #Test
SELECT 'Jack', '1997-12-18', '1998-12-08', 'BANGALORE'
UNION ALL SELECT 'Jack', '1998-12-09', '1999-10-13', 'BANGALORE'
UNION ALL SELECT 'Jack', '1999-10-14', '2000-07-26', 'BANGALORE'
UNION ALL SELECT 'Jack', '2000-07-27', '2001-03-26', 'BANGALORE'
UNION ALL SELECT 'Jack', '2001-03-27', '2002-07-01', 'CHENNAI'
UNION ALL SELECT 'Jack', '2002-07-02', '2003-01-29', 'CHENNAI'
UNION ALL SELECT 'Jack', '2003-01-30', '2003-04-30', 'MUMBAI'
UNION ALL SELECT 'Jack', '2003-05-01', '2004-01-29', 'MUMBAI'
UNION ALL SELECT 'Jack', '2004-01-30', '2004-03-15', 'BANGALORE'
UNION ALL SELECT 'Jack', '2004-03-16', '2005-01-31', 'BANGALORE';
go
with basedata as (
select
t1.NAME,
t1.DT_START,
t1.DT_END,
t1.LOCATION,
row_number() over (partition by t1.NAME order by t1.DT_START) LocationRank
from
#Test t1
left outer join #Test t2
on (t1.NAME = t2.NAME and t1.LOCATION = t2.LOCATION and t1.DT_START = dateadd(dd,1,t2.DT_END))
where
t2.NAME is null
union all
select
t1.NAME,
t1.DT_START,
t1.DT_END,
t1.LOCATION,
t2.LocationRank
from
basedata t2
inner join #Test t1
on (t1.NAME = t2.NAME and t1.LOCATION = t2.LOCATION and t1.DT_START = dateadd(dd,1,t2.DT_END))
)
select
NAME,
DT_START,
DT_END,
LOCATION,
LocationRank
from
basedata
order by
NAME, DT_START
option (maxrecursion 0);
go
drop table #Test;
go
-
Looks like Greg beat me this time.
-
Lynn Pettis (8/29/2012)
Looks like Greg beat me this time....And I consider that a great honor Lynn 🙂
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less. -
Another way
WITH CombinedRanges AS (SELECT s1.NAME,
s1.LOCATION,
s1.DT_START,
MIN(t1.DT_END) AS DT_END
FROM #1 s1
INNER JOIN #1 t1 ON t1.NAME=s1.NAME AND t1.LOCATION=s1.LOCATION
AND s1.DT_START <= t1.DT_END
AND NOT EXISTS(SELECT * FROM #1 t2
WHERE t2.NAME=t1.NAME AND t2.LOCATION=t1.LOCATION
AND (t1.DT_END+1) >= t2.DT_START AND t1.DT_END < t2.DT_END)
WHERE NOT EXISTS(SELECT * FROM #1 s2
WHERE s2.NAME=s1.NAME AND s2.LOCATION=s1.LOCATION
AND s1.DT_START > s2.DT_START AND (s1.DT_START-1) <= s2.DT_END)
GROUP BY s1.NAME,s1.LOCATION,s1.DT_START)
SELECT t.NAME,
t.DT_START,
t.DT_END,
t.LOCATION,
DENSE_RANK() OVER(PARTITION BY t.NAME ORDER BY c.DT_START) AS LocationRank
FROM #1 t
INNER JOIN CombinedRanges c ON c.NAME = t.NAME AND c.LOCATION = t.LOCATION
AND t.DT_START BETWEEN c.DT_START AND c.DT_END
AND t.DT_END BETWEEN c.DT_START AND c.DT_END
ORDER BY t.NAME,LocationRank,t.DT_START;
____________________________________________________
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply