Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Max Date from Unnormalized Table Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:40 PM
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?


  Post Attachments 
Milestones.xlsx (3 views, 8.85 KB)
Post #1404905
Posted Wednesday, January 9, 2013 11:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1404916
Posted Wednesday, January 9, 2013 11:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:46 AM
Points: 1,598, Visits: 6,646
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
Post #1404917
Posted Wednesday, January 9, 2013 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:40 PM
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.
Post #1404948
Posted Wednesday, January 9, 2013 1:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:40 PM
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', '')
Post #1404988
Posted Wednesday, January 9, 2013 2:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:46 AM
Points: 1,598, Visits: 6,646
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.
Post #1405007
Posted Wednesday, January 9, 2013 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:40 PM
Points: 8, Visits: 59
Works perfectly! Cross Apply is a new one for me.
Post #1405022
Posted Wednesday, January 9, 2013 2:58 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:46 AM
Points: 1,598, Visits: 6,646
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.
Post #1405033
Posted Wednesday, January 9, 2013 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 2:40 PM
Points: 8, Visits: 59
Even better. Thanks again.
Post #1405037
Posted Wednesday, January 9, 2013 3:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #1405052
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse