How to assign day of the week to different tills Just wondering if someone would be able to please help me with a problem that I am trying to solve using T-SQL.
I have a table for till numbers assigned to 2 members of staff as seen below:
CREATE TABLE [dbo].[TillNumbersAssignedtoStaff](
    [AssignedWeek] [date] NULL,
    [StaffID] [int] NULL,
    [TillNumbers] [varchar](3) NOT NULL,
    
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TillNumbersAssignedtoStaff]
VALUES ('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB5')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB4')
,('2020-10-19',190,'AB2')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB2')
,('2020-10-19',180,'BY5')
,('2020-10-19',180,'BA3')
,('2020-10-19',180,'BY4')
,('2020-10-19',180,'BA1')
,('2020-10-19',180,'BY2')
,('2020-10-19',180,'BE2')

I also have a daily allocation table as seen below:
CREATE TABLE [dbo].[DailyAlloc](
    [DayNo] [int] NOT NULL,
    [DayName] [nvarchar](20) NOT NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[DailyAlloc]
VALUES (1,'Monday')
,(2,'Tuesday')
,(3,'Wednesday')
,(4,'Thursday')
,(5,'Friday')
,(6,'Saturday')
,(7,'Sunday')

I would like to assign the daily numbers from Mon-Fri to the staff. Each daily count should not be more than the maximum average for the 5 days of the week e.g. If there are 9 tills to be assigned for the week, there should not be more than 2 tills assigned daily i.e. 9/5.
I would like the result to be as seen below:

Any help with this would be greatly appreciated.
Thanks
As mentined in my comment, you need an order to things especially when  ypou want to use row_number, the it is demanded.
The query does in principle what you want, but as there is no order to your table, or outcome, the result like you want id not possible.


CREATE TABLE [TillNumbersAssignedtoStaff](
    [AssignedWeek] date NULL,
    [StaffID] int NULL,
    [TillNumbers] varchar(3) NOT NULL
    
) 



GO


✓




INSERT INTO [dbo].[TillNumbersAssignedtoStaff]
VALUES ('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB5')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB4')
,('2020-10-19',190,'AB2')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB2')
,('2020-10-19',180,'BY5')
,('2020-10-19',180,'BA3')
,('2020-10-19',180,'BY4')
,('2020-10-19',180,'BA1')
,('2020-10-19',180,'BY2')
,('2020-10-19',180,'BE2')
GO


15 rows affected




WITH X AS (
SELECT *
     ,ROW_NUMBER() OVER (PARTITION BY [StaffID] ORDER BY [AssignedWeek],[StaffID]) rn 
FROM TillNumbersAssignedtoStaff
 )
 SELECT [AssignedWeek],[StaffID],[TillNumbers]
      ,ROW_NUMBER() OVER (PARTITION BY (X.rn % 2),[StaffID] ORDER BY rn ASC) rn 
FROM X
ORDER BY [StaffID]
GO


AssignedWeek | StaffID | TillNumbers | rn
:----------- | ------: | :---------- | -:
2020-10-19   |     180 | BA3         |  1
2020-10-19   |     180 | BA1         |  2
2020-10-19   |     180 | BE2         |  3
2020-10-19   |     180 | BY5         |  1
2020-10-19   |     180 | BY4         |  2
2020-10-19   |     180 | BY2         |  3
2020-10-19   |     190 | AB1         |  1
2020-10-19   |     190 | AB5         |  2
2020-10-19   |     190 | AB4         |  3
2020-10-19   |     190 | AB3         |  4
2020-10-19   |     190 | AB1         |  1
2020-10-19   |     190 | AB3         |  2
2020-10-19   |     190 | AB3         |  3
2020-10-19   |     190 | AB2         |  4
2020-10-19   |     190 | AB2         |  5


db&lt;&gt;fiddle here