Re: need help with SQL query

AK,

Thanks for the response. I am new to SQL and your code was a little
over my head but I tried to work with it. I ended up creating 3 stored
procedures and one custom function, but I don't know how to call the
procedures to get the data. Did I do it right and what else do I need
to do?

Thanks.

AK wrote:
> although there is a pure SQL solution, solving such problems is a snap
> using OLAP aka analytical functions. On SQL 2000 they are not
> available, but there is a substitute
>
> ---- set up data
> create table t(n int, d smalldatetime)
> insert into t values(1, '1/1/2005')
> insert into t values(1, '1/2/2005')
> insert into t values(1, '1/9/2005')
> insert into t values(1, '1/14/2005')
> insert into t values(2, '1/21/2005')
> insert into t values(2, '1/23/2005')
> go
> create function dbo.t_with_rn()
> returns @a table(n int, d smalldatetime, rn int)
> as
> begin
> declare a_cur cursor
> local
> for select n,d from t order by n,d
>
> declare @n int
> declare @d smalldatetime
> declare @rn int
>
> set @rn=0
>
> open a_cur
>
> fetch next from a_cur into @n, @d
>
> while @@fetch_status=0
> begin
> set @rn = @rn + 1
>
> insert into @a values(@n, @d, @rn)
>
> fetch next from a_cur into @n, @d
> end
> return
> end
> go
> select * from dbo.t_with_rn()
> ---------this function will assign consecutive numbers to rows
> n d rn
>
> ----------- ------------------------------------------------------
> -----------
> 1 2005-01-01 00:00:00 1
> 1 2005-01-02 00:00:00 2
> 1 2005-01-09 00:00:00 3
> 1 2005-01-14 00:00:00 4
> 2 2005-01-21 00:00:00 5
> 2 2005-01-23 00:00:00 6
>
> go
> select left_ends.n, left_ends.d left_end, right_ends.d right_end from
> dbo.t_with_rn() left_ends,
> dbo.t_with_rn() right_ends
> where left_ends.n = right_ends.n
> and left_ends.rn = right_ends.rn - 1
> go
> ---------- here are the intervals
> n left_end
> right_end
> ----------- ------------------------------------------------------
> ------------------------------------------------------
> 1 2005-01-01 00:00:00
> 2005-01-02 00:00:00
> 1 2005-01-02 00:00:00
> 2005-01-09 00:00:00
> 1 2005-01-09 00:00:00
> 2005-01-14 00:00:00
> 2 2005-01-21 00:00:00
> 2005-01-23 00:00:00
>
>
> ---- clean up
> drop function dbo.t_with_rn
> go
> select * from t
> drop table t

.

Relevant Pages

  • Re: From varchar(max) to xml
    ... the "syscomments" references will need to be changed for SQL 2005 to ... ADD CONSTRAINT PK_Numbers PRIMARY KEY ... CREATE TABLE TestInput(INT PRIMARY KEY, ... DECLARE @temp_str VARCHAR ...
    (microsoft.public.sqlserver.xml)
  • Re: From varchar(max) to xml
    ... this thing together on SQL 2000. ... ADD CONSTRAINT PK_Numbers PRIMARY KEY ... CREATE TABLE TestInput(INT PRIMARY KEY, ... DECLARE @temp_str VARCHAR ...
    (microsoft.public.sqlserver.xml)
  • Re: how to use datetime range in prepare statement
    ... Hi David, ... coming from SQL Server and is complaining aboutPerhaps you could use SQL ... declare @P1 int ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: MSSQL$SBSMONITORING - problem since KB948110 install
    ... Many many thanks for posting a reply, running the SQL command in SQL ... DECLARE @work_to_do TABLE ( ... , indexid int ... DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do ...
    (microsoft.public.windows.server.sbs)
  • Re: Parameter Sniffing?
    ... I fixed a similar problem by, instead of using DECLARE for *any* local ... -- smalldatetime will likely work better ... > DDL and Data would be hard to provide, but here some SQL broken down to ... > INNER JOIN invoice_line IL1 (nolock) ...
    (microsoft.public.sqlserver.programming)