July 31, 2012 at 7:00 pm
I'm trying to create two reports that would look like this; passing a date range for start/end:
Report #1 Sort By Date, HourStart, UserId WHERE Dept <> 2
Date HourStart UserId Count
07/30/2012 8:00 10 1
07/30/2012 9:00 10 4
07/31/2012 8:00 10 1
07/31/2012 9:00 10 3
07/30/2012 8:00 11 1
07/30/2012 9:00 11 4
07/31/2012 8:00 11 1
07/31/2012 9:00 11 2
07/30/2012 8:00 12 1
07/30/2012 9:00 12 1
07/31/2012 9:00 12 1
Report #2 Sort By Date, HourStart, WHERE Dept <> 2
(user id on heading row, counts below)
Date HourStart 10 11 12 (user ids)
07/30/2012 8:00 1 1 1
07/30/2012 9:00 4 4 1
07/31/2012 8:00 1 1 0
07/31/2012 9:00 3 2 1
Data below, thanks!
July 31, 2012 at 7:01 pm
IF OBJECT_ID('tempdb..#CountsPerHour') > 0 DROP TABLE #CountsPerHour
CREATE TABLE #CountsPerHour
(Dept int, UserId int, StartTime datetime)
INSERT INTO #CountsPerHour
SELECT 1, 10, '2012-07-30 08:52' UNION ALL
SELECT 1, 10, '2012-07-30 09:52' UNION ALL
SELECT 1, 10, '2012-07-30 09:53' UNION ALL
SELECT 1, 10, '2012-07-30 09:54' UNION ALL
SELECT 1, 10, '2012-07-30 09:55' UNION ALL
SELECT 1, 10, '2012-07-31 08:52' UNION ALL
SELECT 1, 10, '2012-07-31 09:52' UNION ALL
SELECT 1, 10, '2012-07-31 09:53' UNION ALL
SELECT 1, 10, '2012-07-31 09:54' UNION ALL
SELECT 1, 11, '2012-07-30 08:52' UNION ALL
SELECT 1, 11, '2012-07-30 09:52' UNION ALL
SELECT 1, 11, '2012-07-30 09:53' UNION ALL
SELECT 1, 11, '2012-07-30 09:54' UNION ALL
SELECT 1, 11, '2012-07-30 09:55' UNION ALL
SELECT 1, 11, '2012-07-31 08:52' UNION ALL
SELECT 1, 11, '2012-07-31 09:52' UNION ALL
SELECT 1, 11, '2012-07-31 09:53' UNION ALL
SELECT 1, 12, '2012-07-30 08:52' UNION ALL
SELECT 1, 12, '2012-07-30 09:52' UNION ALL
SELECT 1, 12, '2012-07-31 09:52' UNION ALL
SELECT 2, 10, '2012-07-31 09:52' UNION ALL
SELECT 2, 10, '2012-07-31 10:52'
SELECT * FROM #CountsPerHour
ORDER BY Dept, UserId, StartTime
DECLARE @DateStart datetime SET @DateStart = '2012-07-28'
DECLARE @DateEnd datetime SET @DateEnd = '2012-08-01'
August 1, 2012 at 3:01 am
IF OBJECT_ID('tempdb..#CountsPerHour') > 0 DROP TABLE #CountsPerHour
CREATE TABLE #CountsPerHour
(Dept int, UserId int, StartTime datetime)
INSERT INTO #CountsPerHour
SELECT 1, 10, '2012-07-30 08:52' UNION ALL
SELECT 1, 10, '2012-07-30 09:52' UNION ALL
SELECT 1, 10, '2012-07-30 09:53' UNION ALL
SELECT 1, 10, '2012-07-30 09:54' UNION ALL
SELECT 1, 10, '2012-07-30 09:55' UNION ALL
SELECT 1, 10, '2012-07-31 08:52' UNION ALL
SELECT 1, 10, '2012-07-31 09:52' UNION ALL
SELECT 1, 10, '2012-07-31 09:53' UNION ALL
SELECT 1, 10, '2012-07-31 09:54' UNION ALL
SELECT 1, 11, '2012-07-30 08:52' UNION ALL
SELECT 1, 11, '2012-07-30 09:52' UNION ALL
SELECT 1, 11, '2012-07-30 09:53' UNION ALL
SELECT 1, 11, '2012-07-30 09:54' UNION ALL
SELECT 1, 11, '2012-07-30 09:55' UNION ALL
SELECT 1, 11, '2012-07-31 08:52' UNION ALL
SELECT 1, 11, '2012-07-31 09:52' UNION ALL
SELECT 1, 11, '2012-07-31 09:53' UNION ALL
SELECT 1, 12, '2012-07-30 08:52' UNION ALL
SELECT 1, 12, '2012-07-30 09:52' UNION ALL
SELECT 1, 12, '2012-07-31 09:52' UNION ALL
SELECT 2, 13, '2012-07-31 09:52' UNION ALL
SELECT 2, 13, '2012-07-31 10:52'
IF OBJECT_ID('tempdb..#CountsPerHour') > 0 DROP TABLE #RollUpUsers
DECLARE @StartTime datetime SET @StartTime = '2012-07-30'
DECLARE @EndTime datetime SET @EndTime = '2012-08-01'
DECLARE @Period int SET @Period = 60
--Period 30 minutes = 30; 60 minutes = 60; 24 hours = 1440; week = 10800
SELECT
UserID,
DATEADD(mi,(DATEDIFF(mi,0,StartTime) / @Period) * @Period, 0) as Period,
COUNT(*) AS Cnt
INTO #RollUpUsers
FROM #CountsPerHour
WHERE StartTime >= @StartTime
AND StartTime <= @EndTime
AND Dept <> 2
GROUP BY UserID,DATEADD(mi,(DATEDIFF(mi,0,StartTime) / @Period) * @Period, 0)
ORDER BY UserId, Period
SELECT
UserId,
LEFT(CONVERT(varchar(100), Period, 101), 5) as WorkDate,
SUBSTRING(CONVERT(varchar(100), Period, 121), 12, 5) as WorkTime,
Cnt
FROM #RollUpUsers
August 1, 2012 at 3:07 am
That gets me the first report. Is there a way to dynamically create this report from those results? There would be more time periods and more users.
date time 10 11 12
07/30 8:00 1 1 1
07/30 9:00 4 4 1
07/31 8:00 1 1 0
07/31 9:00 3 2 1
Thanks
August 1, 2012 at 3:30 am
SELECT Dept, UserId, x.[date], x.[Hour], COUNT(*)
FROM #CountsPerHour
CROSS APPLY (
SELECT
[date] = DATEADD(dd,0,DATEDIFF(dd,0,StartTime)),
[Hour] = DATEPART(hh,StartTime)
) x
WHERE Dept <> 2
AND StartTime >= @DateStart -- NOTE >=
AND StartTime < @DateEnd -- NOTE <
GROUP BY Dept, UserId, x.[date], x.[Hour]
ORDER BY UserId, Dept, x.[date], x.[Hour]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2012 at 3:56 am
texpic (8/1/2012)
That gets me the first report. Is there a way to dynamically create this report from those results? There would be more time periods and more users.date time 10 11 12
07/30 8:00 1 1 1
07/30 9:00 4 4 1
07/31 8:00 1 1 0
07/31 9:00 3 2 1
Thanks
Here's the "base code" for it (this is a "CROSSTAB" query);
SELECT
[Date],
[Time],
[10] = MAX(CASE WHEN UserId = 10 THEN [Count] ELSE 0 END),
[11] = MAX(CASE WHEN UserId = 11 THEN [Count] ELSE 0 END),
[12] = MAX(CASE WHEN UserId = 12 THEN [Count] ELSE 0 END)
FROM #PivotSource
GROUP BY [Date], [Time]
ORDER BY [Date], [Time]
Since you've got multiple UserID's and they're unknown at the time you write the crosstab query, you will have to write it dynamically, reading the list of users from the table PivotSource. It's easier than it sounds:
DECLARE @SQLStatement VARCHAR(2000)
SET @SQLStatement = 'SELECT [Date], [Time]'
SELECT @SQLStatement = @SQLStatement + ', ' + CHAR(13) + '['+CAST(UserId AS VARCHAR(10)) + '] = MAX(CASE WHEN UserId = ' + CAST(UserId AS VARCHAR(10)) + ' THEN [Count] ELSE 0 END) '
FROM #PivotSource
GROUP BY UserId
ORDER BY UserId
SELECT @SQLStatement = @SQLStatement + CHAR(13) + 'FROM #PivotSource GROUP BY [Date], [Time] ORDER BY [Date], [Time]'
PRINT @SQLStatement
Have a look at the output of the PRINT statement (it will be in the messages tab of SSMS).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply