Time, Count(Hits)
-
I have table with LoginDateTime (i.e. 01-Jan-2007 10:10 PM) and several other columns. I have to make report that may show results like:
hours wise Login hits.
Hit time Hits 1 to 2 10 2 to 3 25 3 to 4 37 4 to 5 258 5 to 6 13 6 to 7 12 7 to 8 8 8 to 9 45 9 to 10 33 10 to 11 65 11 to 12 58 12 to 13 84 13 to 14 45 14 to 15 65 15 to 16 87 16 to 17 6 17 to 18 66 18 to 19 158 19 to 20 65 20 to 21 85 21 to 22 75 22 to 23 96 23 to 24 22 how to do :
I want dynamic sort aswell.
Declare @dt datetime
set @dt = '01/01/2007'
select datepart(hour, LoginDateTime ), count(*) from LoginTrack
group by datepart(hour, LoginDateTime)
order by 1
but its not showing if there is no any hit in specific time, it should print all time slots from 1 to 24 hrs like above.
Plz help
Shamshad Ali.
-
-- Prepare sample data
DECLARE
@LoginTrack TABLE (LoginDateTime DATETIME)
INSERT
@LoginTrack (LoginDateTime)
SELECT
TOP 10000
DATEADD(SECOND, ABS(CHECKSUM(NEWID())), 0)
FROM
syscolumns as s1
CROSS
JOIN syscolumns as s2
-- Show the expected output
SELECT
CONVERT(VARCHAR, v.Number) + ' to ' + CONVERT(VARCHAR, v.Number + 1) AS [Hit time],
COUNT(*) AS Hits
FROM
master..spt_values AS v
LEFT
JOIN @LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) BETWEEN v.Number AND v.Number + 1
WHERE
v.Type = 'p'
AND v.Number BETWEEN 0 AND 23
GROUP
BY v.Number
ORDER
BY v.Number
N 56°04'39.16"
E 12°55'05.25" -
Thanks Peter, Your help is appreciated. Further more there are TOP 1000 rows. For real scenario If i have no hit during 7 to 9 it should return me 0 againt 7 to 8 and 8 to 9. But that is no doing so.
Also if i put TOP 5, it shows 1 hit per Hit Time that is not a valid outcome. Plz. help
DECLARE
@LoginTrack TABLE (LoginDateTime DATETIME)
INSERT
@LoginTrack (LoginDateTime)
SELECT
TOP 5
DATEADD
(SECOND, ABS(CHECKSUM(NEWID())), 0)
FROM
syscolumns as s1
CROSS
JOIN syscolumns as s2
-- select * from @LoginTrack
-- Show the expected output
SELECT
CONVERT(VARCHAR, v.Number) + ' to ' + CONVERT(VARCHAR, v.Number + 1) AS [Hit time],
COUNT
(*) AS Hits
FROM
master..spt_values AS v
LEFT
JOIN @LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) BETWEEN v.Number AND v.Number + 1
WHERE
v.Type = 'p'
AND
v.Number BETWEEN 0 AND 23
GROUP
BY v.Number
ORDER
BY v.Number
LoginDateTime 1902-04-11 05:30:45.000 1903-10-07 07:42:37.000 1909-02-28 19:29:41.000 1925-07-05 23:35:29.000 1926-02-13 03:37:30.000 Hit time Hits 0 to 1 1 1 to 2 1 2 to 3 1 3 to 4 1 4 to 5 1 5 to 6 1 6 to 7 1 7 to 8 1 8 to 9 1 9 to 10 1 10 to 11 1 11 to 12 1 12 to 13 1 13 to 14 1 14 to 15 1 15 to 16 1 16 to 17 1 17 to 18 1 18 to 19 1 19 to 20 1 20 to 21 1 21 to 22 1 22 to 23 1 23 to 24 1 Plz. check and help.
Shamshad Ali.
-
SELECT CONVERT(VARCHAR, v.Number) + ' to ' + CONVERT(VARCHAR, v.Number + 1) AS [Hit time],
(SELECT COUNT(*) FROM @LoginTrack Z WHERE DATEPART(hour, Z.LoginDateTime) = V.Number) AS Hits
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 0 AND 23
-
Great !!!
Thanks you all for ur time and help.
Shamshad Ali.
-
CREATE TABLE #LoginTrack (RowID INT IDENTITY(1, 1) PRIMARY KEY, LoginDateTime DATETIME)
INSERT #LoginTrack
(
LoginDateTime
)
SELECT TOP 100000
DATEADD(SECOND, ABS(CHECKSUM(NEWID())), 0)
FROM syscolumns as s1
CROSS JOIN syscolumns as s2
DECLARE @dt DATETIME
-- Peso
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
set @dt = current_Timestamp
SELECT CONVERT(VARCHAR, v.Number) + ' to ' + CONVERT(VARCHAR, v.Number + 1) AS [Hit time],
COUNT(lt.LoginDateTime) AS Hits
FROM master..spt_values AS v
LEFT JOIN #LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) = v.Number
WHERE v.Type = 'p'
AND v.Number BETWEEN 0 AND 23
GROUP BY v.Number
ORDER BY v.Number
select datediff(ms, @dt, current_timestamp)
-- Koji
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
set @dt = current_Timestamp
SELECT CONVERT(VARCHAR, v.Number) + ' to ' + CONVERT(VARCHAR, v.Number + 1) AS [Hit time],
(SELECT COUNT(*) FROM #LoginTrack Z WHERE DATEPART(hour, Z.LoginDateTime) = V.Number) AS Hits
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 0 AND 23
ORDER BY v.Number
select datediff(ms, @dt, current_timestamp)
Also the reads are down from 6581 to 269, a speed factor of 24 times faster.
And the time is down from 546 ms to 63 ms, a speed factor of 8 times faster.
N 56°04'39.16"
E 12°55'05.25" -
Peso,
I have been reading many of you and a few others's excellent solutions.
I know join is much more efficient.
I have learned that from you and Jeff M and a few others.
However, I feel it is not that easy to understand for non-experienced members.
It is not a contest and he is not asking to optimize.
If he is smart enough to understand your version,
he should have been able to come up with mine from your original post, don't you think?
Anyways, I'm always looking forward to see your posts.
Koji
-
I believe that if OP is new (non-experienced) to SQL Server, he or she is better of learning the proper [ansi join] way directly, instead of learning them CORRELATED SUBQUERIES, if they can be avoided.
N 56°04'39.16"
E 12°55'05.25" -
wat if the sql user has no permit to master.. permit?
Furthermore, I require percentage per hr. how to do this?
No. Hit Duration Percent Hits 1 00:00 to 01:00 6.67% 1 2 01:00 to 02:00 0% 0 3 02:00 to 03:00 0% 0 4 03:00 to 04:00 0% 0 5 04:00 to 05:00 6.67% 1 6 05:00 to 06:00 0% 0 7 06:00 to 07:00 0% 0 8 07:00 to 08:00 0% 0 9 08:00 to 09:00 6.67% 1 10 09:00 to 10:00 6.67% 1 11 10:00 to 11:00 13.33% 2 12 11:00 to 12:00 20% 3 13 12:00 to 13:00 0% 0 14 13:00 to 14:00 0% 0 15 14:00 to 15:00 13.33% 2 16 15:00 to 16:00 6.67% 1 17 16:00 to 17:00 6.67% 1 18 17:00 to 18:00 0% 0 19 18:00 to 19:00 0% 0 20 19:00 to 20:00 0% 0 21 20:00 to 21:00 0% 0 22 21:00 to 22:00 0% 0 23 22:00 to 23:00 6.67% 1 24 23:00 to 24:00 6.67% 1 TOTAL: 100% 15 Shamshad Ali.
-
SELECT v.Number + 1 AS Hit,
CONVERT(CHAR(5), DATEADD(HOUR, v.Number, 0), 108) + ' to ' + CONVERT(CHAR(5), DATEADD(HOUR, v.Number + 1, 0), 108) AS [Hit Duration],
CONVERT(DECIMAL(5, 2), 100.0 * COUNT(lt.LoginDateTime) / t.h) AS [Percent],
COUNT(lt.LoginDateTime) AS Hits
FROM (
SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
SELECT 22 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS v
CROSS JOIN (
SELECT COUNT(*) AS h FROM LoginTrack
) AS t
LEFT JOIN LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) = v.Number
GROUP BY v.Number,
t.h
ORDER BY v.Number
N 56°04'39.16"
E 12°55'05.25" -
Peter, once again thanks for your support and help.
I have following query and results, that are showing me actual data with date/ time:
Declare
@todate datetime,
@fromdate datetime,
@channelid numeric(9)
set @todate = '01/01/2007'
set @fromdate = '01/01/2007'
select LoginDateTime from LoginTrack
where convert (varchar, LoginDateTime,101) between @fromdate and @todate
order by LoginDateTime
No. Login_datetime 1 2007-01-01 04:00:00.000 2 2007-01-01 08:00:00.000 3 2007-01-01 09:00:00.000 4 2007-01-01 10:26:18.503 5 2007-01-01 11:00:00.000 6 2007-01-01 11:00:00.000 7 2007-01-01 11:00:00.000 8 2007-01-01 14:00:00.000 9 2007-01-01 14:52:27.817 10 2007-01-01 16:00:00.000 11 2007-01-01 22:00:00.000 12 2007-01-01 23:00:00.000 now when I apply your query to get desired results its Total count (that should be 12) is now 13. Is there any thing wrong with my checking query above to find actual data for a day (01/01/07)?
here is my modified query and its output.
Declare
@todate datetime,
@fromdate datetime,
@channelid numeric(9)
set @todate = '01/01/2007'
set @fromdate = '01/01/2007'
SELECT v.Number + 1 AS Hit,
CONVERT(CHAR(5), DATEADD(HOUR, v.Number, 0), 108) + ' to ' + CONVERT(CHAR(5), DATEADD(HOUR, v.Number + 1, 0), 108) AS [Hit Duration],
CONVERT(DECIMAL(5, 2), 100.0 * COUNT(lt.LoginDateTime) / t.h) AS [Percent],
COUNT(lt.LoginDateTime) AS Hits
FROM (
SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
SELECT 22 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS v
CROSS JOIN (
SELECT COUNT(*) AS h FROM LoginTrack
where convert (varchar, LoginDateTime,101) between @fromdate and @todate
) AS t
LEFT JOIN LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) = v.Number
where convert (varchar, lt.LoginDateTime,101) between @fromdate and @todate
GROUP BY v.Number,
t.h
ORDER BY v.Number
Hit Hit duration Percent Hits 5 04:00 to 05:00 8.33 1 9 08:00 to 09:00 8.33 1 10 09:00 to 10:00 8.33 1 11 10:00 to 11:00 8.33 1 12 11:00 to 12:00 25 3 15 14:00 to 15:00 16.67 2 17 16:00 to 17:00 8.33 1 23 22:00 to 23:00 16.67 2 24 23:00 to 00:00 8.33 1 Total <TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(D2
10)">13Plz. see if my first query above is wrong? Is this wrong approach? Aslo I can't get all 24 rows in output above.
where convert (varchar, LoginDateTime,101) between @fromdate and @todate
May between work with above or NOT? users can put any date range but for checking purpose i just input from and to date as '01/01/07' to find actual data in table for a day and get correct outcome.
Shamshad Ali.
-
DECLARE @ToDate DATETIME,
@FromDate DATETIME,
@ChannelID NUMERIC(9)
SELECT @ToDate = '01/01/2007',
@FromDate = '01/01/2007'
SELECT v.Number + 1 AS Hit,
CONVERT(CHAR(5), DATEADD(HOUR, v.Number, 0), 108) + ' to ' + CONVERT(CHAR(5), DATEADD(HOUR, v.Number + 1, 0), 108) AS [Hit Duration],
CONVERT(DECIMAL(5, 2), 100.0 * COUNT(lt.LoginDateTime) / t.h) AS [Percent],
COUNT(lt.LoginDateTime) AS Hits
FROM (
SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
SELECT 22 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
) AS v
CROSS JOIN (
SELECT COUNT(*) AS h
FROM LoginTrack
WHERE LoginDateTime >= @FromDate
AND LoginDateTime < DATEADD(DAY, 1, @ToDate)
) AS t
LEFT JOIN LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) = v.Number
AND lt.LoginDateTime >= @FromDate
AND lt.LoginDateTime < DATEADD(DAY, 1, @ToDate)
GROUP BY v.Number,
t.h
ORDER BY v.Number
N 56°04'39.16"
E 12°55'05.25" -
BTW, are you using SQL Server 2000 or SQL Server 2005?
N 56°04'39.16"
E 12°55'05.25" -
SQL Server 2000 SP4
-
Again, Again, in my second last reply i checked actual rows in table were 12 and the count is giving me 13 rows with your given query output. that should return 12 Hits if we count result.
Shamshad Ali.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply