tough SQL ask

[mrmookster]

I have a table with row data held as one row per edit, against each row is an edit date. querying this table would be much more efficient (i think) if i could use a join between dates, ie if the table had date_from and date_to columns to replace the edit_date colomn.

how do i go about transposing this data to a temp table or view ... i suspect it will be using cursors

ie go from
xxx,01/01/2001,yyy,zzz
xxx,10/01/2001,yyy,zzz
xxx,15/01/2001,yyy,zzz

to
xxx,01/01/2001,09/01/2001,yyy,zzz
xxx,10/01/2001,14/01/2001,yyy,zzz
xxx,15/01/2001,<today>,yyy,zzz

 

[r937]

cursor is a bad word around here

select r1.xxx
     , r1.editdate
     , coalesce(r2.editdate,getdate()) as editdate
     , r1.yyy
     , r1.zzz
  from datable as r1
left outer
  join datable as r2
    on r2.editdate =
       ( select min(editdate)
           from datable
          where editdate > r1.editdate )

r937.com | rudy.ca

 

[mrmookster]

thanks... but it doesnt quite work. and sorry can't figure out why.

1st table contains 43230 rows, query returns 830401 rows

 

[r937]

perhaps the reason you can't figure out why is because you're having trouble adapting the sql i posted to your actual situation...

... and the reason for that might be becuase you gave so very little information in your first post

r937.com | rudy.ca

 

[mrmookster]

sorry. i realise i gave too little info first time around.
the min(editdate) part will not work because edit dates are not neccessarily unique BUT a row_ino and edit_date will be

here is a 'real' data sample
row_uno m_uno edit_date x y z
1029 1162 1995-08-09 SEDI PROP HDIS
1030 1162 1996-11-14 SEDI ARCH DMB
1031 1163 1995-08-09 SEDI PROP HDIS
1032 1163 1996-11-14 SEDI ARCH DMB
1033 1164 1995-08-09 SEDI PROP HDIS
1034 1164 1996-11-14 SEDI ARCH DMB
1035 1173 1998-06-16 SGLA PROP PA
1036 1180 1987-01-21 SEDI PTTR MHR
1037 1180 1998-05-25 SEDI PTTR MHR
1038 1182 1995-08-09 SEDI PROP HDIS
1039 1182 1996-11-14 SEDI ARCH DMB
1040 1194 2000-03-29 SEDI LITI GZC
1041 1233 2000-06-07 SEDI COMM GHD
1042 1254 1999-12-21 SEDI CORF MG
1043 1263 2000-04-20 SEDI PTTR MHR

 

[Crowley16]

instead of using a subquery, try creating a ranked table, and then join on the ranks offset by one...

e.g.

;with rankingTable as (
   select ..., row_number() over( order by editDate, rowUno) rank
   from table
   order by editDate, rowUno
)
   select
      ...
   from
      rankingTable t1 inner join
      rankingTable t2 on t1.rank = t2.rank - 1

or something along those lines...
I've not tested this myself, but this should be quicker since it doesn't have to run a subquery for every record.

--------------------
Procrastinate Now!

 

[mrmookster]

looks interesting... but alas its for SQL Server 2005 ?
We're upgrading in the next few months, but for the time being we're still on SQL Server 2k.

 

[Crowley16]

in 2k you can use a temp table or table variable with an identity value...

in fact, you can in 2005 as well and I'm not sure which would be faster...

--------------------
Procrastinate Now!