Is there w way to achieve following using an SQL 2000 query, i looked everywhere but could not find any working snippet.
I have contiguous date segments and requirement is to get the min effective date and maximum effective dates for each contiguous dates.
if that is not possible getting min effective date and max termdate for an contiguous segment using different queries will also work for me.
ID effdate termdate
1 2007-05-01 2007-05-31
2 2007-06-01 2007-06-30
3 2007-07-01 2007-09-30
4 2008-03-01 2008-03-31
5 2008-05-01 2008-05-31
6 2008-06-01 2008-06-30
Expected Result :
2007-05-01 2007-09-30
2008-03-01 2008-03-31
2008-05-01 2008-06-30
I did something like this to get the effdate and same for termdate, made them as two separate views and got the final result.
SELECT distinct e0.effdate,e0.ID
FROM dbo.datatable e0 LEFT OUTER JOIN dbo.datatable PREV ON
PREV.ID = e0.ID
AND PREV.termdate = DATEADD(dy, -1, e0.Effdate)
WHERE PREV.ID IS NULL
Unfortunately you're probably going to have to use a cursor. Something like this should work.
DECLARE @Results TABLE
(
effdate DATETIME,
termdate DATETIME
)
DECLARE @Date1 DATETIME,
@Date2 DATETIME,
@StartDate DATETIME,
@EndDate DATETIME
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD
FOR
SELECT effdate, termdate FROM <TABLE>
OPEN @Cursor
FETCH NEXT FROM @Cursor
INTO @Date1,@Date2
WHILE @@FETCH_STATUS = 0
BEGIN
IF @StartDate IS NULL
BEGIN
SELECT @StartDate = @Date1,
@EndDate = @Date2
END
ELSE
BEGIN
IF DateDiff(d,@EndDate,@Date1) = 1
BEGIN
SET @EndDate = @Date2
END
ELSE
BEGIN
INSERT INTO @Results
SELECT @StartDate, @EndDate
SELECT @StartDate = @Date1,
@EndDate = @Date2
END
END
FETCH NEXT FROM @Cursor
INTO @Date1,@Date2
END
INSERT INTO @Results
SELECT @StartDate, @EndDate
CLOSE @Cursor
DEALLOCATE @Cursor
SELECT * FROM @Results
select min(effdate),max(termdate) from tab1
where year(effdate) = year(termdate)
group by year(effdate)
order by min(effdate)
Result
2007-05-01 00:00:00.000 2007-09-30 00:00:00.000
2008-03-01 00:00:00.000 2008-06-30 00:00:00.000
I don't think this is possible without the use of a temporary table. (You don't exclude their use, but you didn't exclude using cursors either until Mike Bennett had posted his answer)
I'm reasonably confident that this is a generic solution - it uses an undocumented feature where it's possible to change the value of a variable more than once during an update statement.
You may be able to skip the creation of an artificial identity column to guarantee order (autoID in my query) if the records in your table are entered in order of effdate.
-- Setup test data
IF object_id('tempdb..#test1') IS NOT NULL
DROP TABLE #test1
GO
CREATE TABLE #test1
(id INT
,effdate DATETIME
,termdate DATETIME
)
INSERT #test1
SELECT 1,'2007-05-01','2007-05-31'
UNION SELECT 2 ,'2007-06-01','2007-06-30'
UNION SELECT 3 ,'2007-07-01','2007-09-30'
UNION SELECT 4 ,'2008-03-01','2008-03-31'
UNION SELECT 5 ,'2008-05-01','2008-05-31'
UNION SELECT 6 ,'2008-06-01','2008-06-30'
GO
IF object_id('tempdb..#t') IS NOT NULL
DROP TABLE #t
GO
-- Order the records by effdate
SELECT IDENTITY(INT,1,1) AS autoId
,cast(NULL AS INT) groupID
,*
INTO #t
FROM #test1
ORDER BY effdate
UPDATE #t
SET groupID = 1
WHERE autoID = 1
DECLARE @gp INT
SET @gp = 1
--update groupID using the undocumented variable-update method
UPDATE t2
SET @gp = CASE WHEN t1.termdate = t2.effdate - 1
THEN @gp
ELSE @gp + 1
END
,groupID = @gp
FROM #t AS t1
JOIN #t AS t2
ON t1.autoID = t2.autoID - 1
--output results
select min(effdate), max(termdate)
from #t
group by groupID
order by groupID
