Smallest date in a row - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Smallest date in a row

Author

Topic

samneedshelp
Starting Member

9 Posts

Posted - 2009-11-30 : 23:18:37

Hi all,

I need to find the smallest date in each row and make a 2 column temp table consist of ID and the smallest date in SQL Server 2005.

RowA(ID1, D1, D2, D3, D4)
RowB(ID2, D5, D6, D7, D8)
RowC(ID3, D9, D10, D11, D12)

Makes the following

RowA(ID1, D2)
RowB(ID2, D8)
RowC(ID3, D12)

Any help or comments is appreciated.

Sam

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-01 : 00:55:51

Hi

These columns are datetime field?

D1, D2, D3, D4
D5, D6, D7, D8
D9, D10, D11, D12

-------------------------
R...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-01 : 01:11:01

Hi

You want this..


CREATE TABLE #DATE(ID INT IDENTITY(1,1),DATE1 DATETIME, DATE2 DATETIME, DATE3 DATETIME)

INSERT INTO #DATE
SELECT GETDATE()-3, GETDATE()-1, GETDATE()-2 


SELECT * FROM #DATE

SELECT ID, MIN(DATE)AS DATE FROM (
SELECT ID, DATE
FROM 
   (SELECT ID, DATE1, DATE2, DATE3
   FROM #DATE) P
UNPIVOT
   (DATE FOR SmallDate IN 
      (DATE1, DATE2, DATE3)
)AS UNPVT
) AS B
GROUP BY ID

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 01:41:26

Another way

select id, min(d1) as min_date from
(
select id,d1 from your_table
union all
select id,d2 from your_table
union all
select id,d3 from your_table
union all
select id,d4 from your_table
) as t
group by id

Madhivanan

Failing to plan is Planning to fail

samneedshelp
Starting Member

9 Posts

Posted - 2009-12-01 : 23:29:42

Hi rajdaksha and madhivanan,

Thank you very much for your awsome solutions and help. They both work very well.

Great job!

Cheers
Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources