| Author |
 Topic  |
|
|
thirty
Starting Member
6 Posts |
 Posted - 12/03/2012 : 11:47:10
|
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
5152 Posts |
Posted - 12/03/2012 : 11:54:06
|
You can use UNPIVOT operator like in the example below - I am assuming rowid to be representative of other columns in your tableSELECT
rowid,
MAX(dt) AS maxdate
FROM
YourTable
UNPIVOT(dt FOR dates IN (date1,date2,date3,date4))U |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 12/03/2012 : 12:14:31
|
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/
|
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 12/03/2012 : 13:23:40
|
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 |
|
|
|
thirty
Starting Member
6 Posts |
Posted - 12/04/2012 : 03:09:49
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/04/2012 : 03:37:03
|
[code]
SELECT MAX(n) AS MaxDate
FROM YourTable t
CROSS APPLY (VALUES (DATE1),(DATE2),(DATE3),(DATE4)) m(n)
[/ode]
--
Chandu |
|
|
|
thirty
Starting Member
6 Posts |
Posted - 12/04/2012 : 03:57:50
|
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
? |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/04/2012 : 04:11:02
|
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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
thirty
Starting Member
6 Posts |
Posted - 12/04/2012 : 09:32:52
|
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
|
|
|
|
thirty
Starting Member
6 Posts |
Posted - 12/04/2012 : 15:56:53
|
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?
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/05/2012 : 02:29:31
|
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 |
|
|
|
thirty
Starting Member
6 Posts |
Posted - 12/05/2012 : 07:51:17
|
| thanks everyone for ur help |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/05/2012 : 08:16:16
|
quote:
Originally posted by thirty
thanks everyone for ur help
Got Solution correctly...............
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Help with date query
