October 6, 2004 at 3:53 pm
I need to find the date diff between the first and second , second and
third , third and fourth...... so on for the orderdate with the same Id.
Like this
Id ordate datediff
1 01/01/04 Null
1 01/03/04 2
1 01/06/04 3
1 01/07/04 1
1 01/15/04 8
I tried many ways, but it didn't work. Do you have any idea ?
October 8, 2004 at 1:02 am
I found 2 solutions. The first select will not give you the row with the null value, but should perform better, the second gives you the result you want :
use tempdb -- for testing
go
set nocount on
go
if object_id('dbo.ddiftbl') is not null drop table dbo.ddiftbl
go
create table dbo.ddiftbl
(
ID int not null,
ordate datetime
)
go
insert dbo.ddiftbl values ( 1 , '20040101' )
insert dbo.ddiftbl values ( 1 , '20040103')
insert dbo.ddiftbl values ( 1 , '20040106')
insert dbo.ddiftbl values ( 1 , '20040107')
insert dbo.ddiftbl values ( 1 , '20040115')
go
-- query 1 :
select D1.ID,D1.ordate,ddif=min(datediff(d,D2.ordate,D1.ordate))
from dbo.ddiftbl D1, dbo.ddiftbl D2
where D1.ID=D2.ID
and D2.ordate < D1.ordate
group by D1.ID,D1.ordate
go
-- query 2 :
select D1.ID,D1.ordate,ddif=
(
select TOP 1 datediff(d,D2.ordate,D1.ordate)
from dbo.ddiftbl D2
where D1.ID=D2.ID
and D2.ordate < D1.ordate
order by D2.ordate desc
)
from dbo.ddiftbl D1
order by D1.ID,D1.ordate
go
October 8, 2004 at 1:03 am
I think the easiest way is to work with a #table and an identity field.
Just take care that you have the correct sortorde.
create table #dates ( nline int identity (1,1), ordate datetime, datediffvalue int)
insert into #dates ( ordate )
values ( '01/01/2004' )
values ( '01/03/2004' )
values ( '01/06/2004' )
values ( '01/07/2004' )
values ( '01/15/2004' )
declare @nloop int,
@dStart datetime,
@dEnddatetime
select @nloop = ( select min(nline) from #dates )
while @nloop <= ( select max(nline) from #dates )
begin
select @dStart = ( select ordate from #dates where nline = @nloop )
select @dEnd = ( select ordate from #dates where nline = ( @nloop + 1 ) )
update #dates set datediffvalue = datediff (dd, @dStart, @dEnd )
where nline = @nloop + 1
select @nloop = @nloop + 1
end
October 8, 2004 at 1:26 am
Michael had a good point. But you can do it without a while loop. Using a temp table is fine, but can give you some performance problems when dealing with big tables ( a copy of the table will be stored in tempdb ). You can also use a table variable, but then you need to know all the columns and column types of the table ... :
use tempdb -- for testing
go
set nocount on
go
if object_id('dbo.ddiftbl') is not null drop table dbo.ddiftbl
go
create table dbo.ddiftbl
(
ID int not null,
ordate datetime
)
go
insert dbo.ddiftbl values ( 1 , '20040101' )
insert dbo.ddiftbl values ( 1 , '20040103')
insert dbo.ddiftbl values ( 1 , '20040106')
insert dbo.ddiftbl values ( 1 , '20040107')
insert dbo.ddiftbl values ( 1 , '20040115')
insert dbo.ddiftbl values ( 2 , '20040101' )
insert dbo.ddiftbl values ( 2 , '20040103')
insert dbo.ddiftbl values ( 2 , '20040106')
insert dbo.ddiftbl values ( 2 , '20040107')
insert dbo.ddiftbl values ( 2 , '20040115')
insert dbo.ddiftbl values ( 3 , '20040115')
insert dbo.ddiftbl values ( 3 , '20040116')
insert dbo.ddiftbl values ( 4 , '20040116')go
if object_id('tempdb..#tmp') is not null drop table #tmp
go
-- USING a #tmp table :
-- create a copy of the table with an identity column
select ident=identity(int,1,1),* into #tmp from dbo.ddiftbl order by ID,ordate
select D1.ID,D1.ordate,ddif=datediff(d,D2.ordate,D1.ordate)
from #tmp D1, #tmp D2
where D1.ID *=D2.ID
and D1.ident *= D2.ident+1
order by D1.ID,D1.ordate
go
-- USING a table variable :
declare @Tmp table (ident int identity(1,1),ID int not null,ordate datetime)
insert @Tmp ( ID,ordate) select * from dbo.ddiftbl order by ID,ordate
select D1.ID,D1.ordate,ddif=datediff(d,D2.ordate,D1.ordate)
where D1.ID *=D2.ID
and D1.ident *= D2.ident+1
order by D1.ID,D1.ordate
October 8, 2004 at 10:08 am
Thanks for all your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply