Re: SQL and Grouping
- From: GerryO <GerryO@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Jun 2005 07:43:07 -0700
Thanks for you response to my message. I was unable to get this to work
still, but I am wondering what the < and > means.
ON A.[begin] < B.[begin]
AND A.value <> B.value
Sorry for my lack of experience.
Thanks,
Gerry O.
"David Portas" wrote:
> 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: Hugo Kornelis
- Re: SQL and Grouping
- References:
- SQL and Grouping
- From: gerryo
- Re: SQL and Grouping
- From: David Portas
- SQL and Grouping
- Prev by Date: Re: SQL and Grouping
- Next by Date: Re: Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?
- Previous by thread: Re: SQL and Grouping
- Next by thread: Re: SQL and Grouping
- Index(es):
Relevant Pages
|
|
