Grouping and Ordering -- sql server programming

dpjolly NO[at]SPAM iprimus.com.au
7/15/2003 11:28:59 PM

Hello Group,

I am having problems generating the following result set...
Can it be done?

Raw Data
========

DateTime varA varB
--------------------- ---- ----
2003-04-28 09:27:00 0 1
2003-04-28 09:28:00 0 1
2003-04-28 09:29:00 0 1
2003-04-28 09:30:00 1 1
2003-04-28 09:31:00 0 0
2003-04-28 09:32:00 0 0
2003-04-28 09:33:00 1 1
2003-04-28 09:34:00 1 1
2003-04-28 09:35:00 0 0
2003-04-28 09:36:00 0 0
2003-04-28 09:37:00 0 0
2003-04-28 09:38:00 0 0
2003-04-28 09:39:00 0 0
2003-04-28 09:40:00 0 1

Desired Results
===============

MinDate MaxDate varA varB
--------------------- --------------------- ---- ----
2003-04-28 09:27:00 2003-04-28 09:27:00 0 1
2003-04-28 09:30:00 2003-04-28 09:30:00 1 1
2003-04-28 09:31:00 2003-04-28 09:32:00 0 0
2003-04-28 09:33:00 2003-04-28 09:34:00 1 1
2003-04-28 09:35:00 2003-04-28 09:39:00 0 0
2003-04-28 09:40:00 2003-04-28 09:40:00 0 1

Select Min(DateTime) as MinDate, Max(DateTime) as MaxDate, varA, varB
From MyData
Group by varA, varB
Order by DateTime

Doesn't work.

Dean Savovic
7/16/2003 9:08:10 AM

If in first row in your desired result set is MaxDate = '2003-04-28 09:27:00' then try this.

create table test
( dtm datetime primary key
, varA int not null
, varB int not null
)

insert into test (dtm, varA, varB)
select '2003-04-28 09:27:00', 0, 1
union
select '2003-04-28 09:28:00', 0, 1
union
select '2003-04-28 09:29:00', 0, 1
union
select '2003-04-28 09:30:00', 1, 1
union
select '2003-04-28 09:31:00', 0, 0
union
select '2003-04-28 09:32:00', 0, 0
union
select '2003-04-28 09:33:00', 1, 1
union
select '2003-04-28 09:34:00', 1, 1
union
select '2003-04-28 09:35:00', 0, 0
union
select '2003-04-28 09:36:00', 0, 0
union
select '2003-04-28 09:37:00', 0, 0
union
select '2003-04-28 09:38:00', 0, 0
union
select '2003-04-28 09:39:00', 0, 0
union
select '2003-04-28 09:40:00', 0, 1

select
RBr = (select count(*) from test ct where ct.dtm <= t1.dtm
and convert(char(1), ct.varA) + convert(char(1), ct.varB)
<> convert(char(1), t1.varA)+ convert(char(1), t1.varB))
, *
into #temp
from test t1

select
MinDate = min(dtm), MaxDate = max(dtm), varA, varB
from #temp
group by RBr, varA, varB

drop table test
drop table #temp

--
Dean Savovic
www.teched.hr

[quoted text, click to view]

sql server programming : Grouping and Ordering