CODEWITHSUNDEEP
sql-servert-sql
TAD
TAD

Reputation: 45

SSMS SQL Row_Number incorrect sort

I'm trying to sort by USID and then Date and restart the row number on USID, Utilized. But for some reason the Date is in the incorrect order, so the row numbering isn't doing what I need.

Currently this is what my code is getting me:

USID | Date   | Utilized | RowNumber
123  | 1/1/19 | T        | 1
123  | 1/2/19 | T        | 2
123  | 1/3/19 | T        | 3
123  | 1/19/19| T        | 4
123  | 1/20/19| T        | 5
123  | 1/21/19| T        | 6
123  | 1/4/19 | F        | 1
123  | 1/5/19 | F        | 2
123  | 1/6/19 | F        | 3
123  | 1/7/19 | F        | 4
123  | 1/8/19 | F        | 5
123  | 1/9/19 | F        | 6
125  | 1/1/19 | T        | 1
125  | 1/2/19 | T        | 2
125  | 1/3/19 | T        | 3
125  | 1/10/19| T        | 4
125  | 1/11/19| T        | 5
125  | 1/12/19| T        | 6
125  | 1/4/19 | F        | 1
125  | 1/5/19 | F        | 2
125  | 1/6/19 | F        | 3
125  | 1/7/19 | F        | 4
125  | 1/8/19 | F        | 5
125  | 1/9/19 | F        | 6

However I would want it to look like:

USID | Date   | Utilized | RowNumber
123  | 1/1/19 | T        | 1
123  | 1/2/19 | T        | 2
123  | 1/3/19 | T        | 3
123  | 1/4/19 | F        | 1
123  | 1/5/19 | F        | 2
123  | 1/6/19 | F        | 3
123  | 1/7/19 | F        | 4
123  | 1/8/19 | F        | 5
123  | 1/9/19 | F        | 6
123  | 1/19/19| T        | 1
123  | 1/20/19| T        | 2
123  | 1/21/19| T        | 3
125  | 1/1/19 | T        | 1
125  | 1/2/19 | T        | 2
125  | 1/3/19 | T        | 3
125  | 1/4/19 | F        | 1
125  | 1/5/19 | F        | 2
125  | 1/6/19 | F        | 3
125  | 1/7/19 | F        | 4
125  | 1/8/19 | F        | 5
125  | 1/9/19 | F        | 6
125  | 1/10/19| T        | 1
125  | 1/11/19| T        | 2
125  | 1/12/19| T        | 3

Here is what I have so far:

select 
   USID, cast(Date as datetime) as Date, Utilized, 
     ROW_NUMBER() OVER(PARTITION BY USID, Utilized 
                       ORDER BY USID, cast(Date as datetime) ASC, Utilized)
from vps_time_FullyUtilized

Where am I going wrong?

Upvotes: 0

Views: 143

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

Here is one gaps and islands solution. SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE T(USID INT, Date DATETIME, Utilized NVARCHAR(10))
    INSERT INTO T VALUES
    (123,  '1/1/19',  'T'),
    (123,  '1/2/19',  'T'),
    (123,  '1/3/19',  'T'),
    (123,  '1/4/19',  'F'),
    (123,  '1/5/19',  'F'),
    (123,  '1/6/19',  'F'),
    (123,  '1/7/19',  'F'),
    (123,  '1/8/19',  'F'),
    (123,  '1/9/19',  'F'),
    (123,  '1/19/19', 'T'),
    (123,  '1/20/19', 'T'),
    (123,  '1/21/19', 'T'),
    
    (223,  '1/1/19',  'T'),
    (223,  '1/2/19',  'T'),
    (223,  '1/3/19',  'T'),
    (223,  '1/4/19',  'F'),
    (223,  '1/5/19',  'F'),
    (223,  '1/6/19',  'F'),
    (223,  '1/7/19',  'F'),
    (223,  '1/8/19',  'F'),
    (223,  '1/9/19',  'F'),
    (223,  '1/19/19', 'T'),
    (223,  '1/20/19', 'T'),
    (223,  '1/21/19', 'T')

Query 1:

WITH DataWithEndBoundries AS
(
    SELECT 
        USID,Date,Utilized,
        --Mark your groups here
        IsNewGroup = CASE WHEN ISNULL(LAG(Utilized) OVER (ORDER BY USID,Date),Utilized)<>Utilized THEN 1 ELSE 0 END
    FROM 
        T
)
,VirtualGroup AS
(
    SELECT 
        *,
        --This serialzes the marked groups into clusters 
        VirtualGroupID = SUM(IsNewGroup) OVER (ORDER BY USID, Date ROWS UNBOUNDED PRECEDING)
    FROM 
        DataWithEndBoundries
)
SELECT 
    USID, Date, Utilized,
    --Now you can row number agianst the Virtualized value
    RowNumber = ROW_NUMBER() OVER (PARTITION BY USID, VirtualGroupID ORDER BY Date)
FROM 
    VirtualGroup
ORDER BY 
    USID,Date

Results:

| USID |                 Date | Utilized | RowNumber |
|------|----------------------|----------|-----------|
|  123 | 2019-01-01T00:00:00Z |        T |         1 |
|  123 | 2019-01-02T00:00:00Z |        T |         2 |
|  123 | 2019-01-03T00:00:00Z |        T |         3 |
|  123 | 2019-01-04T00:00:00Z |        F |         1 |
|  123 | 2019-01-05T00:00:00Z |        F |         2 |
|  123 | 2019-01-06T00:00:00Z |        F |         3 |
|  123 | 2019-01-07T00:00:00Z |        F |         4 |
|  123 | 2019-01-08T00:00:00Z |        F |         5 |
|  123 | 2019-01-09T00:00:00Z |        F |         6 |
|  123 | 2019-01-19T00:00:00Z |        T |         1 |
|  123 | 2019-01-20T00:00:00Z |        T |         2 |
|  123 | 2019-01-21T00:00:00Z |        T |         3 |
|  223 | 2019-01-01T00:00:00Z |        T |         1 |
|  223 | 2019-01-02T00:00:00Z |        T |         2 |
|  223 | 2019-01-03T00:00:00Z |        T |         3 |
|  223 | 2019-01-04T00:00:00Z |        F |         1 |
|  223 | 2019-01-05T00:00:00Z |        F |         2 |
|  223 | 2019-01-06T00:00:00Z |        F |         3 |
|  223 | 2019-01-07T00:00:00Z |        F |         4 |
|  223 | 2019-01-08T00:00:00Z |        F |         5 |
|  223 | 2019-01-09T00:00:00Z |        F |         6 |
|  223 | 2019-01-19T00:00:00Z |        T |         1 |
|  223 | 2019-01-20T00:00:00Z |        T |         2 |
|  223 | 2019-01-21T00:00:00Z |        T |         3 |

Upvotes: 2

Related Questions