I need to return the MAX date either of these 3 co

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 2005 Forums

Transact-SQL (2005)

I need to return the MAX date either of these 3 co

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-03-02 : 17:20:31

I need to return the MAX date either of these 3 columns below. Thank you so you in advance.
Below is the rules and desire results.

IF OBJECT_ID('T1', 'u') IS NOT NULL
DROP TABLE T1
GO
CREATE TABLE [dbo].[T1]
(
[LoanNum] [varchar](10) NOT NULL,
[OfferPrice] [money] NULL,
[MgrApprDate] [datetime] NOT NULL,
[DirApprDate] [datetime] NOT NULL,
[ExecApprDate] [datetime] NULL,
[ExitStrategyMainCategoryID] [int] NULL,
[ExitStrategySubCategory1ID] [int] NULL
)
GO

INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 250000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 250000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 243325.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 245000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 245000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 245000.00, CONVERT(DATETIME, 0x00009cd100d30f24), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 250000.00, CONVERT(DATETIME, 0x00009cd30130635e), CONVERT(DATETIME, 0x00009cd400d2017a), CONVERT(DATETIME, 0x00009cd5010dfd30), 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('100993', 250000.00, CONVERT(DATETIME, 0x00009cff00deb976), CONVERT(DATETIME, 0x00009d0500c44600), CONVERT(DATETIME, 0x00009d050116c730), 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 370000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 328000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 370000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 415000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 415000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 385000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 390000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 413500.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 413500.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x00009c87009523b0), 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 410000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x00009c6100d6f0ae), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 425000.00, CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x00009c78010c7d5a), NULL, 4, 4)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 330000.00, CONVERT(DATETIME, 0x00009bed010a7d85), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES ('102930', 330000.00, CONVERT(DATETIME, 0x00009c1500786979), CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
go

SELECT *
FROM T1
ORDER BY LoanNum ASC, MgrApprDate ASC, DirApprDate ASC, ExecApprDate ASC;
go

-- business rules: Return the MAX date either from any of these MgrApprDate, DirApprDate, ExecApprDate columns

-- Desire result:
LoanNum OfferPrice MgrApprDate DirApprDate ExecApprDate ExitStrategyMainCategoryID ExitStrategySubCategory1ID
---------- --------------------- ----------------------- ----------------------- ----------------------- -------------------------- --------------------------
100993 250000.00 2010-01-15 13:30:54.900 2010-01-21 11:54:36.587 2010-01-21 16:55:00.000 4 4
102930 330000.00 2009-05-26 07:18:24.403 1900-01-01 00:00:00.000 NULL 2 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-02 : 18:08:40

Can you explain the logic a bit more? I only get one row back, but I must be missing something.. (quick and dirty)

SELECT *
FROM T1
WHERE
(
	SELECT
		CASE 
			WHEN MAX(MgrApprDate) >= MAX(DirApprDate) AND MAX(MgrApprDate) >= MAX(ExecApprDate) 
				THEN MAX(MgrApprDate)
			WHEN MAX(DirApprDate) >  MAX(MgrApprDate) AND  MAX(DirApprDate) >= MAX(ExecApprDate) 
				THEN MAX(DirApprDate)
			ELSE MAX(ExecApprDate)
		END 
	FROM T1
) IN (MgrApprDate, DirApprDate, ExecApprDate)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-02 : 22:17:30

This is probably the simplest way:

SELECT 
	MaxDate =
		(
		select max(b.x)
		from
			(
			select x = a.MgrApprDate union all 
			select x = a.DirApprDate union all
			select x = a.ExecApprDate
			) b
		where
			b.x is not null
		)		
FROM
	T1 a

If you want to use a case statement, that also works, but the code is complex, especially if any of the columns are nullable.

Much more on this subject on this script library thread, and examples of both:
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources