Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)
Guest

Random

By: a guest on Aug 21st, 2013  |  syntax: None  |  size: 1.78 KB  |  hits: 31  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. DECLARE @Hari TABLE(
  2.         ID Int,
  3.         Hari VARCHAR(10)
  4. )
  5.  
  6. DECLARE @Jam TABLE(
  7.         ID Int,
  8.         Jam VARCHAR(10)
  9. )
  10.  
  11. DECLARE @Ruangan TABLE(
  12.         ID INT,
  13.         Ruangan VARCHAR(2)
  14. )
  15.  
  16. DECLARE @Dosen TABLE(
  17.         ID INT,
  18.         Dosen VARCHAR(10),
  19.         Matkul VARCHAR(10),
  20.         Semester CHAR(1)
  21.        
  22. )
  23.  
  24.  
  25. INSERT INTO @Hari SELECT 1, 'Senin'
  26. INSERT INTO @Hari SELECT 2, 'Selasa'
  27. INSERT INTO @Hari SELECT 3, 'Rabu'
  28. INSERT INTO @Hari SELECT 4, 'Kamis'
  29. INSERT INTO @Hari SELECT 5, 'Jumat'
  30. INSERT INTO @Hari SELECT 6, 'Sabtu'
  31.  
  32. INSERT INTO @Jam SELECT 1, '08.00'
  33. INSERT INTO @Jam SELECT 2, '09.00'
  34. INSERT INTO @Jam SELECT 3, '10.00'
  35. INSERT INTO @Jam SELECT 4, '11.00'
  36. INSERT INTO @Jam SELECT 5, '12.00'
  37. INSERT INTO @Jam SELECT 6, '12.00'
  38.  
  39. INSERT INTO @Ruangan SELECT 1, 'B1'
  40. INSERT INTO @Ruangan SELECT 2, 'B2'
  41. INSERT INTO @Ruangan SELECT 3, 'B3'
  42. INSERT INTO @Ruangan SELECT 4, 'B4'
  43. INSERT INTO @Ruangan SELECT 5, 'B5'
  44. INSERT INTO @Ruangan SELECT 6, 'B6'
  45.  
  46. INSERT INTO @Dosen SELECT 1, 'Acong1', 'Matkul1', '2'
  47. INSERT INTO @Dosen SELECT 2, 'Acong2', 'Matkul2', '3'
  48. INSERT INTO @Dosen SELECT 3, 'Acong3', 'Matkul3', '2'
  49. INSERT INTO @Dosen SELECT 4, 'Acong4', 'Matkul4', '3'
  50. INSERT INTO @Dosen SELECT 5, 'Acong5', 'Matkul5', '2'
  51. INSERT INTO @Dosen SELECT 6, 'Acong6', 'Matkul6', '2'
  52.  
  53. ;WITH RandomSchedule AS (
  54.         SELECT  d.id DosenID, d.Dosen, d.Matkul, d.Semester,                           
  55.                 r.Ruangan,
  56.                 h.Hari,
  57.                 j.Jam,
  58.                 ROW_NUMBER() OVER(PARTITION BY d.ID ORDER BY NEWID()) RowNumber
  59.         FROM    @Dosen d,
  60.                 @Ruangan r,
  61.                 @Hari h,
  62.                 @Jam j
  63. )
  64. SELECT  RS.*, ('Pilihan ' + CAST(RS.RowNumber AS VARCHAR)) AS Pilihan
  65. FROM    RandomSchedule RS
  66. WHERE   RowNumber