Selecting last two orders

Question

Post reply

Selecting last two orders

Viewing 6 posts - 16 through 20 (of 20 total)

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

David Tyler-455966 SSC Veteran Points: 298 More actions · Answer 1

Very nice. I'll start playing with the solution and see what I get. Thank you for taking the time to work on this.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

CREATE

PROCEDURE dbo.uspGetLastOrdersForCustomer

(

@CustomerID

@LastDays INT = 3

)

AS

SET NOCOUNT ON

SELECT CustomerID,

ShipDate,

TrackingNumber

FROM (

SELECT CustomerID,

CreateDate AS ShipDate,

TrackingNumber,

DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY DATEDIFF(DAY, dt, 0)) AS RecID

FROM Shipments

WHERE CustomerID = @CustomerID

) AS d

WHERE RecID <= @LastDays

ORDER BY ShipDate DESC

N 56°04'39.16"
E 12°55'05.25"

David Tyler-455966 SSC Veteran Points: 298 More actions · Answer 3

Sweet. Works like a charm. I also like how you've added number of days as a parameter. I can add days as a selection on our tracking site and pass that in. You've been a great help. Hopefully someday I can return the favor.

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

I see you used DENSE_RANK() vs. ROW_NUMBER().

So if I understand how this works. ROW_NUMBER() would be used if you want the top N rows by customer.

and DENSE_RANK() will return all rows for the top 2 (customer,Day) combinations?

Really Nice...

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

ROW_NUMBER() will give a unique number within the partition regardless of order by.

DENSE_RANK() will give a "rownumber" depending on order by.

If column used in order by has multiple values, all get same rank value.

The trick here is not dense_rank. It is DATEDIFF(DAY, dt, 0)

It calulates the number of days passed since January 1, 1900. BUT IN THE OTHER ORDER!

So for 20070830, the result is -39322 (a whole integer too, regardless of time information in dt).

So for 20070831, the result is -39323 (a whole integer too, regardless of time information in

And I sort by that number, which gives newest dates are first!

I could do an order by DATEDIFF(DAY, 0, dt) DESC

but that takes longer time, since the datediff must be calculated first, AND THEN an extra descending. No need. Just do a DATEDIFF(DAY, dt, 0) instead. Voila, in one step.

N 56°04'39.16"
E 12°55'05.25"

David Tyler-455966 SSC Veteran Points: 298 More actions · Answer 6

This has been a great experience. It's nice to converse with other developers/DBA's for a change. I have been the sole IT guy for a small business for 7 years and never really get to talk things over with anyone IT. Thanks again.