Calculating Date Ranges - 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)

Calculating Date Ranges

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-04-19 : 14:02:33

Hello All.

I have a table with one column called [billdate]. THis table has 6 records as shown below.

BILLDATE
--------
4/5/2002
4/1/2002
3/1/2002
2/1/2002
1/3/2002
1/1/2002

I am trying to design a view that will show me the Starting and Ending dates of the corresponding Bill Date Ranges. I would like the view to look like this:

BILLDATE STARTING ENDING
-------- -------- ------
4/5/2002 4/2/2002 4/5/2002
4/1/2002 3/2/2002 4/1/2002
3/1/2002 2/2/2002 3/1/2002
2/1/2002 1/4/2002 2/1/2002
1/3/2002 1/2/2002 1/3/2002
1/1/2002 N/A 1/1/2002

I can grab the Ending date easily but I don't know how to calculate the STARTING field.

Any Ideas??

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-04-19 : 14:20:47

Not sure how optimized this would be, but I think it works.

SELECT aa.billdate, dateadd(dd,1,bb.START) Starting, aa.billdate Ending
FROM (Select Distinct Billdate
FROM billdate) aa LEFT JOIN
(SELECT a.billdate, Max(b.billdate) START
FROM BILLDATE a,
Billdate b
WHERE a.billdate > b.billdate
GROUP BY a.Billdate) bb
ON aa.billdate = bb.billdate

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources