DENSE_RANK Question

[m***@yahoo.com]

I'm using SQL SERVER 2012, I have the following table for tracking the movement of assets, and I want to assign the same value to a new variable (trip) until the status changes. I've tried using the DENSE_RANK function, but I need to have the data sorted by the s_date field in order to define a trip. Not sure if I can do this, but I would appreciate any suggestions.

Thanks

Raw Data

ASSET_ID Status Info s_tdate
1 E NULL 2014-01-01 00:00:00.000
1 E NULL 2014-01-02 00:00:00.000
1 L NULL 2014-01-03 00:00:00.000
1 L 23 2014-01-04 00:00:00.000
1 E NULL 2014-01-05 00:00:00.000
1 E NULL 2014-01-06 00:00:00.000
1 L NULL 2014-01-07 00:00:00.000
1 L 23 2014-01-08 00:00:00.000
2 E NULL 2014-01-01 00:00:00.000
2 L NULL 2014-01-02 00:00:00.000
2 L 24 2014-01-03 00:00:00.000
2 L 24 2014-01-04 00:00:00.000
2 E NULL 2014-01-05 00:00:00.000
2 E NULL 2014-01-06 00:00:00.000
2 E NULL 2014-01-07 00:00:00.000
2 L NULL 2014-01-08 00:00:00.000
Result that I want
ASSET_ID Status Info s_tdate TRIP
1 E NULL 2014-01-01 00:00:00.000 1
1 E NULL 2014-01-02 00:00:00.000 1
1 L NULL 2014-01-03 00:00:00.000 2
1 L 23 2014-01-04 00:00:00.000 2
1 E NULL 2014-01-05 00:00:00.000 3
1 E NULL 2014-01-06 00:00:00.000 3
1 L NULL 2014-01-07 00:00:00.000 4
1 L 23 2014-01-08 00:00:00.000 4
2 E NULL 2014-01-01 00:00:00.000 1
2 L NULL 2014-01-02 00:00:00.000 2
2 L 24 2014-01-03 00:00:00.000 2
2 L 24 2014-01-04 00:00:00.000 2
2 E NULL 2014-01-05 00:00:00.000 3
2 E NULL 2014-01-06 00:00:00.000 3
2 E NULL 2014-01-07 00:00:00.000 3
2 L NULL 2014-01-08 00:00:00.000 4

SQL I'm using the following statements to create and populate the table, and the last one is not ranking as I want.

CREATE TABLE [dbo].[C](
[ASSET_ID] [numeric](18, 0) NULL,
[Status] [nvarchar](1) NULL,
[Info] [numeric](18, 0) NULL,
[s_tdate] [datetime] NULL
) ON [PRIMARY]

INSERT INTO Cvalues (1,'E',NULL,'1/1/2014')
INSERT INTO Cvalues (1,'E',NULL,'1/2/2014')
INSERT INTO Cvalues (1,'L',NULL,'1/3/2014')
INSERT INTO Cvalues (1,'L',23,'1/4/2014')
INSERT INTO Cvalues (1,'E',NULL,'1/5/2014')
INSERT INTO Cvalues (1,'E',NULL,'1/6/2014')
INSERT INTO Cvalues (1,'L',NULL,'1/7/2014')
INSERT INTO Cvalues (1,'L',23,'1/8/2014')
INSERT INTO Cvalues (2,'E',NULL,'1/1/2014')
INSERT INTO Cvalues (2,'L',NULL,'1/2/2014')
INSERT INTO Cvalues (2,'L',24,'1/3/2014')
INSERT INTO Cvalues (2,'L',24,'1/4/2014')
INSERT INTO Cvalues (2,'E',NULL,'1/5/2014')
INSERT INTO Cvalues (2,'E',NULL,'1/6/2014')
INSERT INTO Cvalues (2,'E',NULL,'1/7/2014')
INSERT INTO Cvalues (2,'L',NULL,'1/8/2014')
INSERT INTO Cvalues (2,'L',NULL,'41656')
INSERT INTO Cvalues (3,'L',24,'1/1/2014')
INSERT INTO Cvalues (3,'L',24,'1/2/2014')
INSERT INTO Cvalues (3,'E',NULL,'1/3/2014')
INSERT INTO Cvalues (3,'E',NULL,'1/4/2014')
INSERT INTO Cvalues (3,'E',NULL,'1/5/2014')
INSERT INTO Cvalues (3,'L',34,'1/6/2014')

DENSE_Rank Function
SELECT *, DENSE_RANK() OVER
(PARTITION BY ASSET_ID ORDER BY STATUS, ASSET_ID) AS Rank
FROM [UmlerMessages].[dbo].C

[Erland Sommarskog]

Post by m***@yahoo.com
I'm using SQL SERVER 2012, I have the following table for tracking the
movement of assets, and I want to assign the same value to a new
variable (trip) until the status changes. I've tried using the
DENSE_RANK function, but I need to have the data sorted by the s_date
field in order to define a trip. Not sure if I can do this, but I
would appreciate any suggestions.

Here is a solution which performs the operation in a single statement,
but if the data set is huge, it may prove to perform better in you loop
the assets in parallel, day by day. After all, this is a problem that
has a single-pass solution logically, but it is not expressible in T-SQL.
The below will sort the data a couple of times.

; WITH CTE AS (
SELECT *, row_number() OVER (PARTITION BY ASSET_ID ORDER BY s_tdate) AS rn_by_date,
row_number() OVER (PARTITION BY ASSET_ID ORDER BY Status, s_tdate) AS rn_by_status_date
FROM C
), CTE2 AS (
SELECT *, rn_by_date - rn_by_status_date AS grp,
dense_rank() OVER (PARTITION BY ASSET_ID, Status ORDER BY rn_by_date - rn_by_status_date) AS grprank
FROM CTE
)
SELECT *, dense_rank() OVER(PARTITION BY ASSET_ID ORDER BY grprank, Status)
FROM CTE2
ORDER BY ASSET_ID, s_tdate
go
DROP TABLE C

First we number the rows by asset in in two ways. One only by date and one
by status and date. In the next CTE we observe the difference between these
numbers. Within a group the difference is constant. To get better numbers
for the group, we run a dense_rank over them by asset and status. To
get the desired rank, we order the assets by group and then by state.

[m***@yahoo.com]

Thanks Erland, that does the trick for sure. Appreciate your help.