Help with date query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with date query

Author

Topic

thirty
Starting Member

6 Posts

Posted - 2012-12-03 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-03 : 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 table

SELECT 
	rowid, 
	MAX(dt) AS maxdate
FROM 
	YourTable 
UNPIVOT(dt FOR dates IN (date1,date2,date3,date4))U

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-03 : 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 - 2012-12-04 : 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) ??

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 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 - 2012-12-04 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 04:11:02

[code]
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'

[/code]

--
Chandu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-12-04 : 06:40:47

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

thirty
Starting Member

6 Posts

Posted - 2012-12-04 : 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 - 2012-12-04 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 02:29:31

[code]
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
[/code]

-----------------------------------
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.
[code]
,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
[/code]
--
Chandu

thirty
Starting Member

6 Posts

Posted - 2012-12-05 : 07:51:17

thanks everyone for ur help

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-05 : 08:16:16