Group By question

Hello!
I would like to get my result grouped by 15 min or 30 min instead of
1 min like I have now, see below.
Can anyone help me ? Any ideas?
select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
from s_case (nolock)
where CAS_USG_PK_OPEN=255543
AND day(CAS_DT_OPE)=day(getdate())
AND month(CAS_DT_OPE)=month(getdate())
AND year(CAS_DT_OPE)=year(getdate())
GROUP BY datepart(mi,CAS_DT_OPE)
order by 2
****Result*******************
2 2006-04-27 06:52:54.783
1 2006-04-27 07:22:54.973
1 2006-04-27 07:23:56.493
2 2006-04-27 08:05:04.217
1 2006-04-27 08:07:41.723
1 2006-04-27 08:27:04.600
1 2006-04-27 08:29:37.840
1 2006-04-27 08:33:13.347
1 2006-04-27 08:39:23.620
1 2006-04-27 08:41:35.240
1 2006-04-27 09:12:00.930
2 2006-04-27 09:13:06.540
1 2006-04-27 09:16:34.030
1 2006-04-27 09:18:00.717
2 2006-04-27 09:26:51.957
1 2006-04-27 09:31:31.507
1 2006-04-27 09:53:32.873
regards
MikeFor group by 15 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /15
For group by 30 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /30|||Below is an example using an interval table variable. This technique has
the benefit of reporting intervals even when no related data exists. If
this is something you do often, consider creating a permanent table for this
purpose:
SET NOCOUNT ON
DECLARE @.IntervalInSeconds int
DECLARE @.StartTime datetime
DECLARE @.EndTime datetime
---
-- **** specify interval size and range below ***
---
SET @.IntervalInSeconds = 900 -- 15 minutes
SET @.StartTime = '20060427 00:00:00'
SET @.EndTime = '20060428 00:00:00'
---
-- create and load Intervals table
DECLARE @.Intervals TABLE
(
StartTime datetime NOT NULL
PRIMARY KEY,
EndTime datetime NOT NULL
)
WHILE @.StartTime < @.EndTime
BEGIN
INSERT INTO @.Intervals VALUES(@.StartTime, DATEADD(ss,
@.IntervalInSeconds, @.StartTime))
SET @.StartTime = DATEADD(ss, @.IntervalInSeconds, @.StartTime)
END
SELECT
COUNT(*) AS Count_Case,
i.StartTime AS IntervalStart,
MIN(CAS_DT_OPE) AS TIME_DATE
FROM s_case (NOLOCK)
JOIN @.Intervals i ON
s_case.CAS_DT_OPE >= i.StartTime
AND s_case.CAS_DT_OPE < i.EndTime
WHERE
CAS_USG_PK_OPEN=255543
AND CAS_DT_OPE >= CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)
AND CAS_DT_OPE < CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) + 1
GROUP BY
i.StartTime
ORDER BY 3
Hope this helps.
Dan Guzman
SQL Server MVP
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1146139098.374595.30350@.v46g2000cwv.googlegroups.com...
> Hello!
> I would like to get my result grouped by 15 min or 30 min instead of
> 1 min like I have now, see below.
> Can anyone help me ? Any ideas?
> select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
> from s_case (nolock)
> where CAS_USG_PK_OPEN=255543
> AND day(CAS_DT_OPE)=day(getdate())
> AND month(CAS_DT_OPE)=month(getdate())
> AND year(CAS_DT_OPE)=year(getdate())
> GROUP BY datepart(mi,CAS_DT_OPE)
> order by 2
> ****Result*******************
> 2 2006-04-27 06:52:54.783
> 1 2006-04-27 07:22:54.973
> 1 2006-04-27 07:23:56.493
> 2 2006-04-27 08:05:04.217
> 1 2006-04-27 08:07:41.723
> 1 2006-04-27 08:27:04.600
> 1 2006-04-27 08:29:37.840
> 1 2006-04-27 08:33:13.347
> 1 2006-04-27 08:39:23.620
> 1 2006-04-27 08:41:35.240
> 1 2006-04-27 09:12:00.930
> 2 2006-04-27 09:13:06.540
> 1 2006-04-27 09:16:34.030
> 1 2006-04-27 09:18:00.717
> 2 2006-04-27 09:26:51.957
> 1 2006-04-27 09:31:31.507
> 1 2006-04-27 09:53:32.873
> regards
> Mike
>|||Didn't do the trick...
/Mike|||Thanks Dan that did the trick!!
/Mike|||On 27 Apr 2006 04:58:18 -0700, McA wrote:

>Hello!
>I would like to get my result grouped by 15 min or 30 min instead of
>1 min like I have now, see below.
>Can anyone help me ? Any ideas?
GROUP BY DATEDIFF(minute, '20060101', CAS_DT_OPE) / 15
Hugo Kornelis, SQL Server MVP|||Thanks Hugo that worked 2!!
And very easy to!!
/McA