SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with date query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

thirty
Starting Member

6 Posts

Posted - 12/03/2012 :  11:47:10  Show Profile  Reply with Quote
Hi

I have 4 date fields (DATE1, DATE2, DATE3, DATE4 for example).

How do I select the greatest (i.e. latest of these 4 date fields) in an efficent and easy to read manner?

I only want to return the greatest date not the greatest date and the other dates in order.

Thanks in advance.

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/03/2012 :  11:54:06  Show Profile  Reply with Quote
You can use UNPIVOT operator like in the example below - I am assuming rowid to be representative of other columns in your table
SELECT 
	rowid, 
	MAX(dt) AS maxdate
FROM 
	YourTable 
UNPIVOT(dt FOR dates IN (date1,date2,date3,date4))U
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/03/2012 :  12:14:31  Show Profile  Reply with Quote
if over 2008 you can use this too

SELECT rowid,
MAX(n) AS MaxDate
FROM table t
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
GROUP BY rowid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4350 Posts

Posted - 12/03/2012 :  13:23:40  Show Profile  Reply with Quote
Here is a sligh variation on Visakh's that doesn't require a group by (not sure if you need a group by in your scenario or not):
SELECT
    RowID,
    (
        SELECT MAX(DateVal) 
        FROM (VALUES (Date1), (Date2), (Date3), (Date4)) AS DateList(Dateval)
    ) AS MaxDate
FROM @Foo
Go to Top of Page

thirty
Starting Member

6 Posts

Posted - 12/04/2012 :  03:09:49  Show Profile  Reply with Quote
thanks for the replies BUT these solutions are bringing me back the following:

stg_row_id MaxDate
1 04/01/2000
11 10/01/1988
21 07/01/1990
31 10/01/2006
41 09/01/2009
51 01/01/2011
61 03/01/2011
71 10/01/2011
81 10/01/2011
91 10/01/2012

I only want 1 value returned after I calculate which is the greatest date of 4 date fields for a row in a table.

Example:
TABLE DATES
DATE1 DATE2 DATE3 DATE4
3/5/1998 7/8/1997 8/9/1999 1/2/2000

For the above example I only want the greatest date returned (i.e. 1/2/2000) ??

Edited by - thirty on 12/04/2012 03:10:36
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/04/2012 :  03:37:03  Show Profile  Reply with Quote
[code]
SELECT MAX(n) AS MaxDate
FROM YourTable t
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
[/ode]

--
Chandu
Go to Top of Page

thirty
Starting Member

6 Posts

Posted - 12/04/2012 :  03:57:50  Show Profile  Reply with Quote
thanks.........

if i am using this logic in an INSERT, how will it differ?

example

INSERT INTO policy_t
(
pol_stat_eff_dt
)
SELECT
CASE WHEN CODE = 'A' THEN
(SELECT MAX(n) AS MaxDate
--FROM datetable
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
END)
FROM datetable

?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/04/2012 :  04:11:02  Show Profile  Reply with Quote

Edit: replace @DATES with your table name

INSERT INTO policy_t
(
pol_stat_eff_dt
)
SELECT CASE WHEN CODE = 'A' THEN MAX(n) END AS MaxDate
FROM @DATES t
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
GROUP BY code

This code also inserts NULL in the case of CODE value != 'A' ( i.e. other than 'A')

So if u want to avoid NULL row,

INSERT INTO policy_t
(
pol_stat_eff_dt
)
SELECT MAX(n) AS MaxDate
FROM @DATES t
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
WHERE code = 'A'



--
Chandu

Edited by - bandi on 12/04/2012 04:11:47
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 12/04/2012 :  06:40:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer
http://beyondrelational.com/modules/2/blogs/70/posts/10905/interesting-enhancements-to-the-values-clause-in-sql-server-2008.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

thirty
Starting Member

6 Posts

Posted - 12/04/2012 :  09:32:52  Show Profile  Reply with Quote
I still have a problem!

I need to bring back the greatest date from the row I am inserting into policy_t from staging_table. I don't want to insert the greatest date from another table.

staging_table has 6 fields: stg_row_id, code, date1, date2, date3, date4

I want to insert into policy_t.........the code field and the greatest of date1, date2, date3, date4 for the same row.

I have commented out staging_table because that will give me a different answer?
But now syntax is not right, what should I do?

INSERT INTO policy_t
(
code
,pol_stat_eff_dt
)
SELECT
code
,CASE WHEN CODE = 'A' THEN
(SELECT MAX(n) AS MaxDate
--FROM staging_table
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
END)
FROM staging_table
Go to Top of Page

thirty
Starting Member

6 Posts

Posted - 12/04/2012 :  15:56:53  Show Profile  Reply with Quote
I have actually developed this query as follows, but not I get the following error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT
AD.admin_typ_cd
,'GENESIS'
,'GENESIS'
,PO.pol_stat_typ_cd
,SP.spsr_id
,'SUNLIFE'
,CASE STG.de_iss_co_gaaa
WHEN '1' THEN 'SLOC'
WHEN '4' THEN 'SLUS'
WHEN '5' THEN 'SLNY'
ELSE 'SLUS'
END
,STG.num_pol_gaaa
,STG.dte_pol_eff_gaaa
,NULL
-- NOTE FOR CASE STATEMENT BELOW:
-- dte_last_pol_cncl_gaaa if cde_pol_stat_gaaa in (‘C’, ‘D’, ‘N’).
-- Greater of dte_renw_eff_gaaa or dte_last_pol_reinst_gaaa
-- or dte_pol_eff_gaaa if cde_pol_stat_gaaa = ‘A’ (THE MAX OF 3 DATE FIELDS).
-- dte_pol_eff_gaaa if cde_pol_stat_gaaa = B.
-- Otherwise this will be greatest of dte_last_pol_reinst_gaaa, dte_last_pol_cncl_gaaa, dte_renw_eff_gaaa,
-- dte_pol_eff_gaaa. (THE MAX OF 4 DATE FIELDS).
-- If no date available, use last update date.
,CASE WHEN STG.cde_pol_stat_gaaa IN ('C','D','N') THEN STG.dte_last_pol_cncl_gaaa
ELSE
---- greatest of 3 fields
CASE WHEN STG.cde_pol_stat_gaaa ='A' THEN
(SELECT MaxDate FROM stg_GENESIS_gaaa_t a
LEFT JOIN (SELECT MAX(convert(date,CASE WHEN n = '00/00/0000' THEN '01/01/1900' ELSE N END)) AS MaxDate,num_pol_gaaa
FROM stg_GENESIS_gaaa_t
CROSS APPLY (VALUES (dte_last_pol_reinst_gaaa),(dte_renw_eff_gaaa),(dte_pol_eff_gaaa)) m(n)
GROUP BY num_pol_gaaa) b
on a.num_pol_gaaa = b.num_pol_gaaa)
ELSE
CASE WHEN STG.cde_pol_stat_gaaa ='B' THEN STG.dte_pol_eff_gaaa
ELSE
---- greatest of 4 fields
CASE WHEN STG.cde_pol_stat_gaaa NOT IN ('C','D','N','A','B') THEN
(SELECT MaxDate FROM stg_GENESIS_gaaa_t a
LEFT JOIN (SELECT MAX(convert(date,CASE WHEN n = '00/00/0000' THEN '01/01/1900' ELSE N END)) AS MaxDate,num_pol_gaaa
FROM stg_GENESIS_gaaa_t
CROSS APPLY (VALUES (dte_last_pol_cncl_gaaa),(dte_last_pol_reinst_gaaa),(dte_renw_eff_gaaa),(dte_pol_eff_gaaa)) m(n)
GROUP BY num_pol_gaaa) b
on a.num_pol_gaaa = b.num_pol_gaaa)
ELSE
CASE WHEN STG.dte_last_pol_cncl_gaaa IS NULL OR STG.dte_last_pol_reinst_gaaa IS NULL OR STG.dte_renw_eff_gaaa IS NULL OR
STG.dte_pol_eff_gaaa IS NULL THEN (SELECT CONVERT(VARCHAR(10),GETDATE(),111))
END
END
END
END
END
--,NULL
,NULL
,NULL
,'N'
,NULL
,GETDATE()
,SUSER_SNAME()
,'N'
,NULL
FROM stg_GENESIS_gaaa_t STG
INNER JOIN sponsor_t SP
ON STG.num_pol_gaaa = SP.src_spsr_id
AND SP.src_sys_cd = 'GENESIS'
LEFT OUTER JOIN admin_type_trnsl_t AD
ON STG.cde_admin_clrk_gaaa = AD.src_admn_typ_cd
AND AD.src_sys_cd = 'GENESIS'
INNER JOIN policy_status_type_trnsl_t PO
ON STG.cde_pol_stat_gaaa = PO.src_pol_stat_typ_cd
AND AD.src_sys_cd = 'GENESIS'

Can anyone see a problem in the code?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  02:29:31  Show Profile  Reply with Quote

GO
CREATE FUNCTION GreatestDate (@date1 date, @date2 date, @date3 date, @date4 date)
RETURNS DATE
BEGIN
	DECLARE @maxDate DATE 
	DECLARE @DATES TABLE(date1 date, date2 date, date3 date, date4 date)
	INSERT INTO @DATES VALUES(@date1, @date2, @date3, @date4)
		
	SELECT @maxDate = MAX(dt) 
	FROM @DATES
	UNPIVOT(dt FOR dates IN (date1,date2,date3, date4))U
	RETURN @maxDate
END

-- Call the above function in CASE statement 
--Example
DECLARE @DATES1 TABLE(DATE1 date, DATE2 date, DATE3 date, DATE4 date, code char(4))
insert into @DATES1 values('3/5/1998' , '7/8/1997', '8/9/1999' ,'1/2/2000', 'A'), ('10/5/1998' , '7/9/1997', '8/9/1999' ,'11/2/2010', 'B')

SELECT CASE WHEN code = 'A' THEN [dbo].[GreatestDate](DATE1, DATE2, DATE3, NULL)
			WHEN code = 'B' THEN [dbo].[GreatestDate](DATE1, DATE2, DATE3, DATE4)
 end
FROM @DATES1


-----------------------------------
For your scenario,
-- NOTE FOR CASE STATEMENT BELOW:
-- dte_last_pol_cncl_gaaa if cde_pol_stat_gaaa in (‘C’, ‘D’, ‘N’).
-- Greater of dte_renw_eff_gaaa or dte_last_pol_reinst_gaaa
-- or dte_pol_eff_gaaa if cde_pol_stat_gaaa = ‘A’ (THE MAX OF 3 DATE FIELDS).
-- dte_pol_eff_gaaa if cde_pol_stat_gaaa = B.
-- Otherwise this will be greatest of dte_last_pol_reinst_gaaa, dte_last_pol_cncl_gaaa, dte_renw_eff_gaaa,
-- dte_pol_eff_gaaa. (THE MAX OF 4 DATE FIELDS).
-- If no date available, use last update date.

,CASE
	WHEN STG.cde_pol_stat_gaaa IN ('C','D','N') THEN STG.dte_last_pol_cncl_gaaa
	WHEN STG.cde_pol_stat_gaaa ='A' THEN GreatestDate(dte_renw_eff_gaaa,dte_last_pol_reinst_gaaa,dte_pol_eff_gaaa, null)
	WHEN STG.cde_pol_stat_gaaa ='B' THEN dte_pol_eff_gaaa
	WHEN STG.cde_pol_stat_gaaa NOT IN ('C','D','N', 'A', 'B') THEN GreatestDate(dte_last_pol_reinst_gaaa,dte_last_pol_cncl_gaaa,dte_renw_eff_gaaa,dte_pol_eff_gaaa)
	ELSE ................. END

--
Chandu

Edited by - bandi on 12/05/2012 06:26:49
Go to Top of Page

thirty
Starting Member

6 Posts

Posted - 12/05/2012 :  07:51:17  Show Profile  Reply with Quote
thanks everyone for ur help
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/05/2012 :  08:16:16  Show Profile  Reply with Quote
quote:
Originally posted by thirty

thanks everyone for ur help


Got Solution correctly...............

Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000