min intervals round the clock for a day and compare them with the same value
for other days of the w
around the same time to get a percentage trend.So input in a table A for eg: would be
TableA
Date1 Count1
2/1/2006 00:01 1
2/1/2006 00:03 1
2/1/2006 00:05 1
2/1/2006 00:07 1
2/1/2006 00:09 1
2/1/2006 00:11 1
2/1/2006 00:16 1
2/1/2006 01:03 1
2/1/2006 01:05 1
2/1/2006 01:13 1
2/2/2006 00:01 1
2/2/2006 00:03 1
2/2/2006 00:05 1
2/2/2006 00:07 1
2/2/2006 00:08 1
2/2/2006 00:09 1
2/2/2006 00:11 1
2/2/2006 00:16 1
2/2/2006 01:03 1
2/2/2006 01:05 1
2/2/2006 01:13 1
Create table tableA
(Date1 datetime,
count1 int)
insert tableA values('2/1/2006 00:01 ' , 1)
insert tableA values('2/1/2006 00:03' , 1)
insert tableA values('2/1/2006 00:05' , 1)
insert tableA values('2/1/2006 00:07' , 1)
insert tableA values('2/1/2006 00:09' , 1)
insert tableA values('2/1/2006 00:11' , 1)
insert tableA values('2/1/2006 00:16' , 1)
insert tableA values('2/1/2006 01:03' , 1)
insert tableA values('2/1/2006 01:05' , 1)
insert tableA values('2/1/2006 01:13' , 1)
insert tableA values('2/2/2006 00:01' , 1)
insert tableA values('2/2/2006 00:03' , 1)
insert tableA values('2/2/2006 00:05' , 1)
insert tableA values('2/2/2006 00:07' , 1)
insert tableA values('2/2/2006 00:08' , 1)
insert tableA values('2/2/2006 00:09' , 1)
insert tableA values('2/2/2006 00:11' , 1)
insert tableA values('2/2/2006 00:16' , 1)
insert tableA values('2/2/2006 01:03' , 1)
insert tableA values('2/2/2006 01:05' , 1)
insert tableA values('2/2/2006 01:13' , 1)
Output required
Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
00 0 0 0
00 5 2 2
00 10 3 4
00 15 1 1
00 20 1 1
00 25 0 0
00 30 0 0
00 35 0 0
00 40 0 0
00 45 0 0
00 50 0 0
00 55 0 0
01 00 0 0
01 5 2 2
01 10 3 3
01 15 1 1
01 20 1 1
01 25 0 0
01 30 0 0
01 35 0 0
01 40 0 0
01 45 0 0
01 50 0 0
01 55 0 0
........
As you can see, I would like to group within 5 minute intervals of the hour.
I would then like to pivot the dates so I can trend day over day. Ideally Id
like to group daily for 7 days .. that way its not dynamic
Can someone assist ?Hassan
Take a look at Erland's example. Perhaps it is not exactly what you wanted
but it certainly give you an idea
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 5
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eSVEB7wOGHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a table with items bought by the sec. I would like to group them by
>5 min intervals round the clock for a day and compare them with the same
>value for other days of the w
around the same time to get a percentage>trend.
> So input in a table A for eg: would be
> TableA
> Date1 Count1
> 2/1/2006 00:01 1
> 2/1/2006 00:03 1
> 2/1/2006 00:05 1
> 2/1/2006 00:07 1
> 2/1/2006 00:09 1
> 2/1/2006 00:11 1
> 2/1/2006 00:16 1
> 2/1/2006 01:03 1
> 2/1/2006 01:05 1
> 2/1/2006 01:13 1
> 2/2/2006 00:01 1
> 2/2/2006 00:03 1
> 2/2/2006 00:05 1
> 2/2/2006 00:07 1
> 2/2/2006 00:08 1
> 2/2/2006 00:09 1
> 2/2/2006 00:11 1
> 2/2/2006 00:16 1
> 2/2/2006 01:03 1
> 2/2/2006 01:05 1
> 2/2/2006 01:13 1
>
> Create table tableA
> (Date1 datetime,
> count1 int)
> insert tableA values('2/1/2006 00:01 ' , 1)
> insert tableA values('2/1/2006 00:03' , 1)
> insert tableA values('2/1/2006 00:05' , 1)
> insert tableA values('2/1/2006 00:07' , 1)
> insert tableA values('2/1/2006 00:09' , 1)
> insert tableA values('2/1/2006 00:11' , 1)
> insert tableA values('2/1/2006 00:16' , 1)
> insert tableA values('2/1/2006 01:03' , 1)
> insert tableA values('2/1/2006 01:05' , 1)
> insert tableA values('2/1/2006 01:13' , 1)
> insert tableA values('2/2/2006 00:01' , 1)
> insert tableA values('2/2/2006 00:03' , 1)
> insert tableA values('2/2/2006 00:05' , 1)
> insert tableA values('2/2/2006 00:07' , 1)
> insert tableA values('2/2/2006 00:08' , 1)
> insert tableA values('2/2/2006 00:09' , 1)
> insert tableA values('2/2/2006 00:11' , 1)
> insert tableA values('2/2/2006 00:16' , 1)
> insert tableA values('2/2/2006 01:03' , 1)
> insert tableA values('2/2/2006 01:05' , 1)
> insert tableA values('2/2/2006 01:13' , 1)
> Output required
> Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
> 00 0 0 0
> 00 5 2 2
> 00 10 3 4
> 00 15 1 1
> 00 20 1 1
> 00 25 0 0
> 00 30 0 0
> 00 35 0 0
> 00 40 0 0
> 00 45 0 0
> 00 50 0 0
> 00 55 0 0
> 01 00 0 0
> 01 5 2 2
> 01 10 3 3
> 01 15 1 1
> 01 20 1 1
> 01 25 0 0
> 01 30 0 0
> 01 35 0 0
> 01 40 0 0
> 01 45 0 0
> 01 50 0 0
> 01 55 0 0
> ........
>
> As you can see, I would like to group within 5 minute intervals of the
> hour. I would then like to pivot the dates so I can trend day over day.
> Ideally Id like to group daily for 7 days .. that way its not dynamic
> Can someone assist ?
>
>|||On Sun, 26 Feb 2006 11:56:36 -0800, Hassan wrote:
>I have a table with items bought by the sec. I would like to group them by
5
>min intervals round the clock for a day and compare them with the same valu
e
>for other days of the w
around the same time to get a percentage trend.(snip)
Hi Hassan,
Thanks for posting CREATE TABLE and INSERT statements and expected
output. This made it very easy to develop the query below, which will
return the expected results, BUT:
1. Rows with only 0 count are excluded. If you really need them, you'll
have to add a numbers table to the query to get the desired result (let
me know if you need assistance with that part as well)
2. Not all output matches your expected output. I think that the errors
are in your post. If not, I must have misunderstood the requirements.
Anyway, here's the query:
DECLARE @.BaseDate datetime
SET @.BaseDate = '20060201'
SELECT FiveMinIntervals / 12 AS Hours,
FiveMinIntervals % 12 * 5 AS Minutes,
SUM(CASE WHEN Days = 0 THEN count1 ELSE 0 END) AS Day1,
SUM(CASE WHEN Days = 1 THEN count1 ELSE 0 END) AS Day2,
-- repeat some more times
SUM(CASE WHEN Days = 6 THEN count1 ELSE 0 END) AS Day7
FROM (SELECT DATEDIFF(day, @.BaseDate, Date1) AS Days,
DATEDIFF(minute, @.BaseDate, Date1) / 5 % 288
AS FiveMinIntervals,
count1
FROM tableA) AS d
GROUP BY FiveMinIntervals
Hugo Kornelis, SQL Server MVP
No comments:
Post a Comment