SQLで当番表作成

coconan
2014-05-26 12:42 1548 1
会社で掃除当番表を作成することになったのですが、どのようにしたらいいのかがわかりません。

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

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

コメント

2014年 05月 27日 (12:07)
こんにちは。
イノ(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を実行すると当番を計算して結果がでるようになっています。
履歴も残しています。

--#################################
--#### 当番計算
--#### 1度にランダムに5人ずつ当番になる。
--#### 1度当番になると、全員の当番が一巡するまで回ってこない。
--#################################
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

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