December 11, 2006 at 1:47 pm
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
December 11, 2006 at 2:05 pm
What is the datatype of the time column?
And do you want the count of rows, or the sum of workStartCount?
December 11, 2006 at 2:14 pm
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.
December 11, 2006 at 2:34 pm
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
December 11, 2006 at 3:09 pm
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
December 12, 2006 at 1:30 am
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
December 12, 2006 at 7:54 am
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
December 12, 2006 at 11:04 pm
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
Change is inevitable... Change for the better is not.
December 13, 2006 at 8:31 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply