Avoiding Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Avoiding Cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-14 : 06:58:01

Sachin writes "Hi,

I have a table say xy like this---

idcol ac1 ac2 dates(y/m/d)
------------------------------------------------------
1 a b 2005-01-01
2 a b 2005-01-02
3 a b 2005-01-03
4 a b 2005-01-06
5 a b 2005-01-08
6 a b 2005-01-09
7 a c 2005-01-01
8 a c 2005-01-02

(8 row(s) affected)

now i want to see the output as follows

ac1 ac2 FromDate ToDate
a b 2005-01-01 2005-01-03
a b 2005-01-06 2005-01-06
a b 2005-01-08 2005-01-09
a c 2005-01-01 2005-01-02

Can this be done without using a cursor.

DDL is as below
--------------------------------------------------------------

drop table xy
go
CREATE TABLE [xy] (
[idcol] [int] IDENTITY (1, 1) NOT NULL ,
[ac1] [varchar] (20) NULL ,
[ac2] [varchar] (20) NULL ,
[dates] [datetime] NULL
) ON [PRIMARY]
GO
truncate table xy
go
insert into xy
(
ac1,ac2,dates
)
select 'a' , 'b', '2005-01-01'
union all select 'a' , 'b' , '2005-01-02'
union all select 'a' , 'b' , '2005-01-03'
union all select 'a' , 'b' , '2005-01-06'
union all select 'a' , 'b' , '2005-01-08'
union all select 'a' , 'b' , '2005-01-09'
union all select 'a' , 'c' , '2005-01-01'
union all select 'a' , 'c' , '2005-01-02'

go

-------------------------------------------------------------"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-14 : 09:59:37

You have to tell us on what basis you want those result

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-14 : 10:08:01

Is it "continue the sequence" iq test?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 10:11:46

How about something like:

select identity(int,1,1) id,* 
into #a
from
(
	select a.* , isnull(datediff(d,b.dates,a.dates),0) diff
	from xy a
	left join xy b on b.ac1 = a.ac1 and b.ac2 = a.ac2 and b.idcol+1 = a.idcol
) d
where diff != 1

select a.ac1,a.ac2,a.dates fromDate,c.dates toDate
from #a a
left outer join #a b on b.id-1 = a.id
left outer join xy c on c.idcol = isnull(b.idcol-1,a.idcol+1)

drop table #a

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-14 : 12:36:12

I tried to make it readable, I failed miserably.

Nice ehorn!

drop table xy
go
CREATE TABLE [xy] (
[idcol] [int] IDENTITY (1, 1) NOT NULL ,
[ac1] [varchar] (20) NULL ,
[ac2] [varchar] (20) NULL ,
[dates] [datetime] NULL 
) ON [PRIMARY]
GO
truncate table xy
go
insert into xy 
(
ac1,ac2,dates
)
select 'a' , 'b', '2005-01-01'
union all select 'a' , 'b' , '2005-01-02'
union all select 'a' , 'b' , '2005-01-03'
--union all select 'a' , 'b' , '2005-01-04'

union all select 'a' , 'b' , '2005-01-06'

union all select 'a' , 'b' , '2005-01-08'
union all select 'a' , 'b' , '2005-01-09'

union all select 'a' , 'c' , '2005-01-01'
union all select 'a' , 'c' , '2005-01-02'

union all select 'a' , 'c' , '2005-01-03'
union all select 'a' , 'c' , '2005-01-04'

union all select 'f' , 'c' , '2002-01-07'
union all select 'f' , 'c' , '2002-01-08'
union all select 'f' , 'c' , '2002-01-09'
union all select 'f' , 'c' , '2002-01-10'


SELECT t.ac1,t.ac2,t.dates AS fromDate,MIN(t2.dates) AS toDate FROM
	xy t
	LEFT JOIN xy tb ON t.ac1 = tb.ac1 AND t.ac2 = tb.ac2 AND t.dates = tb.dates + 1
	LEFT JOIN xy ta ON t.ac1 = ta.ac1 AND t.ac2 = ta.ac2 AND t.dates = ta.dates - 1
	CROSS JOIN xy t2
		LEFT JOIN xy tb2 ON t2.ac1 = tb2.ac1 AND t2.ac2 = tb2.ac2 AND t2.dates = tb2.dates + 1
		LEFT JOIN xy ta2 ON t2.ac1 = ta2.ac1 AND t2.ac2 = ta2.ac2 AND t2.dates = ta2.dates - 1
WHERE
	t.ac1 = t2.ac1 AND t.ac2 = t2.ac2 AND t.dates <= t2.dates
	AND
	( (ta.dates IS NOT NULL AND tb.dates IS NULL) OR (ta.dates IS NULL AND tb.dates IS NULL) )
	AND
	( (ta2.dates IS NULL AND tb2.dates IS NOT NULL) OR (ta2.dates IS NULL AND tb2.dates IS NULL) )
GROUP BY
	t.ac1,t.ac2,t.dates
ORDER BY
	t.ac1,t.ac2,t.dates

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources