MS-SQL / SQL Server
日別接続時間取得 - ログイン時間、ログアウト時間を利用して取得
ログインログを利用して日別接続時間を取得する方法について説明したいと思います。
個人的には出席ログともいいます。
ログインログで日別の接続時間（プレイ時間）を抽出してその日ログインしていたかを判別するデータを作成したいと思います。
必要なテーブルは下記のとおりです。
-.dbo.LoginLog : ログインログテーブル
-.dbo.AttendInfo : 日別接続時間テーブル
-.dbo.tComDay : 日付テーブル
SPリストは下記のとおりです。
-.dbo.pAddLoginLog : ログインログデータを登録
-.dbo.pAddAttendInfo : 日別接続時間を集計
では、テーブルから作成してみましょう。
テーブル作成スクリプト
-- #### ログインログテーブル作成
CREATE TABLE [dbo].[LoginLog](
[UserID] [varchar](20) NULL,
[LoginDate] [datetime] NULL,
[LogoutDate] [datetime] NULL
) ON [PRIMARY]
GO
-- #### 日別接続時間テーブル作成
CREATE TABLE [dbo].[AttendInfo](
[LogDate] [date] NULL,
[UserID] [varchar](20) NULL,
[PlayTime] [smallint] NULL
) ON [PRIMARY]
GO
-- #### 日付テーブル作成
CREATE TABLE [dbo].[tComDay](
[Idx] [int] NOT NULL,
[ComDay] [datetime] NOT NULL,
CONSTRAINT [PK_tComDay] PRIMARY KEY CLUSTERED
(
[ComDay] ASC
)
) ON [PRIMARY]
GO
-- #### 日付データを登録
DECLARE @i int
SET @i = 1
WHILE @i <= 36525
BEGIN
INSERT INTO [dbo].[tComDay]
SELECT @i, DATEADD(DD, @i, '1999-12-31')
SET @i = @i + 1
END
次はSPを作成してみましょう。
「dbo.pAddLoginLog」SP作成スクリプト
CREATE PROCEDURE [dbo].[pAddLoginLog]
@UserID varchar(20)
,@LoginDate datetime
,@LogoutDate datetime
AS
BEGIN
SET NOCOUNT ON
-- ログインログ登録
INSERT INTO dbo.LoginLog (UserID, LoginDate, LogoutDate)
Values (@UserID, @LoginDate, @LogoutDate)
-- 日別プレイ時間登録
EXEC dbo.pAddAttendInfo @UserID, @LoginDate, @LogoutDate
END
「dbo.pAddAttendInfo」SP作成スクリプト
CREATE PROCEDURE [dbo].[pAddAttendInfo]
@UserID varchar(20)
, @LoginDate datetime
, @LogoutDate datetime
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.AttendInfo Set PlayTime = A.PlayTime + B.PlayTime
FROM dbo.AttendInfo A WITH (NOLOCK) JOIN
(
SELECT ComDay, DATEDIFF( mi, StartTime, EndTime) PlayTime, StartTime, EndTime
FROM
(
SELECT ComDay, DATEADD(dd, 1, ComDay) EndDay
,CASE WHEN ComDay <= @LoginDate AND DATEADD(dd, 1, ComDay) > @LoginDate THEN @LoginDate ELSE ComDay END StartTime
,CASE WHEN ComDay <= @LogoutDate AND DATEADD(dd, 1, ComDay) > @LogoutDate THEN @LogoutDate ELSE DATEADD(dd, 1, ComDay) END EndTime
FROM dbo.tComDay B WITH (NOLOCK)
WHERE ComDay BETWEEN CONVERT(CHAR(10), @LoginDate, 120) AND CONVERT(CHAR(10), @LogoutDate, 120)
) A
) B
ON A.LogDate = B.ComDay
WHERE A.UserID = @UserID
INSERT INTO dbo.AttendInfo ( LogDate, UserID, PlayTime )
SELECT ComDay, @UserID, PlayTime
FROM
(
SELECT ComDay, DATEDIFF( mi, StartTime, EndTime) PlayTime, StartTime, EndTime
FROM
(
SELECT ComDay, DATEADD(dd, 1, ComDay) EndDay
,CASE WHEN ComDay <= @LoginDate AND DATEADD(dd, 1, ComDay) > @LoginDate THEN @LoginDate ELSE ComDay END StartTime
,CASE WHEN ComDay <= @LogoutDate AND DATEADD(dd, 1, ComDay) > @LogoutDate THEN @LogoutDate ELSE DATEADD(dd, 1, ComDay) END EndTime
FROM dbo.tComDay B WITH (NOLOCK)
WHERE ComDay BETWEEN CONVERT(CHAR(10), @LoginDate, 120) AND CONVERT(CHAR(10), @LogoutDate, 120)
) A
) A
WHERE NOT EXISTS ( SELECT TOP 1 1 FROM dbo.AttendInfo B WITH (NOLOCK) WHERE B.UserID = @UserID AND B.LogDate = A.ComDay)
END
「dbo.pAddAttendInfo」SPは「dbo.pAddLoginLog」SPの中に踏み込まれているので、
実際に使うのは「dbo.pAddLoginLog」のみになります。
「dbo.pAddLoginLog」SPの使用例
EXEC dbo.pAddLoginLog 'UserID', 'ログイン時刻', 'ログアウト時刻'
では、一つ一つ実行しながらデータを確認してみよう。
テストSQL
EXEC dbo.pAddLoginLog 'test_user_01', '2014-07-01 09:00:00', '2014-07-01 09:10:00'
--#### データ確認
SELECT * FROM dbo.LoginLog
実行結果
UserID LoginDate LogoutDate
-------------------- ----------------------- -----------------------
test_user_01 2014-07-01 09:00:00.000 2014-07-01 09:10:00.000
「test_user_01」IDのユーザーのログインログが登録されています。
では、「dbo.AttendInfo」テーブルの接続時間（プレイ時間）を確認してみましょう。
--#### データ確認
SELECT * FROM dbo.AttendInfo
実行結果
LogDate UserID PlayTime
---------- -------------------- --------
2014-07-01 test_user_01 10
ログイン時間とログアウト時間をみると9時から9時10分まで接続しているので、
接続時間は10分になります。
では、次のログを登録してみましょう。
EXEC dbo.pAddLoginLog 'test_user_01', '2014-07-01 09:10:00', '2014-07-01 09:25:00'
ログインログを確認してみましょう。
--#### データ確認
SELECT * FROM dbo.LoginLog
実行結果
UserID LoginDate LogoutDate
-------------------- ----------------------- -----------------------
test_user_01 2014-07-01 09:00:00.000 2014-07-01 09:10:00.000
test_user_01 2014-07-01 09:10:00.000 2014-07-01 09:25:00.000
データが登録されていることが確認できます。
では、接続時間を確認してみましょう。
--#### データ確認
SELECT * FROM dbo.AttendInfo
実行結果
LogDate UserID PlayTime
---------- -------------------- --------
2014-07-01 test_user_01 25
「dbo.AttendInfo」テーブルのデータはIDごとに1日当たり一つのログしか残りません。
1回目の接続時間10分に、2回目は10分から25分までログインしているので、接続時間は15分になり、
合計25分ログインしたとログが残ります。
では、3回目はログイン時間を日を跨いで設定してみましょう。
EXEC dbo.pAddLoginLog 'test_user_01', '2014-07-01 23:50:00', '2014-07-02 00:10:00'
--#### データ確認
SELECT * FROM dbo.LoginLog
実行結果
UserID LoginDate LogoutDate
-------------------- ----------------------- -----------------------
test_user_01 2014-07-01 09:00:00.000 2014-07-01 09:10:00.000
test_user_01 2014-07-01 09:10:00.000 2014-07-01 09:25:00.000
test_user_01 2014-07-01 23:50:00.000 2014-07-02 00:10:00.000
データが登録されていることが確認できます。
では、接続時間を確認してみましょう。
--#### データ確認
SELECT * FROM dbo.AttendInfo
実行結果
LogDate UserID PlayTime
---------- -------------------- --------
2014-07-01 test_user_01 35
2014-07-02 test_user_01 10
3回目のログインでは2014-07-01に10分ログイン！
2014-07-02に10分ログインしていますので、
上記の実行結果のようになります。
では、4回目では2日ログインしていたと設定してみましょう。
EXEC dbo.pAddLoginLog 'test_user_01', '2014-07-02 00:10:00', '2014-07-04 00:10:00'
--#### データ確認
SELECT * FROM dbo.LoginLog
実行結果
UserID LoginDate LogoutDate
-------------------- ----------------------- -----------------------
test_user_01 2014-07-01 09:00:00.000 2014-07-01 09:10:00.000
test_user_01 2014-07-01 09:10:00.000 2014-07-01 09:25:00.000
test_user_01 2014-07-01 23:50:00.000 2014-07-02 00:10:00.000
test_user_01 2014-07-02 00:10:00.000 2014-07-04 00:10:00.000
データが登録されていることが確認できます。
では、接続時間を確認してみましょう。
--#### データ確認
SELECT * FROM dbo.AttendInfo
実行結果
LogDate UserID PlayTime
---------- -------------------- --------
2014-07-01 test_user_01 35
2014-07-02 test_user_01 1440
2014-07-03 test_user_01 1440
2014-07-04 test_user_01 10
このように接続時間が日別に分かれてそれぞれ入っています。
ですので、ログインログで上記のようにその日にログインしていたのか判別と
どのぐらいログインしていたのかが分かることができます。