SQL Server Performance Forum – Threads Archive

Difference between successive rows

Hi,
I have a table called visitors that has the following records VisitDate visitorsCount
——————————
11/12/2004 19
11/13/2004 56
11/14/2004 147
11/15/2004 229
11/16/2004 310 I need to write a query that returns the result Range Visitors
——————————————————–
From 11/13/2004 to 11/12/2004 37
From 11/14/2004 to 11/13/2004 91
From 11/15/2004 to 11/14/2004 82
From 11/16/2004 to 11/15/2004 91 I used the following query
select ‘From ‘+ t2.VisitDate+’ to ‘+ t1.VisitDate as Range, t2.visitorsCount-t1.visitorsCount as Visitors from Visitors t1, Visitors t2
where (t2.VisitDate+1)>(t1.VisitDate)
but not working Is there a way to do this? Thanks in advance
Madhivanan

---

If subsequent rows all have a VisitDate that is one day after that on the previous row, then use
SELECT ‘From ‘+ t2.VisitDate+’ to ‘+ t1.VisitDate as Range, t2.visitorsCount-t1.visitorsCount as Visitors
FROM #Visitors t1, #Visitors t2
WHERE DATEADD(d, 1, t1.VisitDate) = t2.VisitDate
If you can’t rely on the date interval, then the easiest solution would be to create a temporary table with an identity column, and insert the data in the correct order. Then you can use this query:
SELECT ‘From ‘+ t2.VisitDate+’ to ‘+ t1.VisitDate as Range, t2.visitorsCount-t1.visitorsCount as Visitors
FROM #Visitors t1, #Visitors t2
WHERE t1.identitycolumn = t2.identitycolumn – 1

---

Here is one possible way… create table #t(
ddatetime,
iint
) set dateformat mdy
insert into #t values ( ’11/12/2004′, 19 )
insert into #t values ( ’11/13/2004′, 56 )
insert into #t values ( ’11/14/2004′, 147 )
insert into #t values ( ’11/15/2004′, 229 )
insert into #t values ( ’11/16/2004′, 310 ) select’from ‘ + convert( varchar(10), t3.d, 101 )
+ ‘ to ‘ + convert( varchar(10), t5.d, 101 ) range,
t3.i – t5.i visitors
from
#t t3
inner join( select
t1.d d1,
max( t2.d ) d2
from
#t t1
inner join #t t2
on t1.d > t2.d
group by t1.d ) t4
inner join #t t5
on t4.d2 = t5.d
on t3.d = t4.d1 drop table #t Cheers
Twan

---

Thanks to both Adriaan and Twan. Your ideas worked well. I changed Adriaan’s first method to the following to work without error SELECT ‘From ‘+ convert(nvarchar(30),t1.VisitDate)+’ to ‘+ convert(nvarchar(30),t2.VisitDate) as Range, t2.visitorsCount-t1.visitorsCount as Visitors
FROM Visitors t1, Visitors t2
WHERE DATEADD(d, 1, t1.VisitDate) = t2.VisitDate
Madhivanan

---

Madhivanan,<br /><br />Twan’s method is the one that you use in case you cannot rely on the one-day date interval – I was just being a little bit lazy. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

]]>