Re: SQL and Grouping
- From: "David Portas" <REMOVE_BEFORE_REPLYING_dportas@xxxxxxx>
- Date: Sun, 26 Jun 2005 21:25:09 +0100
SELECT name, MIN([begin]) AS [begin], MAX([end]) AS [end],
value
FROM
(SELECT A.name, A.[begin], A.[end], A.value,
MIN(B.[end]) AS endx
FROM YourTable AS A
LEFT JOIN YourTable AS B
ON A.[begin] < B.[begin]
AND A.value <> B.value
AND A.name = B.name
GROUP BY A.name, A.[begin], A.[end], A.value) AS T
GROUP BY name, endx, value
ORDER BY [begin] ;
--
David Portas
SQL Server MVP
--
"gerryo" <gerryo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7752E7C7-E30F-4B11-86E7-CF522BD5C755@xxxxxxxxxxxxxxxx
> This should be simple but I just can't get it. Please Help!
>
> I have a table that contains hourly records, with a begin time and end
> time,
> and a value field. I need to group these so they are not hourly anymore.
> For example:
>
> TABLE BEFORE
>
> NAME BEGIN END VALUE
> AGR1 1/1/2005 1:00:00 1/1/2005 2:00:00 10
> AGR1 1/1/2005 2:00:00 1/1/2005 3:00:00 10
> AGR1 1/1/2005 3:00:00 1/1/2005 4:00:00 10
> AGR1 1/1/2005 4:00:00 1/1/2005 5:00:00 5
> AGR1 1/1/2005 5:00:00 1/1/2005 6:00:00 5
> AGR1 1/1/2005 6:00:00 1/1/2005 7:00:00 10
>
> I need the results to look like this:
>
> NAME BEGIN END VALUE
> AGR1 1/1/2005 1:00:00 1/1/2005 4:00:00 10
> AGR1 1/1/2005 4:00:00 1/1/2005 6:00:00 5
> AGR1 1/1/2005 6:00:00 1/1/2005 7:00:00 10
>
>
> Thanks in Advance,
> Gerry O.
>
.
- Follow-Ups:
- Re: SQL and Grouping
- From: GerryO
- Re: SQL and Grouping
- References:
- SQL and Grouping
- From: gerryo
- SQL and Grouping
- Prev by Date: Re: Insert non duplicate data
- Next by Date: Re: SQL and Grouping
- Previous by thread: SQL and Grouping
- Next by thread: Re: SQL and Grouping
- Index(es):
Relevant Pages
|
|
