highest of 3 [dates] and highest > 11 months old

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)

highest of 3 [dates] and highest > 11 months old

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2008-11-21 : 11:10:49

I have a table with 3 columns(datatype = datetime) and I need to find the latest date and then see if its older than 11 months from today.. using sql 2005 i included a markup table to save someone time for testing..

DECLARE @t TABLE (id int, d1 datetime, d2 datetime, d3 datetime)

INSERT INTO @t ( id, d1, d2, d3)

SELECT 1, CAST('01/01/2000' AS datetime), CAST('01/01/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL

SELECT 2, CAST('01/10/2000' AS datetime), CAST('01/15/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL

SELECT 3, CAST('01/01/2008' AS datetime), CAST('01/01/2007' AS datetime), CAST('01/01/2006' AS datetime)

SELECT * FROM @t
WHERE
/* get the highest of the 3 date columns and older than 11 months from current date
*/

thanks so much..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 11:16:15

[code]DECLARE @t TABLE (
id int,
d1 datetime,
d2 datetime,
d3 datetime
)

INSERT @t
(
id,
d1,
d2,
d3
)

SELECT 1, '01/01/2000', '01/01/2005', '01/01/2002' UNION ALL
SELECT 2, '01/10/2000', '01/15/2005', '01/01/2002' UNION ALL
SELECT 3, '01/01/2008', '01/01/2007', '01/01/2006'

SELECT * FROM @t

SELECT TOP 1 u.ID,
u.theDate
FROM @t as t
UNPIVOT (
theDate
FOR theCol IN (t.d1, t.d2, t.d3)
) AS u
WHERE u.theDate < DATEADD(MONTH, -11, GETDATE())
ORDER BY u.theDate DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-21 : 11:36:31

I came up with something slightly different than Peso:

DECLARE @t TABLE (id int, d1 datetime, d2 datetime, d3 datetime)

INSERT INTO @t ( id, d1, d2, d3)
SELECT 1, CAST('01/01/2000' AS datetime), CAST('01/01/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 2, CAST('01/10/2000' AS datetime), CAST('01/15/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 3, CAST('01/01/2008' AS datetime), CAST('01/01/2007' AS datetime), CAST('01/01/2006' AS datetime);

WITH DateCompare (ID, Date)
AS
( /* Convert each record from a horizontal to vertical format */
    SELECT u.ID, u.Date
    FROM @t AS t
    UNPIVOT (Date FOR Col IN (t.d1, t.d2, t.d3)) AS u
), DateMax (Date)
AS
( /* Derive the maximum date for each ID */
    SELECT DISTINCT
        MAX(Date) OVER (PARTITION BY ID)
    FROM DateCompare
)
/* Only retrieve records older than 11 months from today */
SELECT *
FROM DateMax
WHERE Date > DATEADD(MONTH, -11, GETDATE());

I also noted that we return different results:
Peso: 2007-01-01 00:00:00.000
Mine: 2008-01-01 00:00:00.000

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2008-11-21 : 11:54:11

quote:

Originally posted by tfountain

I came up with something slightly different than Peso:

DECLARE @t TABLE (id int, d1 datetime, d2 datetime, d3 datetime)

INSERT INTO @t ( id, d1, d2, d3)
SELECT 1, CAST('01/01/2000' AS datetime), CAST('01/01/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 2, CAST('01/10/2000' AS datetime), CAST('01/15/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 3, CAST('01/01/2008' AS datetime), CAST('01/01/2007' AS datetime), CAST('01/01/2006' AS datetime);

WITH DateCompare (ID, Date)
AS
( /* Convert each record from a horizontal to vertical format */
    SELECT u.ID, u.Date
    FROM @t AS t
    UNPIVOT (Date FOR Col IN (t.d1, t.d2, t.d3)) AS u
), DateMax (Date)
AS
( /* Derive the maximum date for each ID */
    SELECT DISTINCT
        MAX(Date) OVER (PARTITION BY ID)
    FROM DateCompare
)
/* Only retrieve records older than 11 months from today */
SELECT *
FROM DateMax
WHERE Date > DATEADD(MONTH, -11, GETDATE());

I also noted that we return different results:
Peso: 2007-01-01 00:00:00.000
Mine: 2008-01-01 00:00:00.000

Yahtzee!@!!!!
I did have to change this line Date > DATEADD to Date < DATEADD to return the older dates.. but your code returned the distinct highest date just as I was looking for.. Thanks to both of you~

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2008-11-21 : 12:39:08

I modified it some.. I need to return the formID and the userID with the results. . when i added it in i lost some functionality so i took them out.. thanks


SELECT     formID, effectiveDate, reviewDate, revisedDate, userID
FROM         policyManagementDB.dbo.formList
WHERE     (effectiveDate IS NOT NULL) OR
                      (reviewDate IS NOT NULL) OR
                      (revisedDate IS NOT NULL); WITH DateCompare(formID, Date, userID) AS
                          (/* Convert each record from a horizontal to vertical format */ 
							SELECT u.formID, u.Date, u.userID
                            FROM    policyManagementDB.dbo.formList AS tbl UNPIVOT (Date FOR Col IN (tbl .effectiveDate, tbl .reviewDate, tbl .revisedDate)) AS u), DateMax(Date) AS
                          (/* Derive the maximum date for each ID */ 
							SELECT DISTINCT MAX(Date) OVER (PARTITION BY formID)
                            FROM          DateCompare)
                          /* Only retrieve records older than 11 months from today */ 
							SELECT *
                            FROM         DateMax
                            WHERE     Date < DATEADD(MONTH, - 11, GETDATE());

quote:

Originally posted by tfountain

I came up with something slightly different than Peso:

DECLARE @t TABLE (id int, d1 datetime, d2 datetime, d3 datetime)

INSERT INTO @t ( id, d1, d2, d3)
SELECT 1, CAST('01/01/2000' AS datetime), CAST('01/01/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 2, CAST('01/10/2000' AS datetime), CAST('01/15/2005' AS datetime), CAST('01/01/2002' AS datetime) UNION ALL
SELECT 3, CAST('01/01/2008' AS datetime), CAST('01/01/2007' AS datetime), CAST('01/01/2006' AS datetime);

WITH DateCompare (ID, Date)
AS
( /* Convert each record from a horizontal to vertical format */
    SELECT u.ID, u.Date
    FROM @t AS t
    UNPIVOT (Date FOR Col IN (t.d1, t.d2, t.d3)) AS u
), DateMax (Date)
AS
( /* Derive the maximum date for each ID */
    SELECT DISTINCT
        MAX(Date) OVER (PARTITION BY ID)
    FROM DateCompare
)
/* Only retrieve records older than 11 months from today */
SELECT *
FROM DateMax
WHERE Date > DATEADD(MONTH, -11, GETDATE());

I also noted that we return different results:
Peso: 2007-01-01 00:00:00.000
Mine: 2008-01-01 00:00:00.000

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 15:38:57

Does Peso's solution not work? It seems strange to that an elegant solution and then dismiss it for a less elegant one.

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2008-11-21 : 15:46:47

quote:
Originally posted by Lamprey

Does Peso's solution not work? It seems strange to that an elegant solution and then dismiss it for a less elegant one.

Yes I actually did get everything to work.. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 18:23:13

I am baffled that

/* Only retrieve records older than 11 months from today */
SELECT *
FROM DateMax
WHERE Date > DATEADD(MONTH, -11, GETDATE());

returns records "older than 11 months from today" because of the ">" comparison.
I think that returns records that are at most 11 months old.

E 12°55'05.63"
N 56°04'39.26"

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-21 : 18:44:56

quote:
Originally posted by Peso

I am baffled that
/* Only retrieve records older than 11 months from today */
SELECT *
FROM DateMax
WHERE Date > DATEADD(MONTH, -11, GETDATE());
returns records "older than 11 months from today" because of the ">" comparison.
I think that returns records that are at most 11 months old.

E 12°55'05.63"
N 56°04'39.26"

Yes, I got the comparison backwards :)

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-11-21 : 18:48:50

quote:
Originally posted by Lamprey

Does Peso's solution not work? It seems strange to that an elegant solution and then dismiss it for a less elegant one.

Taking punches? Jeesh... So I got a comparison backwards. To say one is "elegant" and one is not is rather silly here. They both work well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources