SQL Tips

 

[SQL Server] 日別接続時間取得 - ログイン時間、ログアウト時間を利用して取得

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

このように接続時間が日別に分かれてそれぞれ入っています。

ですので、ログインログで上記のようにその日にログインしていたのか判別と

どのぐらいログインしていたのかが分かることができます。
このエントリーをはてなブックマークに追加
2014-10-23 15:14:52   2004

コメント

[SQL Server] Transact-SQLを使ってSQL Server ユーザーを作成する方法

-.日付:2015-01-16   カテゴリ : -   閲覧数:1453

[SQL Server] SET IDENTITY_INSERT で IDENTITY を設定した列に値を登録する方法

-.日付:2014-11-30   カテゴリ : -   閲覧数:3325

[SQL Server] 月末の日付(月末日)を求めたい / EOMONTH関数 / 月末日を取得

-.日付:2014-11-23   カテゴリ : -   閲覧数:12132

[SQL Server] LIKEを利用して検索する時パフォーマンスに注意事項

-.日付:2014-11-22   カテゴリ : -   閲覧数:4950

[SQL Server] IPアドレスを数字に変更する方法

-.日付:2014-10-26   カテゴリ : -   閲覧数:1718

[SQL Server] GeoIPを利用してIPアドレスの国情報を取得する方法

-.日付:2014-10-25   カテゴリ : -   閲覧数:6211

Copyright © 2015 INNOYA.COM All rights reserved. RSS