User Count By the Half Hour

Question

Post reply

User Count By the Half Hour

texpic

SSCertifiable

Points: 5887
More actions
December 11, 2006 at 1:47 pm

#94836

I'm trying to write a query that would give me a count of how many accounts were viewed by a user in each half hour period. I have a table with UserCode and WorkStart. This is a simple example, they actually look at accounts the entire day. Thanks!
UserCode Time   WorkStartCount
1            09:00 5
1             09:30 4
1             10:00 6
1            10:30 2
1            11:00 7
1             11:30 0
2             09:00 1
2            09:30 1
2             10:00 6
2             10:30 2
2             11:00 7
2            11:30 1

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

What is the datatype of the time column?

And do you want the count of rows, or the sum of workStartCount?

DaveItz SSCrazy Points: 2146 More actions · Answer 2

It looks like he's building the summary for the time column from the UserCode and the time the user starts viewing the account (WorkStart). Judging from the row with zero instances, he should probably make a table with the 48 half-hour periods and right join on that.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Change COUNT(*) TO SUM(WorkCounts) depending on your needs, this will give you something to start with :

CREATE TABLE #Demo (UserID INT NOT NULL, ViewDate DATETIME NOT NULL)

INSERT INTO #Demo (UserID, ViewDate)

SELECT 1, GetDate()

UNION ALL

SELECT 1, DATEADD(n, 1, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 31, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 61, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 62, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 62, GetDate())

UNION ALL

SELECT 2, DATEADD(n, 31, GetDate())

UNION ALL

SELECT 3, DATEADD(n, 61, GetDate())

SELECT * FROM #Demo

SELECT UserID, DATEDIFF(S, '2000/01/01', ViewDate) / 1800 AS NbrOf30MinsSinceSomeArbitraryDate, COUNT(*) AS Total FROM #Demo GROUP BY UserID, DATEDIFF(S, '2000/01/01', ViewDate) / 1800

DROP TABLE #Demo

texpic SSCertifiable Points: 5887 More actions · Answer 4

I got some ideas looking at thses posts. Thanks. This is rough but a start. I decided to change it to by the hour. Any ideas on how to make this better, also in the same table.

SELECT UserCodeAlpha, COUNT(*) as CntIt_07

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) <= 7

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_08

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 7 AND DATEPART(hh, WorkStart) <= 8

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_09

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 8 AND DATEPART(hh, WorkStart) <= 9

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_10

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 9 AND DATEPART(hh, WorkStart) <= 10

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_11

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 10 AND DATEPART(hh, WorkStart)<= 11

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_12

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 11 AND DATEPART(hh, WorkStart)<= 12

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_13

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 12 AND DATEPART(hh, WorkStart)<= 13

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_14

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 13 AND DATEPART(hh, WorkStart)<= 14

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_15

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 14 AND DATEPART(hh, WorkStart)<= 15

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_16

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 15 AND DATEPART(hh, WorkStart)< = 16

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_17

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 16 AND DATEPART(hh, WorkStart)< = 17

GROUP BY UserCodeAlpha

SELECT UserCodeAlpha, COUNT(*) as CntIt_18

FROM CsvData.._UserStatsPull

WHERE DATEPART(hh, WorkStart) > 18

GROUP BY UserCodeAlpha

Adi Cohen Old Hand Points: 330 More actions · Answer 5

I'd use this one:

declare @GroupByMinutes int

set @GroupByMinutes = 30

select UserCode, dateadd(second, -(datepart(minute, [Time])%@GroupByMinutes*60 + datepart(second, [Time])), [Time]) [Time], count(*) [Accounts_Viewed]

from (your table name)

group by UserCode, dateadd(second, -(datepart(minute, [Time])%@GroupByMinutes*60 + datepart(second, [Time])), [Time])

You can change the @GroupByMinutes parameter to group by different time periods.

Adi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Simple modification from my original answer :

SELECT UserID, DATEDIFF(S, '2000/01/01', ViewDate) / ~~1800~~ 3600 AS NbrOf30MinsSinceSomeArbitraryDate, COUNT(*) AS Total FROM #Demo GROUP BY UserID, DATEDIFF(S, '2000/01/01', ViewDate) / ~~1800~~ 3600

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Using Remi's fine demo table, the following will actually give you dates and times for half hour slots... it will not, however, give any missing slots... that would require just a bit more...

CREATE TABLE #Demo (UserID INT NOT NULL, ViewDate DATETIME NOT NULL)

INSERT INTO #Demo (UserID, ViewDate)

SELECT 1, GetDate()

UNION ALL

SELECT 1, DATEADD(n, 1, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 31, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 61, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 62, GetDate())

UNION ALL

SELECT 1, DATEADD(n, 62, GetDate())

UNION ALL

SELECT 2, DATEADD(n, 31, GetDate())

UNION ALL

SELECT 3, DATEADD(n, 61, GetDate())

SELECT * FROM #Demo

SELECT UserID, DATEADD(mi,DATEDIFF(mi,0,ViewDate)/30*30,0) TimeSlot,COUNT(*) AS TheCount

FROM #Demo

GROUP BY UserID,DATEADD(mi,DATEDIFF(mi,0,ViewDate)/30*30,0)

ORDER BY TimeSlot

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

To finish Jeff's idea. You'd simply use a tally (numbers) table to increment a starting date placed in a variable. Then you'd simply left join that results to the current table and use the same group by so that way you'd get all the timeslots.