Add a unique ID to results of a CTE

ttist25
ttist25 used Ask the Experts™
on
Good morning!

I'm working in SQL Server 2012 and using a windowing function to identify sets of rows grouping by a person id, location id, and a date field with a specified time interval.  

For example:
Person 1 | Location 1 | 1/1/2019
Person 1 | Location 1 | 1/2/2019
Person 2 | Location 1 | 1/1/2019
Person 2 | Location 2 | 1/1/2019

In the above example, my window function returns the following:
Person 1 | Location 1 | 1/1/2019 | 1
Person 1 | Location 1 | 1/2/2019 | 2
Person 2 | Location 1 | 1/1/2019 | 1
Person 2 | Location 2 | 1/1/2019 | 1

What I would like to do is generate a unique id for each group so that the result would be something like::
Person 1 | Location 1 | 1/1/2019 | 1 | 1
Person 1 | Location 1 | 1/2/2019 | 2 | 1
Person 2 | Location 1 | 1/1/2019 | 1 | 2
Person 2 | Location 2 | 1/1/2019 | 1 | 3

Any ideas?

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What is your windowing function that you are using?

Commented:
You could put the query with the windowing function in a CTE and then query from it and DENSE_RANK() OVER (PARTITION BY (the person1 column and the result of the window) ORDER BY the window number)

Having the query that produces the output for the Windowing Function would help me put it into a final query, then you could go from there.
Nothing easier than to do a few tests in SSMS (and I did many before the result is acceptable...)
DECLARE @t TABLE (Name char(20), Location char(20), SomeDate date)
INSERT INTO @t VALUES ('Person 1', 'Location 1', '2019/01/01')
INSERT INTO @t VALUES ('Person 1', 'Location 1', '2019/02/01')
INSERT INTO @t VALUES ('Person 2', 'Location 1', '2019/01/01')
INSERT INTO @t VALUES ('Person 2', 'Location 2', '2019/01/01')
INSERT INTO @t VALUES ('Person 1', 'Location 2', '2019/01/01')
INSERT INTO @t VALUES ('Person 1', 'Location 2', '2019/03/01')

;WITH cte AS (
  SELECT Name, Location, SomeDate, 
         LocID = ROW_NUMBER() OVER (PARTITION BY Name, Location ORDER BY SomeDate), 
		 GroupID = RANK() OVER (ORDER BY Name, Location), 
		 GroupID2 = DENSE_RANK() OVER (ORDER BY Name, Location) 
    FROM @t
 )
SELECT * FROM cte

Open in new window

Author

Commented:
Sorry for the delay in response - been a little crazy here.

This was a poorly written question but you both provided the keys to the solution.  pcelba - I was able to use your simple setup of the problem to test until I got where I needed to be.  I'll be using that process in the future so thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial