Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max Date from Unnormalized Table


Max Date from Unnormalized Table

Author
Message
DevSteve
DevSteve
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
I have a table of dates similar to the attached.

I can't change the table design (it's not my decision.)
I need the max date for each ID. The dates are stored as varchars, not dates. The empty cells are sometimes null and sometimes zero-length strings. Any suggestions?
Attachments
Milestones.xlsx (3 views, 8.00 KB)
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Can you post the table definition and sample data (as insert statements) instead of an Excel spreadsheet?

Pardon my paranoia, but this is the internet, and trusting a file that could contain executable code is a poor security practice. Lots of Excel macro viruses, etc., out there.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 7074
One way to go:


DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10))

INSERT INTO
@T
VALUES
(1, NULL, '2-11-2012', NULL),
(2, '22-10-2012', NULL, '2-12-2012'),
(3, '8-8-2012', NULL, NULL),
(4, NULL, NULL, '25-12-2012'),
(5, '7-11-2012', '1-8-2012', '5-1-2012')

SET DATEFORMAT dmy

SELECT
ID, MAX(CAST(AnyDate AS DATE)) -- Select the maximum date out of the three splitted rows
FROM
@T
CROSS APPLY
(
-- Split row into three rows, each row containing a different date
VALUES (SentDate), (ReceivedDate), (CreatedDate)
) CA(AnyDate)
GROUP BY
ID



And I am totally agreeing with GSquared. I took the risk.

Edit: Added cast to date
DevSteve
DevSteve
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
I truly appreciate it. I'll see if I can make that work. If I have to follow up with another question I'll post a table def and insert statement.
DevSteve
DevSteve
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
Does Cross Apply work in SQL Server 2005? I'm getting -Incorrect syntax near the keyword 'VALUES'.

Also, making it slightly more challenging - two more fields, LExecuted, and LB_Submitted. If LExecuted is not zero length and not null I need that date. If it is null or zls try LB_Submitted, if also null or zls I need the max date of the three original fields...

DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10), LB_Submitted VARCHAR(10), LExecuted VARCHAR(10))

INSERT INTO @T VALUES (1, NULL, '2-11-2012', NULL, NULL, NULL)
INSERT INTO @T VALUES (2, '22-10-2012', NULL, '2-12-2012', '', NULL)
INSERT INTO @T VALUES (3, '8-8-2012', NULL, '', NULL, NULL)
INSERT INTO @T VALUES (4, NULL, '', '25-12-2012', '', NULL)
INSERT INTO @T VALUES (5, '7-11-2012', '1-8-2012', '5-1-2012', NULL, NULL)
INSERT INTO @T VALUES (6, NULL, NULL, NULL, NULL, '6-30-2012')
INSERT INTO @T VALUES (7, NULL, '', NULL, '4-3-2012', '')
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 7074
You posted in a SQL 2008 forum, so I gave you a SQL 2008 solution. CROSS APPLY works in SQL 2005, it´s the row constructor VALUES which causes the syntax error. It´s new in SQL 2008. The VALUES row constructor can be replaced by a SELECT ... UNION ALL .. SELECT pattern in SQL 2005. The following should work in SQL 2005 (untested, and including your additional requirements).


DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10), LB_Submitted VARCHAR(10), LExecuted VARCHAR(10))

INSERT INTO @T VALUES (1, NULL, '2-11-2012', NULL, NULL, NULL)
INSERT INTO @T VALUES (2, '22-10-2012', NULL, '2-12-2012', '', NULL)
INSERT INTO @T VALUES (3, '8-8-2012', NULL, '', NULL, NULL)
INSERT INTO @T VALUES (4, NULL, '', '25-12-2012', '', NULL)
INSERT INTO @T VALUES (5, '7-11-2012', '1-8-2012', '5-1-2012', NULL, NULL)
INSERT INTO @T VALUES (6, NULL, NULL, NULL, NULL, '30-6-2012')
INSERT INTO @T VALUES (7, NULL, '', NULL, '4-3-2012', '')


SET DATEFORMAT dmy

SELECT
ID,
CASE
WHEN NULLIF(LExecuted, '') IS NOT NULL THEN
CAST(LExecuted AS DATETIME)
WHEN NULLIF(LB_Submitted, '') IS NOT NULL THEN
CAST(LB_Submitted AS DATETIME)
ELSE
MAX(CAST(AnyDate AS DATETIME))
END AnyDate
FROM
@T
CROSS APPLY
(
-- Split row into three rows, each row containing a different date
--VALUES (SentDate), (ReceivedDate), (CreatedDate)
SELECT SentDate
UNION ALL
SELECT ReceivedDate
UNION ALL
SELECT CreatedDate
) CA(AnyDate)
GROUP BY
ID, LExecuted, LB_Submitted



BTW, the row with ID 6 contained an illegal date. I swapped day and month.

EDIT: The DATE datatype is also new in SQL 2008, so I used DATETIME instead.
DevSteve
DevSteve
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
Works perfectly! Cross Apply is a new one for me.
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1677 Visits: 7074
Great. To make it a little bit more elegant you could replace the CASE expression with a COALESCE function:


SELECT
ID,
COALESCE(NULLIF(LExecuted, ''), NULLIF(LB_Submitted, ''), MAX(CAST(AnyDate AS DATETIME))) AnyDate
FROM
@T
CROSS APPLY
(
-- Split row into three rows, each row containing a different date
--VALUES (SentDate), (ReceivedDate), (CreatedDate)
SELECT SentDate
UNION ALL
SELECT ReceivedDate
UNION ALL
SELECT CreatedDate
) CA(AnyDate)
GROUP BY
ID, LExecuted, LB_Submitted



Performance-wise it should not make any difference. COALESCE is translated into a CASE expression by the query compiler anyway, but it just seems more elegant to me. But that's just a personal taste of course.
DevSteve
DevSteve
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
Even better. Thanks again.
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
DevSteve, I think Peter's got it wrapped up, but for some unknown reason I saw this as an unpivot problem. I would probably stick with what you already have, but just for the heck of it, here is another alternative. But stick with what Peter gave you. (walking away humbly now)


DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10), LB_Submitted VARCHAR(10), LExecuted VARCHAR(10))

INSERT INTO @T VALUES (1, NULL, '2-11-2012', NULL, NULL, NULL)
INSERT INTO @T VALUES (2, '22-10-2012', NULL, '2-12-2012', '', NULL)
INSERT INTO @T VALUES (3, '8-8-2012', NULL, '', NULL, NULL)
INSERT INTO @T VALUES (4, NULL, '', '25-12-2012', '', NULL)
INSERT INTO @T VALUES (5, '7-11-2012', '1-8-2012', '5-1-2012', NULL, NULL)
INSERT INTO @T VALUES (6, NULL, NULL, NULL, NULL, '6-30-2012')
INSERT INTO @T VALUES (7, NULL, '', NULL, '4-3-2012', '');

SET DATEFORMAT dmy;

WITH upvt AS
(
SELECT
ID,
DATE_TYPE,
THE_DATE
FROM
(SELECT ID, SentDate, ReceivedDate, CreatedDate, LB_Submitted, LExecuted
FROM @t) pvt
UNPIVOT
(THE_DATE FOR DATE_TYPE IN ([SentDate], [ReceivedDate], [CreatedDate], [LB_Submitted], [LExecuted])) AS upvt
)
SELECT
ID,
DATE_TYPE,
THE_DATE
FROM upvt a
WHERE THE_DATE =
(SELECT MAX(THE_DATE) FROM upvt b
WHERE a.ID = b.ID)



Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search