DECLARE @Hari TABLE(
ID Int,
Hari VARCHAR(10)
)
DECLARE @Jam TABLE(
ID Int,
Jam VARCHAR(10)
)
DECLARE @Ruangan TABLE(
ID INT,
Ruangan VARCHAR(2)
)
DECLARE @Dosen TABLE(
ID INT,
Dosen VARCHAR(10),
Matkul VARCHAR(10),
Semester CHAR(1)
)
INSERT INTO @Hari SELECT 1, 'Senin'
INSERT INTO @Hari SELECT 2, 'Selasa'
INSERT INTO @Hari SELECT 3, 'Rabu'
INSERT INTO @Hari SELECT 4, 'Kamis'
INSERT INTO @Hari SELECT 5, 'Jumat'
INSERT INTO @Hari SELECT 6, 'Sabtu'
INSERT INTO @Jam SELECT 1, '08.00'
INSERT INTO @Jam SELECT 2, '09.00'
INSERT INTO @Jam SELECT 3, '10.00'
INSERT INTO @Jam SELECT 4, '11.00'
INSERT INTO @Jam SELECT 5, '12.00'
INSERT INTO @Jam SELECT 6, '12.00'
INSERT INTO @Ruangan SELECT 1, 'B1'
INSERT INTO @Ruangan SELECT 2, 'B2'
INSERT INTO @Ruangan SELECT 3, 'B3'
INSERT INTO @Ruangan SELECT 4, 'B4'
INSERT INTO @Ruangan SELECT 5, 'B5'
INSERT INTO @Ruangan SELECT 6, 'B6'
INSERT INTO @Dosen SELECT 1, 'Acong1', 'Matkul1', '2'
INSERT INTO @Dosen SELECT 2, 'Acong2', 'Matkul2', '3'
INSERT INTO @Dosen SELECT 3, 'Acong3', 'Matkul3', '2'
INSERT INTO @Dosen SELECT 4, 'Acong4', 'Matkul4', '3'
INSERT INTO @Dosen SELECT 5, 'Acong5', 'Matkul5', '2'
INSERT INTO @Dosen SELECT 6, 'Acong6', 'Matkul6', '2'
;WITH RandomSchedule AS (
SELECT d.id DosenID, d.Dosen, d.Matkul, d.Semester,
r.Ruangan,
h.Hari,
j.Jam,
ROW_NUMBER() OVER(PARTITION BY d.ID ORDER BY NEWID()) RowNumber
FROM @Dosen d,
@Ruangan r,
@Hari h,
@Jam j
)
SELECT RS.*, ('Pilihan ' + CAST(RS.RowNumber AS VARCHAR)) AS Pilihan
FROM RandomSchedule RS
WHERE RowNumber