Calculate the Days Between First and Last Orders

Question

Calculate the Days Between First and Last Orders

Kathi Kellenberger

SSChasing Mays

Points: 601
More actions
February 3, 2020 at 12:00 am

#3713240

Comments posted to this topic are about the item Calculate the Days Between First and Last Orders

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 1

Nice question to start the week on, thanks Kathi

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Ah well.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rune Bivrin SSCrazy Eights Points: 8537 More actions · Answer 3

Good one!

Until I understood the framing context, I used to do the LAST_VALUE as FIRST_VALUE(.. ORDER BY [col] DESC), which works out the same, but is likely a worse approach from a query plan perspective. At least if you have indexes in place to support the ordering.

Just because you're right doesn't mean everybody else is wrong.

Jakub.Janda Default port Points: 1424 More actions · Answer 4

Or you could do it totally old school by MIN(OrderDate), MAX(OrderDate) and GROUP BY CusomerID. And it would be faster, too.

Is there any reason to overcomplicate things?

Kathi Kellenberger SSChasing Mays Points: 601 More actions · Answer 5

In this case, it would work. I should probably have pulled a different column like the amount. I wanted to show that you can use FIRST_VALUE and LAST_VALUE to pull values from the first and last rows. It's not always the same as MIN and MAX.

Jakub.Janda Default port Points: 1424 More actions · Answer 6

Yes, amount of first and last customer purchase would require a different solution. Sorry for my rant then.