Hi everyone,
I am having problem/troubles with creating an sql inline statement for our application. How do I create a statement/query that selects list of servers with defined date ranges, but once a server of the same name has date ranges within the date range of another, I will have to get the date range that contains them all. For easy understanding here's a snapshot of data (the Before and After screenshots).
BEFORE:
Id ServerName DateFrom DateTo
1 APAXSG1 01/01/2007 01/31/2007
2 APAXSG1 01/15/2007 02/15/2007
3 APAXSG1 02/01/2007 05/31/2007
4 APAXSG1 06/01/2007 07/01/2007
5 APAXSG1 07/02/2007 07/31/2007
6 APAXSG2 04/01/2007 05/01/2007
7 APAXSG2 04/15/2007 06/30/2007
8 APAXSG2 08/25/2007 09/25/2007
9 APAXSG3 09/01/2007 10/01/2007
10 APAXSG3 11/01/2007 12/25/2007
AFTER:
Id ServerName DateFrom DateTo
1 APAXSG1 01/01/2007 05/31/2007
2 APAXSG1 06/01/2007 07/01/2007
3 APAXSG1 07/02/2007 07/31/2007
4 APAXSG2 04/01/2007 06/30/2007
5 APAXSG2 08/25/2007 09/25/2007
6 APAXSG3 09/01/2007 10/01/2007
7 APAXSG3 11/01/2007 12/25/2007
I was helped by a friend and suggested the following statement to me:
DECLARE @Source TABLE (Id INT, ServerNm VARCHAR(100), DateFrom DATETIME, DateTo DATETIME)
INSERT INTO @Source SELECT * FROM dbo.NormalizationTable
DECLARE @Target TABLE (ServerNm VARCHAR(100), DateFrom DATETIME, DateTo DATETIME)
DECLARE @ServerNm VARCHAR(100), @DateFrom DATETIME, @DateTo DATETIME
DECLARE normalize CURSOR FOR
SELECT ServerNm, DateFrom, DateTo
FROM @Source
ORDER BY DateFrom, DateTo
OPEN normalize
FETCH NEXT FROM normalize INTO @ServerNm, @DateFrom, @DateTo
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(*)
FROM @Target
WHERE ServerNm = @ServerNm
AND (DateFrom BETWEEN @DateFrom AND @DateTo OR DateTo BETWEEN @DateFrom AND @DateTo)) = 0
INSERT INTO @Target VALUES (@ServerNm, @DateFrom, @DateTo)
ELSE
UPDATE @Target
SET DateFrom = CASE WHEN DateFrom < @DateFrom
THEN DateFrom
ELSE @DateFrom END,
DateTo = CASE WHEN DateTo > @DateTo
THEN DateTo
ELSE @DateTo END
WHERE ServerNm = @ServerNm
AND (DateFrom BETWEEN @DateFrom AND @DateTo OR DateTo BETWEEN @DateFrom AND @DateTo)
FETCH NEXT
FROM normalize
INTO @ServerNm, @DateFrom, @DateTo
END
CLOSE normalize
DEALLOCATE normalize
SELECT * FROM @Target
ORDER BY ServerNm, DateFrom, DateTo
- - But upon researching, cursors can be performance issues esp for large data. How could I convert tha above statement to a non-cursor one?
Kindly help me.. thanks a lot!!