November 17, 2011 at 4:48 am
Hi All,
I have one requirement , in that i need to provide the total users in the session the data as like below
UsersSession StartSession End
User 110/10/2011 2:11:0010/10/2011 2:19:00
User 210/10/2011 2:15:0010/10/2011 2:17:00
User 310/10/2011 2:19:0010/10/2011 2:21:00
User 410/10/2011 2:23:0010/10/2011 2:46:00
User 510/10/2011 2:21:0010/10/2011 2:25:00
User 610/10/2011 2:26:0010/10/2011 2:45:00
User 710/10/2011 2:27:0010/10/2011 3:03:00
User 810/10/2011 2:28:0010/10/2011 3:05:00
User 910/10/2011 2:47:0010/10/2011 2:49:00
User 1010/10/2011 2:50:0510/10/2011 2:59:00
User 110/10/2011 3:11:0010/10/2011 4:19:00
User 210/10/2011 3:15:0010/10/2011 3:17:00
User 310/10/2011 3:19:0010/10/2011 3:20:00
User 410/10/2011 3:02:0010/10/2011 3:10:00
User 510/10/2011 3:21:0010/10/2011 3:25:00
User 610/10/2011 3:26:0010/10/2011 3:48:00
User 710/10/2011 3:27:0010/10/2011 4:03:00
User 810/10/2011 3:28:0010/10/2011 4:35:00
User 910/10/2011 3:11:0010/10/2011 3:13:00
User 1010/10/2011 3:47:0010/10/2011 3:59:00
output
SLNoDateTimeTotalPeak
110/10/20112:00:00104
210/10/20113:00:00125
-- Sample Data
Declare @SessionDatas table(
[Users] [nvarchar](255) NULL,
[Session Start] [datetime] NULL,
[Session End] [datetime] NULL
)
INSERT @SessionDatas ([Users], [Session Start], [Session End]) VALUES
(N'User 1', CAST(0x00009F780023FAF0 AS DateTime), CAST(0x00009F7800262D70 AS DateTime))
,(N'User 2', CAST(0x00009F7800251430 AS DateTime), CAST(0x00009F780025A0D0 AS DateTime))
, (N'User 3', CAST(0x00009F7800262D70 AS DateTime), CAST(0x00009F780026BA10 AS DateTime))
, (N'User 4', CAST(0x00009F78002746B0 AS DateTime), CAST(0x00009F78002D97E0 AS DateTime))
, (N'User 5', CAST(0x00009F780026BA10 AS DateTime), CAST(0x00009F780027D350 AS DateTime))
, (N'User 6', CAST(0x00009F78002819A0 AS DateTime), CAST(0x00009F78002D5190 AS DateTime))
, (N'User 7', CAST(0x00009F7800285FF0 AS DateTime), CAST(0x00009F7800324330 AS DateTime))
, (N'User 8', CAST(0x00009F780028A640 AS DateTime), CAST(0x00009F780032CFD0 AS DateTime))
, (N'User 9', CAST(0x00009F78002DDE30 AS DateTime), CAST(0x00009F78002E6AD0 AS DateTime))
, (N'User 10', CAST(0x00009F78002EB6FC AS DateTime), CAST(0x00009F78003129F0 AS DateTime))
, (N'User 1', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F78004722F0 AS DateTime))
, (N'User 2', CAST(0x00009F7800358EF0 AS DateTime), CAST(0x00009F7800361B90 AS DateTime))
, (N'User 3', CAST(0x00009F780036A830 AS DateTime), CAST(0x00009F780036EE80 AS DateTime))
, (N'User 4', CAST(0x00009F780031FCE0 AS DateTime), CAST(0x00009F7800342F60 AS DateTime))
, (N'User 5', CAST(0x00009F78003734D0 AS DateTime), CAST(0x00009F7800384E10 AS DateTime))
, (N'User 6', CAST(0x00009F7800389460 AS DateTime), CAST(0x00009F78003E9F40 AS DateTime))
, (N'User 7', CAST(0x00009F780038DAB0 AS DateTime), CAST(0x00009F780042BDF0 AS DateTime))
, (N'User 8', CAST(0x00009F7800392100 AS DateTime), CAST(0x00009F78004B87F0 AS DateTime))
, (N'User 9', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F7800350250 AS DateTime))
, (N'User 10', CAST(0x00009F78003E58F0 AS DateTime), CAST(0x00009F780041A4B0 AS DateTime))
select * from @SessionDatas
-- OutPut
Select 1 SLNo,'10/10/2011' [Date], '2:00:00' as [Time], 10 [Total] , 4 Peak union
Select 2 SLNo,'10/10/2011' [Date], '3:00:00' as [Time], 12 [Total] , 5 Peak
Thanks & Regards
Deepak .A
November 17, 2011 at 5:29 am
I don't understand how you're working out your "Peak". Explain that part and I'll edit the script. Also, I think you've missed out the 3 records that bleed into 04:00:00.000.
For now, I've only included the "total". I've used a calendar table CTE, for performance you will want a physical table.
Declare @SessionDatas table(
[Users] [nvarchar](255) NULL,
[Session Start] [datetime] NULL,
[Session End] [datetime] NULL
)
INSERT @SessionDatas ([Users], [Session Start], [Session End]) VALUES
(N'User 1', CAST(0x00009F780023FAF0 AS DateTime), CAST(0x00009F7800262D70 AS DateTime))
,(N'User 2', CAST(0x00009F7800251430 AS DateTime), CAST(0x00009F780025A0D0 AS DateTime))
, (N'User 3', CAST(0x00009F7800262D70 AS DateTime), CAST(0x00009F780026BA10 AS DateTime))
, (N'User 4', CAST(0x00009F78002746B0 AS DateTime), CAST(0x00009F78002D97E0 AS DateTime))
, (N'User 5', CAST(0x00009F780026BA10 AS DateTime), CAST(0x00009F780027D350 AS DateTime))
, (N'User 6', CAST(0x00009F78002819A0 AS DateTime), CAST(0x00009F78002D5190 AS DateTime))
, (N'User 7', CAST(0x00009F7800285FF0 AS DateTime), CAST(0x00009F7800324330 AS DateTime))
, (N'User 8', CAST(0x00009F780028A640 AS DateTime), CAST(0x00009F780032CFD0 AS DateTime))
, (N'User 9', CAST(0x00009F78002DDE30 AS DateTime), CAST(0x00009F78002E6AD0 AS DateTime))
, (N'User 10', CAST(0x00009F78002EB6FC AS DateTime), CAST(0x00009F78003129F0 AS DateTime))
, (N'User 1', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F78004722F0 AS DateTime))
, (N'User 2', CAST(0x00009F7800358EF0 AS DateTime), CAST(0x00009F7800361B90 AS DateTime))
, (N'User 3', CAST(0x00009F780036A830 AS DateTime), CAST(0x00009F780036EE80 AS DateTime))
, (N'User 4', CAST(0x00009F780031FCE0 AS DateTime), CAST(0x00009F7800342F60 AS DateTime))
, (N'User 5', CAST(0x00009F78003734D0 AS DateTime), CAST(0x00009F7800384E10 AS DateTime))
, (N'User 6', CAST(0x00009F7800389460 AS DateTime), CAST(0x00009F78003E9F40 AS DateTime))
, (N'User 7', CAST(0x00009F780038DAB0 AS DateTime), CAST(0x00009F780042BDF0 AS DateTime))
, (N'User 8', CAST(0x00009F7800392100 AS DateTime), CAST(0x00009F78004B87F0 AS DateTime))
, (N'User 9', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F7800350250 AS DateTime))
, (N'User 10', CAST(0x00009F78003E58F0 AS DateTime), CAST(0x00009F780041A4B0 AS DateTime))
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
tally AS (SELECT
DATEADD(mi,60*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))),'2011-10-01 00:00:00') AS n,
DATEADD(mi,60*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1),'2011-10-01 00:00:00') AS n2
FROM t4 x, t4 y)
SELECT ROW_NUMBER() OVER (ORDER BY n ASC) AS SLNo, n AS [Date Time], SUM([User Count]) AS Total
FROM tally a
OUTER APPLY (SELECT 1 AS [User Count], [Users], [Session Start], [Session End]
FROM @SessionDatas
WHERE ([Session Start] >= n AND [Session Start] <= n2)
OR ([Session End] >= n AND [Session End] <= n2)) b
WHERE [User Count] IS NOT NULL
GROUP BY n
This returns
SLNo Date Time Total
-------------------- ----------------------- -----------
1 2011-10-10 02:00:00.000 10
2 2011-10-10 03:00:00.000 12
3 2011-10-10 04:00:00.000 3
November 17, 2011 at 6:00 am
im expecting the below output
SLNo Date Time Total Peak
----------- ---------- ------- ----------- -----------
1 10/10/2011 2:00:00 10 4
2 10/10/2011 3:00:00 12 5
Peak Calculation is , how many users simultaneously in an hour. It will gives the concurrent users in the session in an hour. In simple it is a overlapping of user in an hour.
Regards - Deepak
November 17, 2011 at 6:02 am
Cadavre (11/17/2011)
SLNo Date Time Total-------------------- ----------------------- -----------
1 2011-10-10 02:00:00.000 10
2 2011-10-10 03:00:00.000 12
3 2011-10-10 04:00:00.000 3
Total is correct i need the peak count also
November 17, 2011 at 6:12 am
Cadavre (11/17/2011)
I don't understand how you're working out your "Peak". Explain that part and I'll edit the script. Also, I think you've missed out the 3 records that bleed into 04:00:00.000.
Peak Calculation is , how many users simultaneously in an hour. It will gives the concurrent users in the session in an hour. In simple it is a overlapping of user in an hour.
Please find the attachment for more details.
November 17, 2011 at 6:24 am
I am not sure, but as far as I understand after looking at your comments and data, this could be what you are after
Declare @SessionDatas table(
[Users] [nvarchar](255) NULL,
[Session Start] [datetime] NULL,
[Session End] [datetime] NULL
)
INSERT @SessionDatas ([Users], [Session Start], [Session End])
select N'User 1', CAST(0x00009F780023FAF0 AS DateTime), CAST(0x00009F7800262D70 AS DateTime) UNION ALL
select N'User 2', CAST(0x00009F7800251430 AS DateTime), CAST(0x00009F780025A0D0 AS DateTime) UNION ALL
select N'User 3', CAST(0x00009F7800262D70 AS DateTime), CAST(0x00009F780026BA10 AS DateTime) UNION ALL
select N'User 4', CAST(0x00009F78002746B0 AS DateTime), CAST(0x00009F78002D97E0 AS DateTime) UNION ALL
select N'User 5', CAST(0x00009F780026BA10 AS DateTime), CAST(0x00009F780027D350 AS DateTime) UNION ALL
select N'User 6', CAST(0x00009F78002819A0 AS DateTime), CAST(0x00009F78002D5190 AS DateTime) UNION ALL
select N'User 7', CAST(0x00009F7800285FF0 AS DateTime), CAST(0x00009F7800324330 AS DateTime) UNION ALL
select N'User 8', CAST(0x00009F780028A640 AS DateTime), CAST(0x00009F780032CFD0 AS DateTime) UNION ALL
select N'User 9', CAST(0x00009F78002DDE30 AS DateTime), CAST(0x00009F78002E6AD0 AS DateTime) UNION ALL
select N'User 10', CAST(0x00009F78002EB6FC AS DateTime), CAST(0x00009F78003129F0 AS DateTime) UNION ALL
select N'User 1', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F78004722F0 AS DateTime) UNION ALL
select N'User 2', CAST(0x00009F7800358EF0 AS DateTime), CAST(0x00009F7800361B90 AS DateTime) UNION ALL
select N'User 3', CAST(0x00009F780036A830 AS DateTime), CAST(0x00009F780036EE80 AS DateTime) UNION ALL
select N'User 4', CAST(0x00009F780031FCE0 AS DateTime), CAST(0x00009F7800342F60 AS DateTime) UNION ALL
select N'User 5', CAST(0x00009F78003734D0 AS DateTime), CAST(0x00009F7800384E10 AS DateTime) UNION ALL
select N'User 6', CAST(0x00009F7800389460 AS DateTime), CAST(0x00009F78003E9F40 AS DateTime) UNION ALL
select N'User 7', CAST(0x00009F780038DAB0 AS DateTime), CAST(0x00009F780042BDF0 AS DateTime) UNION ALL
select N'User 8', CAST(0x00009F7800392100 AS DateTime), CAST(0x00009F78004B87F0 AS DateTime) UNION ALL
select N'User 9', CAST(0x00009F78003475B0 AS DateTime), CAST(0x00009F7800350250 AS DateTime) UNION ALL
select N'User 10', CAST(0x00009F78003E58F0 AS DateTime), CAST(0x00009F780041A4B0 AS DateTime)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) SLNO,
DATEADD(hour, DATEDIFF(hour,0,[Session Start]),0) AS DATE,
COUNT([Users]) AS Total,
COUNT(DISTINCT [Users]) Peak
FROM @SessionDatas AS SD
GROUP BY DATEADD(hour, DATEDIFF(hour,0,[Session Start]),0)
November 17, 2011 at 6:26 am
Sorry, after looking at the image, this is not what required. Please discard it.
November 17, 2011 at 6:39 am
I think you are going to have to calculate the hour and minute values separately and then combine them.
You will probably have to use both a Calendar and a tally table; the following should get you started although it just hard codes the date and uses spt_values for the tally:
WITH MinuteRange
AS
(
SELECT DATEADD(minute, number, '20111010') AS sessionMinute
FROM master.dbo.spt_values
WHERE [type] = 'P'
AND number < 24 * 60
)
, MinuteCounts
AS
(
SELECT M.sessionMinute, COUNT(*) MinuteCount
FROM MinuteRange M
JOIN @SessionDatas D
ON M.sessionMinute BETWEEN D.[Session Start] AND D.[Session End]
GROUP BY M.sessionMinute
)
, HourPeaks
AS
(
SELECT DATEADD(hour, DATEDIFF(hour, 0, sessionMinute), 0) AS HourStart
,MAX(MinuteCount) AS Peak
FROM MinuteCounts
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, sessionMinute), 0)
)
, HourRange
AS
(
SELECT DATEADD(hour, number, '20111010') AS HourStart
,DATEADD(hour, number + 1, '20111010') AS HourEnd
FROM master.dbo.spt_values
WHERE [type] = 'P'
AND number < 24
)
, HourCounts
AS
(
SELECT H.HourStart, COUNT(*) AS HourCount
FROM HourRange H
JOIN @SessionDatas D
ON H.HourEnd > D.[Session Start]
AND H.HourStart <= D.[Session End]
GROUP BY H.HourStart
)
SELECT *
FROM HourCounts C
JOIN HourPeaks P
ON C.HourStart = P.HourStart;
November 18, 2011 at 12:04 am
Ken McKelvey (11/17/2011)
I think you are going to have to calculate the hour and minute values separately and then combine them.You will probably have to use both a Calendar and a tally table; the following should get you started although it just hard codes the date and uses spt_values for the tally:
WITH MinuteRange
AS
(
SELECT DATEADD(minute, number, '20111010') AS sessionMinute
FROM master.dbo.spt_values
WHERE [type] = 'P'
AND number < 24 * 60
)
, MinuteCounts
AS
(
SELECT M.sessionMinute, COUNT(*) MinuteCount
FROM MinuteRange M
JOIN @SessionDatas D
ON M.sessionMinute BETWEEN D.[Session Start] AND D.[Session End]
GROUP BY M.sessionMinute
)
, HourPeaks
AS
(
SELECT DATEADD(hour, DATEDIFF(hour, 0, sessionMinute), 0) AS HourStart
,MAX(MinuteCount) AS Peak
FROM MinuteCounts
GROUP BY DATEADD(hour, DATEDIFF(hour, 0, sessionMinute), 0)
)
, HourRange
AS
(
SELECT DATEADD(hour, number, '20111010') AS HourStart
,DATEADD(hour, number + 1, '20111010') AS HourEnd
FROM master.dbo.spt_values
WHERE [type] = 'P'
AND number < 24
)
, HourCounts
AS
(
SELECT H.HourStart, COUNT(*) AS HourCount
FROM HourRange H
JOIN @SessionDatas D
ON H.HourEnd > D.[Session Start]
AND H.HourStart <= D.[Session End]
GROUP BY H.HourStart
)
SELECT *
FROM HourCounts C
JOIN HourPeaks P
ON C.HourStart = P.HourStart;
Thanks for your reply , your solution is working for me
November 18, 2011 at 12:05 am
Hi, friends thanks for your reply and quick response
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply