GET First 3 dates in 3 columns

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

SQL Server Development (2000)

GET First 3 dates in 3 columns

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-11 : 15:15:16

I have a TABLE WHICH IS LIKE

CREATE TABLE #t1
(PERSONID INT, A1DATE DATETIME, A2DATE DATETIME,A3DATE DATETIME,A4DATE DATETIME,A5DATE DATETIME,
A6DATE DATETIME,A7DATE DATETIME, A8DATE DATETIME, A9DATE DATETIME)

INSERT INTO #t1
(PERSONID, A1DATE,A2DATE,A3DATE)
VALUES
(111, '2/1/2008','3/1/2008','4/1/2008')

INSERT INTO #t1
(PERSONID, A3DATE,A5DATE,A7DATE)
VALUES
(222, '2/1/2008','2/1/2008','2/1/2008')

INSERT INTO #t1
(PERSONID, A8DATE)
VALUES
(333, '10/1/2008')

INSERT INTO #t1
(PERSONID, A3DATE,A5DATE,A7DATE,A9DATE)
VALUES
(444, '11/1/2008','3/1/2008','10/1/2008','5/1/2008')

INSERT INTO #t1
(PERSONID, A6DATE,A8DATE)
VALUES
(555, '10/1/2007','4/1/2008')

Now I need to return data in 4 columns. Which are

PersonID, 1stDate, 2nDate, 3rdDate

Each Person can have 1 to 9 dates in the 9 date columns. And in my result I have 3 columns which should display any 3 dates for each person. If the person has 1 date then just 1st date, if 2 then 1stdate and 2nd Date.

The result required is like this

PersonID, 1stDate, 2nDate, 3rdDate
111,2008-02-01,2008-03-01,2008-04-01
222,2008-02-01 ,2008-02-01 ,2008-02-01
333,10/1/2008,null,null
444,11/1/2008,3/1/2008,10/1/2008
555,10/1/2007,4/1/2008, null

The dates can be in any order. Most persons have upto 3 dates so this is the reason the output is required like this.

-----------------------------------------------------------------------------------------------
Ashley Rhodes

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-11 : 16:11:46

The query should return max 3 date columns or that depends on availability of data? coz for 444 I can see 4 dates..

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2008-07-11 : 16:23:30

Any 3 dates will do.
Max should be fine as well.

-----------------------------------------------------------------------------------------------
Ashley Rhodes

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-11 : 21:06:06

Hope this helps you:-
CREATE TABLE #t2 (Id1 int identity(1,1), PersonID int, Date1 datetime)
INSERT INTO #t2
SELECT PersonID, Date1 FROM
(
SELECT PersonID, A1DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A2DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A3DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A4DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A5DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A6DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A7DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A8DATE Date1 FROM #t1 UNION ALL
SELECT PersonID, A9DATE Date1 FROM #t1
)T
WHERE Date1 IS NOT NULL
ORDER BY PersonID

SELECT
t1.PersonID,
t1.Date1 A1Date,
t2.Date1 A2Date,
t3.Date1 A3Date
FROM
(SELECT TOP 100 PERCENT * FROM #t2 t WHERE id1 = (SELECT TOP 1 id1 FROM #t2 tInner WHERE PersonID = t.PersonID ORDER BY id1)) t1
LEFT OUTER JOIN (SELECT TOP 100 PERCENT * FROM #t2 t WHERE id1 = (SELECT TOP 1 id1 FROM #t2 tInner WHERE PersonID = t.PersonID AND tInner.id1 <> (SELECT TOP 1 id1 FROM #t2 tInner WHERE PersonID = t.PersonID ORDER BY id1)) ORDER BY id1) t2 ON t1.PersonID = t2.PersonID
LEFT OUTER JOIN (SELECT TOP 100 PERCENT * FROM #t2 t WHERE id1 = (SELECT TOP 1 id1 FROM #t2 tInner WHERE PersonID = t.PersonID AND tInner.id1 NOT IN (SELECT TOP 2 id1 FROM #t2 tInner WHERE PersonID = t.PersonID ORDER BY id1)) ORDER BY id1) t3 ON t1.PersonID = t3.PersonID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources