Need help to code in different row data

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)

Need help to code in different row data

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2011-02-09 : 12:17:28

I need to move Excel data to SQL. In Excel, it is easier to calculate duration:
If MEMBID is the same, make first row as null, second row duration=[second row].[DATEFROM]-[first row].[DATETO].
For example,
MEMBID=06000031,
first row duration=null,
second row duration=[05/30/2007]-[05/09/2007] = 21
third row duration=[07/22/2009]-[06/04/2007]=779
fourth row duration=[04/09/2010]-[07/24/2009]=259
and so on...

How to code in SQL is really hard for me. Can someone help me?

MEMBID DATEFROM DATETO duration
------------------------------------------
06000031 05/04/2007 05/09/2007
06000031 05/30/2007 06/04/2007 21
06000031 07/22/2009 07/24/2009 779
06000031 04/09/2010 04/11/2010 259
06000031 05/28/2010 05/30/2010 47
06000145 06/09/2007 06/21/2007
06000145 06/27/2007 07/20/2007 6
06000145 08/07/2007 09/06/2007 18
06000145 09/29/2007 10/05/2007 23
06000145 10/23/2007 10/30/2007 18
06000145 10/30/2007 11/02/2007 0
06000145 05/28/2008 05/30/2008 208
06000145 08/09/2009 08/18/2009 436

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-09 : 14:15:51

Here's one way: (which assumes your table order is determined by the dates in the table.


--Your Table with your Sample data
declare @t table (MEMBID int, DATEFROM datetime, DATETO datetime, duration int null)
insert @t (membid, datefrom, dateto)
select 06000031, '05/04/2007', '05/09/2007' union all
select 06000031, '05/30/2007', '06/04/2007' union all 
select 06000031, '07/22/2009', '07/24/2009' union all
select 06000031, '04/09/2010', '04/11/2010' union all
select 06000031, '05/28/2010', '05/30/2010' union all
select 06000145, '06/09/2007', '06/21/2007' union all
select 06000145, '06/27/2007', '07/20/2007' union all
select 06000145, '08/07/2007', '09/06/2007' union all
select 06000145, '09/29/2007', '10/05/2007' union all
select 06000145, '10/23/2007', '10/30/2007' union all
select 06000145, '10/30/2007', '11/02/2007' union all
select 06000145, '05/28/2008', '05/30/2008' union all
select 06000145, '08/09/2009', '08/18/2009' 

--One possible solution
;with yak (membid, datefrom, dateto, seq)
as
(
       select membid
              ,datefrom
              ,dateto
              ,row_number() over (partition by membid order by datefrom)
       from   @t
)
select a.membid
       ,a.datefrom
       ,a.dateto
       ,datediff(day, b.dateto, a.datefrom) duration
from yak a
left outer join yak b 
       on b.membid = a.membid
       and b.seq + 1 = a.seq
order by a.membid
       ,a.seq

OUTPUT:
membid      datefrom                dateto                  duration
----------- ----------------------- ----------------------- -----------
6000031     2007-05-04 00:00:00.000 2007-05-09 00:00:00.000 NULL
6000031     2007-05-30 00:00:00.000 2007-06-04 00:00:00.000 21
6000031     2009-07-22 00:00:00.000 2009-07-24 00:00:00.000 779
6000031     2010-04-09 00:00:00.000 2010-04-11 00:00:00.000 259
6000031     2010-05-28 00:00:00.000 2010-05-30 00:00:00.000 47
6000145     2007-06-09 00:00:00.000 2007-06-21 00:00:00.000 NULL
6000145     2007-06-27 00:00:00.000 2007-07-20 00:00:00.000 6
6000145     2007-08-07 00:00:00.000 2007-09-06 00:00:00.000 18
6000145     2007-09-29 00:00:00.000 2007-10-05 00:00:00.000 23
6000145     2007-10-23 00:00:00.000 2007-10-30 00:00:00.000 18
6000145     2007-10-30 00:00:00.000 2007-11-02 00:00:00.000 0
6000145     2008-05-28 00:00:00.000 2008-05-30 00:00:00.000 208
6000145     2009-08-09 00:00:00.000 2009-08-18 00:00:00.000 436

Be One with the Optimizer
TG

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2011-02-09 : 15:00:34

Thank you TG but I just realize that our company is using SQL 2000 which do not have ROW_NUMBER function.
Is there another way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-09 : 15:27:54

I have moved your topic to the 2000 forum. Please post in the appropriate forum going forward to avoid wasting people's time with code that won't work on your platform.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-02-10 : 10:35:56

>>Is there another way?
Another way for sql200 is to create a #temp table with an identity column and insert your rows into it ordered by fromdate. Then use your identity column in place of the row_number().


create table #t (MEMBID int, DATEFROM datetime, DATETO datetime, duration int null, seq int identity(1,1))

insert #t (membid, datefrom, dateto)
select membid, datefrom, dateto from [YourTable] order by membid, datefrom

select a.membid
       ,a.datefrom
       ,a.dateto
       ,datediff(day, b.dateto, a.datefrom) duration
from #t a
left outer join #t b 
       on b.membid = a.membid
       and b.seq + 1 = a.seq
order by a.membid
       ,a.seq

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources