Using Top to split a field into segments in SQL Server

Question

I have two tables.

Table A

╔══════════════════════════════════════════╗
║ Date            Segment        Total     ║
╠══════════════════════════════════════════╣
║ 11/04/2015        1            3         ║
║ 12/04/2015        3            2         ║
║ 13/04/2015        5            1         ║
╚══════════════════════════════════════════╝

Table B

╔══════════════════════════════════════════╗
║ Date            Sequence         Segment ║
╠══════════════════════════════════════════╣
║ 11/04/2015        4               null   ║
║ 11/04/2015        2               null   ║
║ 11/04/2015        9               null   ║
║ 11/04/2015        6               null   ║
║ 11/04/2015        12              null   ║
║ 12/04/2015        9               null   ║
║ 12/04/2015        5               null   ║
║ 12/04/2015        1               null   ║
║ 13/04/2015        4               null   ║
║ 13/04/2015        6               null   ║
║ 13/04/2015        2               null   ║
╚══════════════════════════════════════════╝

The Final Output should look like below. Segment 1 has top 3 sequence and so on. Date on Table A = Date on Table B.

Table B

╔═════════════════════════════════════════╗
║ Date            Sequence        Segment ║
╠═════════════════════════════════════════╣
║ 11/04/2015        4               1     ║
║ 11/04/2015        2               1     ║
║ 11/04/2015        9               null  ║
║ 11/04/2015        6               1     ║
║ 11/04/2015        12              null  ║
║ 12/04/2015        9               null  ║
║ 12/04/2015        5               3     ║
║ 12/04/2015        1               3     ║
║ 13/04/2015        4               null  ║
║ 13/04/2015        6               null  ║
║ 13/04/2015        2               5     ║
╚═════════════════════════════════════════╝

Answer 1

On the given input as you describe in your Question. You can use this as a solution:

-- Create demo data
CREATE TABLE #a(date date, segment int, total int)

INSERT INTO #a(date, segment, total)
VALUES  (N'04/11/2015',1,3),
        (N'04/12/2015',3,2),
        (N'04/13/2015',5,1)

CREATE TABLE #b(date date, sequence int, segment int)

INSERT INTO #b(date, sequence)
VALUES  (N'04/11/2015',4),
        (N'04/11/2015',2),
        (N'04/11/2015',9),
        (N'04/11/2015',6),
        (N'04/11/2015',12),
        (N'04/12/2015',9),
        (N'04/12/2015',5),
        (N'04/12/2015',1),
        (N'04/13/2015',4), 
        (N'04/13/2015',6), 
        (N'04/13/2015',2)

-- Your part
SELECT b.date, b.sequence, a.segment
FROM #a as a
RIGHT JOIN (
        SELECT date, sequence, segment,
            ROW_NUMBER() OVER(PARTITION BY date ORDER BY sequence) as seqRank
        FROM #b
    ) as b
    ON b.date = a.date
    AND b.seqRank <= a.total

-- Cleanup
DROP TABLE #a
DROP TABLE #b

It will provide this output:

date       sequence    segment
---------- ----------- -----------
2015-04-11 2           1
2015-04-11 4           1
2015-04-11 6           1
2015-04-11 9           NULL
2015-04-11 12          NULL
2015-04-12 1           3
2015-04-12 5           3
2015-04-12 9           NULL
2015-04-13 2           5
2015-04-13 4           NULL
2015-04-13 6           NULL

Due to the fact, that I'm using ROW_NUMBER, it will work even on duplicates with the same date and sequence number. Other plus would be the low cpu usage.