Sql Date Calculations - SQL Server Forums

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

Transact-SQL (2000)

Sql Date Calculations

Author

Topic

ekellysql
Starting Member

16 Posts

Posted - 2006-03-21 : 09:53:15

I need to select a set of records from a sql table ordered by date in descending order. When the records get returned I need to compare the date field in each record and get the number of days between each record. I then need to determine which date range is the largest.

e.g.
Record 1 has a date of today and record 2 is 5 days ago so that difference is 5. But then record 3 is 8 days prior to record 2 so the max range is now 8.

Anyone can help out with the syntax for that.

thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 10:18:43

Here is one way that assumes you have only the date column to determine sequence.


set nocount on
declare @tb table (dt datetime)
insert @tb
select dateadd(day, 0, getdate()) union all
select dateadd(day, 5, getdate()) union all
select dateadd(day, 13, getdate()) union all
select dateadd(day, 15, getdate()) 


select	dt
	,intervalFromPrevious = datediff(day, (select max(dt) from @tb where dt < a.dt), dt)
from	@tb a
order by dt desc


--I'm just using the same statement as a derived table to get the MaxInterval
select	max(interval) maxInterval
from	(
	select	interval = datediff(day, (select max(dt) from @tb where dt < a.dt), dt)
	from	@tb a
	) a


OUTPUT:

dt                                                     intervalFromPrevious 
------------------------------------------------------ -------------------- 
2006-04-05 10:16:37.433                                2
2006-04-03 10:16:37.433                                8
2006-03-26 10:16:37.433                                5
2006-03-21 10:16:37.433                                NULL

maxInterval 
----------- 
8

Be One with the Optimizer
TG

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-21 : 10:19:14

Go to Books OnLine & read about
-- Order By Desc
-- DateDiff
-- DateAdd

Or give some Sample Data

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-21 : 10:19:59

Something like this?

declare @t table (d datetime)

insert @t
      select '1 Jan 06'
union select '5 Jan 06'
union select '27 Jan 06'
union select '8 Jan 06'
union select '15 Jan 06'
union select '14 Jan 06'
union select '20 Jan 06'
union select '22 Jan 06'
union select '7 Jan 06'

declare @t2 table (id int identity(1, 1), d datetime)

insert into @t2 select d from @t order by d

select max(datediff(d, a.d, b.d)) from @t2 a inner join @t2 b on a.id = b.id - 1

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-21 : 10:29:31

From your example:

CREATE TABLE #Dates 
(  
   DateID INT IDENTITY(1, 1), 
   Date DATETIME
)

INSERT INTO #Dates
(
   Date
)
SELECT '21/Mar/2006' AS 'Date'
UNION SELECT '16/Mar/2006'
UNION SELECT '8/Mar/2006'

SELECT    dt.DateID, 
          dt.[Date], 
          dt.PreviousDate, 
          ABS(DATEDIFF(day, dt.[Date], dt.PreviousDate))
FROM      (
            SELECT    dt.DateID, 
                      dt.[Date], 
                      MAX(pd.[Date]) AS PreviousDate
            FROM      #Dates AS dt
            LEFT JOIN #Dates AS pd
            ON        dt.[Date] > pd.[Date]
            GROUP BY  d.DateID, 
                      d.[Date]
          ) AS dt
ORDER BY  dt.[Date] DESC

This is just to give you an idea of what it's doing. You could then just amend the query to select the max of the absolute date difference to get your greatest range.

Mark

ekellysql
Starting Member

16 Posts

Posted - 2006-03-21 : 12:17:31

Thanks for the help guys. I got it working.

ekellysql
Starting Member

16 Posts

Posted - 2006-03-21 : 12:18:31

I meant I got it working with the code snippets you posted!

Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources