How to apply randomly selected values to distinct dates in SQL Server

Question

I have a table showing available dates for some staff with two fields - staffid and date with information that looks :`

staffid     date  
1           2016-01-01
1           2016-01-02
1           2016-01-03
2           2016-01-03
3           2016-01-01
3           2016-01-03

I need to generate a list of DISTINCT available dates from this table, where the staff selected to each date is selected randomly. I know how to select rows based on one distinct field, (see for example the answer here, but this will always select the rows based on a given order in the table (so for example staff 1 for January 1, while I need selection to be random so sometimes 1 will be selected as the distinct row and sometimes staff 3 will be selected.

The result needs to be ordered by date.

Possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — PM 77-1, Sep 29 '16 at 18:12
The question is not about selecting a random row, it is about selecting a DISTINCT random row. — user1480192, Sep 29 '16 at 18:14
so the possible duplicate is maybe the first hint, on which one can go on — swe, Sep 29 '16 at 18:15
maybe [this](http://stackoverflow.com/questions/13440992/sql-random-aggregate) question has some hints for you. what you need is to group by the date and select a random row for each group, the linked question needs exacly the same. one of the answers should run in mssql as well. so, possible duplicate: http://stackoverflow.com/questions/13440992/sql-random-aggregate — swe, Sep 29 '16 at 18:19

Andrey Morozov · Answer 1 · 2016-09-29T18:33:52.453

Try this:

-- test data
create table your_table (staffid int, [date] date);
insert into your_table values
(1,        '2016-01-01'),
(1,        '2016-01-02'),
(1,        '2016-01-03'),
(2,        '2016-01-03'),
(3,        '2016-01-01'),
(3,        '2016-01-03');

-- query
select *
from (
    select distinct [date] [distinct_date] from your_table
) as d
outer apply (
    select top 1 staffid
    from your_table
    where d.[distinct_date] = [date]
    order by newid()
) as x

-- result 1
distinct_date   staffid
-----------------------
2016-01-01      3
2016-01-02      1
2016-01-03      1

-- result 2
distinct_date   staffid
-----------------------
2016-01-01      1
2016-01-02      1
2016-01-03      2

hope it helps :)

How to apply randomly selected values to distinct dates in SQL Server

1 Answers1