August 29, 2007 at 3:17 pm
Hi all. I am having a brain meltdown. I am trying to gather the last two orders for a customer. Would a subquery be the best way to get the desired results? I have been trying to use TOP 2 in one query and various things in the second query but am only getting errors. I just need a little direction to get started. Here is what I have so far. I just need to add a subquery to get all records for each order from the same table. Thanks in advance.
SELECT TOP 2 createdate AS ShipDate, TrackingNumber
FROM shipments
WHERE a.customerid = 'dmo-con'
August 29, 2007 at 3:31 pm
August 29, 2007 at 3:40 pm
This is one query that didn't work. Produces a syntax error near ')'
SELECT createdate AS ShipDate, TrackingNumber
FROM (SELECT TOP 2 createdate AS ShipDate, TrackingNumber FROM shipments WHERE customerid = 'dmo-con')
August 29, 2007 at 4:12 pm
are you not using an order id, can't you use that to get the last orders?
as for your script try to alias your sub query i.e.
SELECT createdate AS ShipDate, TrackingNumber
FROM (SELECT TOP 2 createdate AS ShipDate, TrackingNumber FROM shipments WHERE customerid = 'dmo-con') A
but did you try the ORDER BY solution I put above?
August 29, 2007 at 4:17 pm
Internally we have an order id but this is information exported from a UPS system. I did try the order by and it didn't help. I think I'm close with the following query but it still only returns one row for each date.
SELECT createdate AS ShipDate, TrackingNumber
FROM shipments
WHERE customerid = 'dmo-con' AND
createdate IN (SELECT TOP 2 createdate FROM shipments WHERE customerid = 'dmo-con' ORDER BY createdate desc)
August 29, 2007 at 4:24 pm
I think I have this figured out. It looks like the time portion of the createdate field was getting in the way. I will do more testing but here is the revised query:
SELECT createdate AS ShipDate, TrackingNumber
FROM shipments
WHERE customerid = 'dmo-con' AND
CONVERT(CHAR(10),createdate,101) IN (SELECT TOP 2 CONVERT(CHAR(10),createdate,101) FROM shipments WHERE customerid = 'dmo-con' ORDER BY createdate desc)
ORDER BY createdate
August 29, 2007 at 11:40 pm
Why are you overcomplicating things?
Bledu put you on the right track.
SELECT TOP 2 * FROM shipments WHERE customerid = 'dmo-con' ORDER BY createdate desc
It the issue is that you want them chronological, add
select * from (
SELECT TOP 2 * FROM shipments WHERE customerid = 'dmo-con' ORDER BY createdate desc
) as d ORDER BY createdate
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 9:28 am
Thank you for the reply. Both options above still only return one record for each createdate. I do like your approach though keeping it simple. Any thoughts on why only one row per date would be returned?
August 30, 2007 at 9:55 am
I have to throw a little kink in the issue. It appears that TOP n when evaluating datetime fields always uses the time portion in addition to the date. Above where I thought I had a solution, the outcome returns two records for the same date when looking at TOP 3 createdate. My test customer has three dates available. One record for 08/29, three records for 08/28, and two records for 08/27. In summary, when I run the test query below, I get one record from the 29th and two records from the 28th even though I'm converting the format.
SELECT TOP 3 * CONVERT(char(10),createdate,101) as ShipDate
FROM shipments
where customerid = 'dmo-con'
order by shipdate desc
August 30, 2007 at 10:44 am
For anyone viewing this post, I finally have a solution. It may not be the best solution but it works for now. If anyone can see a way to simplify the query, please let me know. The DISTINCT keyword in the TOP query solved my problem.
SELECT CONVERT(CHAR(10),createdate,101) AS ShipDate, TrackingNumber
FROM shipments
WHERE customerid = @clinicid AND
CONVERT(CHAR(10),createdate,101) IN
(SELECT DISTINCT TOP 3 CONVERT(CHAR(10),createdate,101) as ShipDate
FROM shipments
WHERE customerid = @clinicid
ORDER BY ShipDate DESC)
ORDER BY ShipDate DESC
August 30, 2007 at 11:32 am
That is not the same as what you origionally posted. That is ALL orders that match the 3 most recent days on which any number of orders was posted. So if there were 100 orders on 8/30/2007 then they would all be 1 per your above query.
If that is what you are looking for, then I have 2 suggestions
1) make a table valued function and pass the customer ID.
2) Add an index on (customerID,CreateDate)
August 30, 2007 at 11:36 am
Why don't you use WITH TIES?
SELECT
TOP 3 WITH TIES
CreateDate AS ShipDate,
TrackingNumber
FROM Shipments
WHERE CustomerID = @ClinicID
ORDER BY DATEDIFF(DAY, CreateDate, 0)
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 12:13 pm
RE: WITH TIES. This produces the same result I received before placing the DISTINCT keyword in the TOP query. I apologize if I didn't explain clearly what I am trying to accomplish. Basically I am trying to retrieve the last n orders from a customer based on a ship date. For each of the last n ship dates (createdate), I want to pull all records for those dates.
August 30, 2007 at 12:42 pm
Now that's an explanation!
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 12:51 pm
And this is the solution
-- Prepare sample data
DECLARE @Sample TABLE (CustomerID INT, dt DATETIME)
INSERT @Sample
(
CustomerID,
dt
)
SELECT TOP 100000
1000 + ABS(CHECKSUM(NEWID())) % 10000,
15000 + ABS(CHECKSUM(NEWID())) % 15000
FROM syscolumns AS c1
CROSS JOIN syscolumns AS c2
-- Show the expected output
SELECT CustomerID,
dt
FROM (
SELECT CustomerID,
dt,
DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY DATEDIFF(DAY, dt, 0)) AS RecID
FROM @Sample
) AS d
WHERE RecID <= 2
ORDER BY CustomerID
Now you can make a VIEW with the query above, without the WHERE and ORDER BY.
And in your SP, select from the view and add the WHERE and ORDER BY!
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply