Search code examples
sql-serverdense-rank

Unable to get desired output using Dense Rank

Can you please help me how to get the expected output as shown in the screenshot below?

DECLARE @TT TABLE  ( CUSTOMERID INT, TYPE VARCHAR(100) , D1 DATE)
INSERT INTO @TT VALUES ( 15001,'TYPE1', '4/1/2017')
INSERT INTO @TT VALUES ( 15001,'TYPE1', '4/2/2017')
INSERT INTO @TT VALUES ( 15001,'TYPE1', '4/3/2017')
INSERT INTO @TT VALUES ( 15001,'NON TYPE1', '4/4/2017')
INSERT INTO @TT VALUES ( 15001,'NON TYPE1', '4/5/2017')
INSERT INTO @TT VALUES ( 15001,'NON TYPE1', '4/6/2017')
INSERT INTO @TT VALUES ( 245,'Non TYPE1', '4/1/2017')
INSERT INTO @TT VALUES ( 245,'Non TYPE1', '4/2/2017')
INSERT INTO @TT VALUES ( 245,'Non TYPE1', '4/3/2017')
INSERT INTO @TT VALUES ( 245,'TYPE1', '4/4/2017')
INSERT INTO @TT VALUES ( 245,'TYPE1', '4/5/2017')
INSERT INTO @TT VALUES ( 245,'TYPE1', '4/6/2017')

A query is:

SELECT * ,
       DENSE_RANK() OVER ( PARTITION BY CUSTOMERID ORDER BY CUSTOMERID,TYPE) DR
FROM @TT
WHERE CUSTOMERID = 15001
ORDER BY CUSTOMERID,D1

SELECT *,
       DENSE_RANK() OVER ( PARTITION BY CUSTOMERID ORDER BY CUSTOMERID,TYPE) DR
FROM @TT
WHERE CUSTOMERID = 245
ORDER BY CUSTOMERID,D1

enter image description here

Thank you for the help.

Solution

  • You want to order groups by the minimum date. I think it is something like this:

    SELECT t.*,
       DENSE_RANK() OVER ( PARTITION BY CUSTOMERID ORDER BY mind1, TYPE) DR
FROM (SELECT t.*, MIN(d1) OVER (PARTITION BY CUSTOMERID, TYPE) as mind1
      FROM @TT t
     ) t
WHERE CUSTOMERID = 15001
ORDER BY CUSTOMERID, D1;

    This sort of assumes that the types are not interleaved (as in your examples). If that is the case, then please ask another question. That significantly changes the question. That question should also provide better sample data and desired results.