Tilted Forum Project Discussion Community - View Single Post - [SQL]

leftwingx · 05-26-2006, 11:23 AM

To clarify further my code in SQL using memory tables would look like this (note: I'm using the @dates mem table to simulate your actual data table):

Code:

DECLARE @dates TABLE (OrigDate SMALLDATETIME)

INSERT INTO @dates (OrigDate) VALUES ('01/01/2003')
INSERT INTO @dates (OrigDate) VALUES ('01/15/2003')
INSERT INTO @dates (OrigDate) VALUES ('02/26/2003')
INSERT INTO @dates (OrigDate) VALUES ('02/27/2003')

DECLARE @d1 TABLE (ID INT IDENTITY, Date1 SMALLDATETIME)
DECLARE @d2 TABLE (ID INT IDENTITY, Date2 SMALLDATETIME)

INSERT INTO @d1 (Date1)
SELECT OrigDate [d1] FROM @dates WHERE OrigDate NOT IN (SELECT MIN(OrigDate) FROM @dates)
INSERT INTO @d2 (Date2)
SELECT OrigDate [d2] FROM @dates WHERE OrigDate NOT IN (SELECT MAX(OrigDate) FROM @dates)

SELECT *
FROM @dates
WHERE OrigDate IN (
			SELECT TOP 1
				Date1
			FROM @d1 t1
				INNER JOIN @d2 t2
					ON t1.ID = t2.ID
			WHERE DATEDIFF(DAY,Date2,Date1) > 30
			ORDER BY Date1 DESC)