resultset merging
-
Hi,
The problem is I want to group data but the rule is only continous same values should be grouped i.e.
create table seasons(
pkey int identity(1,1),
season char(1),
fromdate datetime,
todate datetime
)
insert into a(season, fromdate, todate) values('a','1/1/2002', '1/30/2002')
insert into a(season, fromdate, todate) values('c','2/1/2002', '2/28/2002')
insert into a(season, fromdate, todate) values('a','3/1/2002', '3/30/2002')
insert into a(season, fromdate, todate) values('c','4/1/2002', '4/30/2002')
insert into a(season, fromdate, todate) values('b','5/1/2002', '5/30/2002')
insert into a(season, fromdate, todate) values('c','6/1/2002', '6/30/2002')
insert into a(season, fromdate, todate) values('b','7/1/2002', '7/30/2002')
insert into a(season, fromdate, todate) values('d','8/1/2002', '8/30/2002')
insert into a(season, fromdate, todate) values('b','9/1/2002', '9/30/2002')
insert into a(season, fromdate, todate) values('a','10/1/2002', '10/30/2002')
The following SELECT:
Select season, fromdate, todate from seasons
where season <> 'c' group by fromdate, season
returns:
a 2002-01-01 00:00:00.000 2002-01-30 00:00:00.000
a 2002-03-01 00:00:00.000 2002-03-30 00:00:00.000
b 2002-05-01 00:00:00.000 2002-05-30 00:00:00.000
b 2002-07-01 00:00:00.000 2002-07-30 00:00:00.000
d 2002-08-01 00:00:00.000 2002-08-30 00:00:00.000
b 2002-09-01 00:00:00.000 2002-09-30 00:00:00.000
a 2002-10-01 00:00:00.000 2002-10-30 00:00:00.000
what I want to is:
a 2002-01-01 00:00:00.000 2002-03-30 00:00:00.000
b 2002-05-01 00:00:00.000 2002-07-30 00:00:00.000
d 2002-08-01 00:00:00.000 2002-08-30 00:00:00.000
b 2002-09-01 00:00:00.000 2002-09-30 00:00:00.000
a 2002-10-01 00:00:00.000 2002-10-30 00:00:00.000
Thanx.
-
Simplest solution is probably a cursor loop
, but below you can find a set-based approach.This solution uses only a single temptable. Probably just a starting point that can use some improvement.
select identity(int) as pkey, season, fromdate, todate into #tempseasons from seasons
order by fromdate, season
select d.season, min(d.fromdate), d.todate
from
(select a.season, a.fromdate, max(b.todate) as todate
from #tempseasons a, #tempseasons b
where a.season = b.season and b.pkey >= a.pkey
and b.pkey < (select isnull(min(c.pkey), a.pkey+1) from #tempseasons c where c.season <> 'c' and c.season <> a.season and c.pkey > a.pkey)
and a.season <> 'c'
group by a.season, a.fromdate) d
group by d.season, d.todate
Maybe a bit of explanation :
1. First step is ordering the recordset on fromdate and season. I've used a temptable to add the identity column (ordered!), but you can just as well use a comparison on fromdate instead of the temptable.
2. Next step (subquery in the FROM clause) is selecting all the combinations of the continous times. This gives you a bunch of wrong records, so we'll have to eliminate this.
3. Adding a group by on season and fromdate, just selecting the max of todate. This eliminates the first half of the doubles.
4. Adding a group by on season and todate, selecting the min of fromdate eleminates the second half of the doubles.
, but below you can find a set-based approach.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply