[緊急！]SQLで当番表作成

初めまして。
最近、SQLを学び始めました。

会社で掃除当番表を作成することになったのですが、どのようにしたらいいのかがわかりません。

条件としては、
１．人数は全部で３１人。
２．１度にランダムに５人ずつ当番になる。
３．１度当番になると、全員の当番が一巡するまで回ってこない。
４．SQLServer2012を使用し、プログラミング後はAccessのフォームに出力してフォームで確認可能。
５．ランダムに選ばれた当番表を履歴テーブルに残す。

の５点になります。
本やネットで調べてみたのですがうまくいきません。
ご教授をお願い致します。

No.4 回答者： innoya 回答日時： 2014/05/15 15:21

こんにちは。

イノ(inno)です。

方法はいろいろありますけど、
上記の条件のにあうSQLを作ってみました。
下記のSQLをそのまま実行してみてください。
先ずは、テーブルを作成してください。

--#################################
--#### テーブル作成
--#################################
--#### ユーザーテーブル
CREATE TABLE dbo.TempUser
(
Sn int
, UserName nvarchar(100)
, IsCheck bit
)

--#### 当番履歴テーブル
CREATE TABLE dbo.TempUserLog
(
Sn int
, UserName nvarchar(100)
, RegDate Datetime
)


ここまでがテーブル作成です。
それでは31人のユーザーデータを登録しましょう。

--#################################
--#### データ登録
--#################################
DECLARE @i int
SET @i = 1

WHILE(@i <= 31)
BEGIN

INSERT INTO dbo.TempUser (Sn, UserName, IsCheck)
VALUES (@i, CONVERT(varchar(10), @i) + 'さん', 0)
SET @i = @i + 1
END


ここまでがユーザー登録です。
では、下記のSQLを実行すると当番を計算して結果がでるようになっています。
履歴も残しています。

--#################################
--#### 当番計算
--#### １度にランダムに５人ずつ当番になる。
--#### １度当番になると、全員の当番が一巡するまで回ってこない。
--#################################
DECLARE @TempSelectUser TABLE
(
Sn int
,UserName nvarchar(100)
)

DECLARE @Sn int
DECLARE @UserName nvarchar(100)

DECLARE DB_Cursor CURSOR FOR
SELECT TOP 5 Sn, UserName
FROM
(
SELECT TOP 5 1 Code, Sn, UserName FROM dbo.TempUser WHERE IsCheck = 0 ORDER BY NEWID()
UNION ALL
SELECT TOP 5 2 Code, Sn, UserName FROM dbo.TempUser ORDER BY NEWID()
) A
ORDER BY Code
OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor
INTO @Sn, @UserName
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @TempSelectUser
SELECT @Sn, @UserName

IF 0 = (SELECT COUNT(Sn) FROM dbo.TempUser WHERE IsCheck = 0)
BEGIN
UPDATE dbo.TempUser
SET IsCheck = 0
END

UPDATE dbo.TempUser
SET IsCheck = 1
WHERE Sn = @Sn

INSERT INTO dbo.TempUserLog
SELECT @Sn, @UserName, GETDATE()

FETCH NEXT FROM DB_Cursor
INTO @Sn, @UserName

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor


--#################################
--#### 当番結果
--#################################
SELECT * FROM @TempSelectUser


ここまで実行してみてください。
上記のSQLを実行するたびに当番を計算して結果を表示します。


履歴を見るには下記のSQLを実行してご確認ください。

SELECT * FROM dbo.TempUserLog
ORDER BY RegDate DESC

どうですか。
正常に実行されましたか。

No.3 回答者： maiko0318 回答日時： 2014/05/15 12:23

db2 connect to sample

db2 "drop table hoge"
db2 "CREATE TABLE hoge (id char(2), cnt int, date char(10))"

db2 "INSERT INTO hoge VALUES ('01',0,''),('02',0,''),('03',0,''),('04',0,''),('05',0,''),('06',0,''),('07',0,''),('08',0,''),('09',0,''),('10',0,''),('11',0,''),('12',0,''),('13',0,''),('14',0,''),('15',0,''),('16',0,''),('17',0,''),('18',0,''),('19',0,''),('20',0,''),('21',0,''),('22',0,''),('23',0,''),('24',0,''),('25',0,''),('26',0,''),('27',0,''),('28',0,''),('29',0,''),('30',0,''),('31',0,'')"

db2 "update hoge set cnt=cnt+1, date='%date:~0,10%' where id in (select id from (select id,int(RAND(100) * 65535) from hoge, SYSIBM.SYSDUMMY1 order by cnt, 2 limit 5))"

db2 "select * from hoge order by id"

DB2 で作ってみました。
結果はhogeテーブルに入っていますので、Accessなりでみてください。

No.2 回答者： Siegrune 回答日時： 2014/05/15 02:29

さて、まず、当番の割り当ては、T-SQLを使うのですか？Accessでやってしまいますか？

（バッチ処理っぽくやるのか、画面を上げて当番を割り当てるのか？という聞き方と同じような話ですが）

どうやりたいのか書いてみてください。

どうできるのか？
と逆に聞かれるのなら、

Accessの画面を立ち上げてボタンを押すと何日分（？何か月分？）かの割り当てが自動で
行われる。
というのがいいのか、勝手に（というよりは自動的に）
割り当てを行ったものを参照するのがいいのか？
割り当てた後に変更はできないのか？
とか、要件がわからないとちょっと、回答に困ってしまうのが現実ですが。。。

No.1 回答者： kamikami30 回答日時： 2014/05/14 23:16

実際に本やネットで調べてみてよくわからないところを具体的に書いてください。