CASE STATEMENT Intead of Pivot? 1

[kjv1611]

This may be an impossible question / thing I am looking to do, but I figured I'd ask some folks around here. To start off with, I hate using PIVOT in SQL Server. I guess I'm too lazy on that one, or else it's just because I rarely find times that I need it. So with that, I generally try to avoid it.

I'm working with historical data, and basically I'm trimming down to a "First" Date and a "Last" date of this particular set of events per a unique ID. So the initial dataset I have setup has all the other stuff in between as well as first and last, but I'm picking out first and last by using ROW_NUMBER() OVER (PARTITION BY .. ORDER BY..) and then a case statement to easily see "first" and "last".

Then I can write a query with CASE statements to say:

SELECT MyID
[indent][/indent],CASE WHEN FirstRow = 1 THEN MyDate ELSE NULL END AS MyDateFirst
[indent][/indent],CASE WHEN LastRow = 1 THEN MyDate ELSE NULL END AS MyDateLast
FROM MyTable
WHERE MyDateType <> 'MiddleStuff'

My Results (as expected) so far are:

[b]MyID      MyDateFirst   MyDateLast[/b]
11111     2007-08-03 	NULL
11111     NULL	        2012-03-05
22222     2007-08-03	NULL
22222     NULL	        2007-09-25

What I really want out of it is this:

[b]MyID      MyDateFirst   MyDateLast[/b]
11111     2007-08-03 	2012-03-05
22222     2007-08-03	2007-09-25

So is there a way to do this without PIVOT, or do I just have to quit being lazy on PIVOT?

Thanks for any suggestions.

p.s. I realize I'm probably overdoing what is necessary for determining first and last and showing those side by side in one row, but I figured it's at least a start. And I was attempting to keep as much calculations and what not away from the mammoth overall table to begin with.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

Almost there....

SELECT MyID
,[!]Max([/!]CASE WHEN FirstRow = 1 THEN MyDate ELSE NULL END[!])[/!] AS MyDateFirst
,[!]Max([/!]CASE WHEN LastRow = 1 THEN MyDate ELSE NULL END[!])[/!] AS MyDateLast
FROM MyTable
WHERE MyDateType <> 'MiddleStuff' 
[!]GROUP BY MyID[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

AWESOME! Thanks a Ton, gmmastros! So simple yet so profound!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57