How to deal with sequence in SQL Server?

Question

I have a question handling sequence in SQL Server.

I want to get result01.

Here is sample table.

CREATE TABLE temp01
(
    SEQ         int,
    cat01       numeric(6,0),
    cat02       numeric(6,0),
    dt_day      date,
    dt_week     date,
    dt_month    date,
    price       decimal(10,0)
)

INSERT INTO temp01 VALUES (1, 230, 1, '2019-01-01', '2019-01-05', '2019-01-31', 16000)
INSERT INTO temp01 VALUES (2, 230, 1, '2019-01-02', '2019-01-05', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (3, 230, 1, '2019-01-03', '2019-01-05', '2019-01-31', 13000)
INSERT INTO temp01 VALUES (4, 230, 1, '2019-01-04', '2019-01-05', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (5, 230, 1, '2019-01-05', '2019-01-12', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (6, 230, 1, '2019-01-06', '2019-01-12', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (7, 230, 1, '2019-01-07', '2019-01-12', '2019-01-31', 19000)
INSERT INTO temp01 VALUES (1, 230, 2, '2019-01-01', '2019-01-05', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (2, 230, 2, '2019-01-02', '2019-01-05', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (3, 230, 2, '2019-01-03', '2019-01-12', '2019-01-31', 12000)
INSERT INTO temp01 VALUES (4, 230, 2, '2019-01-04', '2019-01-12', '2019-01-31', 17000)
INSERT INTO temp01 VALUES (5, 230, 2, '2019-01-05', '2019-01-12', '2019-01-31', 22000)
INSERT INTO temp01 VALUES (6, 230, 2,' 2019-01-06', '2019-01-12', '2019-01-31', NULL)
INSERT INTO temp01 VALUES (7, 230, 2,' 2019-01-07', '2019-01-12', '2019-01-31', 21000)

And I want to get this table from temp01.

[result01]

    | SEQ | cat01 | cat02 |   dt_day   | price | sub_seq |
    | --- | ----- | ----- | ---------- | ----- | ------- |
    |  1  |  230  |   1   | 2019-01-01 | 16000 |    1    |
    |  3  |  230  |   1   | 2019-01-03 | 13000 |    2    |
    |  7  |  230  |   1   | 2019-01-07 | 19000 |    3    |
    |  3  |  230  |   2   | 2019-01-03 | 12000 |    1    |
    |  4  |  230  |   2   | 2019-01-04 | 17000 |    2    |    
    |  5  |  230  |   2   | 2019-01-05 | 22000 |    3    | 
    |  7  |  230  |   2   | 2019-01-07 | 21000 |    4    |

....

So, I use this code. I think this code incorrect.

WITH ROW_VALUE AS
(
    SELECT SEQ
        , dt_day
        , cat01
        , cat02
        , price
        , ROW_NUMBER() OVER (ORDER BY cat01, cat02, dt_day) AS sub_seq
    FROM (
        SELECT SEQ
            , cat01
            , cat02
            , dt_day
            , dt_week
            , dt_month
            , price
        FROM temp01
        WHERE price IS NOT NULL
            )val
)
SELECT DISTINCT *
FROM ROW_VALUE
ORDER BY cat01, cat02, dt_day

How can I get a result01 table?

Please, review my code.

Answer 1

You should PARTITION BY cat01, cat02 and ORDER BY dt_day:

SELECT SEQ
    , cat01
    , cat02
    , dt_day
    , dt_week
    , dt_month
    , price
    , ROW_NUMBER() OVER (PARTITION BY cat01, cat02 ORDER BY dt_day) AS sub_seq
FROM temp01
WHERE price IS NOT NULL

See the demo.
Results:

SEQ	cat01	cat02	dt_day	dt_week	dt_month	price	sub_seq
1	230	1	2019-01-01	2019-01-05	2019-01-31	16000	1
3	230	1	2019-01-03	2019-01-05	2019-01-31	13000	2
7	230	1	2019-01-07	2019-01-12	2019-01-31	19000	3
3	230	2	2019-01-03	2019-01-12	2019-01-31	12000	1
4	230	2	2019-01-04	2019-01-12	2019-01-31	17000	2
5	230	2	2019-01-05	2019-01-12	2019-01-31	22000	3
7	230	2	2019-01-07	2019-01-12	2019-01-31	21000	4