November 2, 2011 at 8:21 am
Hi,
My requirement is as given below:
My table contains three columns:
start_date,id, prev_date
--------------------------------
1-NOV-2011,1,1-NOV-2010
1-NOV-2010,2,1-NOV-2009
1-NOV-2009,3,1-NOV-2008
1-NOV-2008,3,1-NOV-2007
2-OCT-2010,4,3-OCT-2009
From the above data, i need the below as output:
start_date,id,start_date1,id1,start_date2,id2
--------------------------------------------------------------
1-NOV-2011,1,1-NOV-2010,2,1-NOV-2009,3
The logic is,first i need to filter out the records whose start_date is in current year.For those records, i need to match the prev_date with the start_date of remaining columns.
I need to bring those in same record.
i dont want to compare more than 2 hierarchies.
Thats why the result doesn't contain 2008 record even though the matching start date is there.
Please help me
Thanks
Thanks,
Pandeeswaran
November 2, 2011 at 8:28 am
Something like this?
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(StartDate DATE,
ID INT,
PrevDate DATE) ;
INSERT INTO #T
(StartDate, ID, PrevDate)
VALUES ('1-NOV-2011', 1, '1-NOV-2010'),
('1-NOV-2010', 2, '1-NOV-2009'),
('1-NOV-2009', 3, '1-NOV-2008'),
('1-NOV-2008', 3, '1-NOV-2007'),
('2-OCT-2010', 4, '3-OCT-2009') ;
SELECT *
FROM #T
CROSS APPLY (SELECT TOP 1
*
FROM #T AS T2
WHERE T2.StartDate = #T.PrevDate
ORDER BY T2.StartDate DESC) AS PrevYear
WHERE DATEPART(YEAR, #T.StartDate) = DATEPART(YEAR, GETDATE()) ;
I'm not really clear on your specifications. What does "filter out" mean here? It would usually mean "exclude" those rows, but the following sentence seems to indicate it means "include" those rows. But I could easily be reading it wrong.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply