January 9, 2013 at 11:07 am
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?
January 9, 2013 at 11:22 am
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
January 9, 2013 at 11:27 am
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
January 9, 2013 at 12:34 pm
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.
January 9, 2013 at 1:40 pm
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', '')
January 9, 2013 at 2:15 pm
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.
January 9, 2013 at 2:39 pm
Works perfectly! Cross Apply is a new one for me.
January 9, 2013 at 2:58 pm
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.
January 9, 2013 at 3:04 pm
Even better. Thanks again.
January 9, 2013 at 3:39 pm
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.
January 10, 2013 at 6:28 am
Thanks Greg. Peter's solution is working for me, but I appreciate the input.
When I tried your method I got an error. I realize I'm using SQL Server 2008 SSMS but I'm connecting to a 2005 database. Perhaps that has something to do with it...
Msg 325, Level 15, State 1, Line 22
Incorrect syntax near 'UNPIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
January 10, 2013 at 7:37 am
DevSteve (1/10/2013)
Thanks Greg. Peter's solution is working for me, but I appreciate the input.When I tried your method I got an error. I realize I'm using SQL Server 2008 SSMS but I'm connecting to a 2005 database. Perhaps that has something to do with it...
Msg 325, Level 15, State 1, Line 22
Incorrect syntax near 'UNPIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Ah, yes. I was about 20 minutes from walking out the door when I read your post yesterday, and did not read it closely enough. I did not realize you are on 2005, so you will have to stick with Peter's solution (which is better anyway). I just realized my solution neglects your last set of criteria anyway. I probably should not have posted it.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply