MS SQL Server MSEQ - SQL and Grouping

Author

SQL and Grouping

gerryo

2005-06-22, 9:23 am

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.

David Portas

2005-06-26, 8:23 pm

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@discussions.microsoft.com> wrote in message
news:7752E7C7-E30F-4B11-86E7- CF522BD5C755@microso
ft.com...
> 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.
>

GerryO

2005-06-27, 9:23 am

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@discussions.microsoft.com> wrote in message
> news:7752E7C7-E30F-4B11-86E7- CF522BD5C755@microso
ft.com...
>
>
>

Hugo Kornelis

2005-06-27, 1:23 pm

On Mon, 27 Jun 2005 07:43:07 -0700, GerryO wrote:

>
>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.

Hi Gerry,

Checking the headers of your message, it appears as if you're using this
group through some Internet portal. And obviously, this portal does some
funny reformatting with the message. I am using a straight feed from a
usenet provider and David's message shows fine for me.

Anyway, < and > are (when delimited by a semicolon) HTML-versions of
the greater-than and smaller-than sign. If you replace each occurence of
< plus semicolon with the smaller-than sign and each > plus
semicolon by a greater-than sign, you should get the query as David
posted it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links