Access column just created in same CTE-CodePudding

I have data like this. Basically date and time.

DECLARE @sample table
(
    _date date,
    _time Time(0)
)

INSERT INTO @sample
VALUES
('2022-06-22', '09:00:00'),
('2022-06-22', '09:30:00'),
('2022-06-22', '10:00:00'),
('2022-06-22', '10:30:00'),
('2022-06-22', '11:00:00'),
('2022-06-23', '09:00:00'),
('2022-06-23', '09:30:00'),
('2022-06-23', '10:00:00');

And I added row number to it.

WITH cte AS(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY _date ORDER BY _time) AS rn     
    FROM @sample    
)
SELECT *
FROM cte

After that, the data look like this.

| _date      | _time    | rn |
|------------|----------|----|
| 2022-06-22 | 09:00:00 | 1  |
| 2022-06-22 | 09:30:00 | 2  |
| 2022-06-22 | 10:00:00 | 3  |
| 2022-06-22 | 10:30:00 | 4  |
| 2022-06-22 | 11:00:00 | 5  |
| 2022-06-23 | 09:00:00 | 1  |
| 2022-06-23 | 09:30:00 | 2  |
| 2022-06-23 | 10:00:00 | 3  |

Say now I want to loop though each row and modify the rn column, each rn is itself rn from last row.

| _date      | _time    | rn |
|------------|----------|----|
| 2022-06-22 | 09:00:00 | 1  |
| 2022-06-22 | 09:30:00 | 3  |
| 2022-06-22 | 10:00:00 | 6  |
| 2022-06-22 | 10:30:00 | 10 |
| 2022-06-22 | 11:00:00 | 15 |
| 2022-06-23 | 09:00:00 | 16 |
| 2022-06-23 | 09:30:00 | 18 |
| 2022-06-23 | 10:00:00 | 21 |

How can I add row number and do that in same CTE scope?

I know I can probably get away with another CTE right after and use LAG() function to get previous row and do what ever modification I want with that rn column, but somehow I need to do this in one CTE and it is a little too complicated to explain.

Thank you in advance for any help!

CodePudding user response：

A possible option is an additional windowed SUM() (I assume, that the rows are ordered by the _date column):

; WITH cte AS (
   SELECT 
       *,
       ROW_NUMBER() OVER (PARTITION BY _date ORDER BY _time) AS rn
   FROM @sample
)
SELECT 
    _date, _time, 
   SUM(rn) OVER (ORDER BY _date, rn) AS rn
FROM cte

CodePudding user response：

As @Zhorov pointed out, you don't need LAG, as it is just a SUM - however as you mentioned that you really want to have the numbers from one CTE, the two could be combined like this:

WITH cte AS(  
    SELECT *,  
        SUM(ROW_NUMBER() OVER (PARTITION BY _date ORDER BY _time)) OVER (ORDER BY _date, _time) AS rn  
    FROM @sample  
)  
SELECT *  
FROM cte

However, this can not be done, because Windowed functions cannot be used in the context of another windowed function or aggregate..

I was thinking about how to eliminate the inner ROW_NUMBER() because it is an easy logic, and came up with this:

WITH cte AS(  
    SELECT *,  
        SUM((SELECT COUNT(*) FROM @sample as inter WHERE inter._date = exter._date and inter._time <= exter._time)) OVER (ORDER BY _date, _time) AS rn  
    FROM @sample as exter  
)  
SELECT *  
FROM cte

But I don't know how much it hurts performance due to the correlated subquery - it really should be done in 2 separate set operations = not in the same CTE.