select the record with the most recent order date

Question

select the record with the most recent order date

itortu

SSCommitted

Points: 1914
More actions
June 16, 2014 at 9:13 am

#308256

Hello I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
,s.ItemNumberAS 'PriorItemNumber'
,s.RequestedDeliveryDateAS 'PriorQuoteDate'
,ROW_NUMBER () OVER (PARTITION BY s.ItemNumber ORDER BY s.RequestedDeliveryDate DESC) rn
FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
LEFT OUTER JOIN @CurrentQuoteItemComponentTable cqic
ON cqic.ItemNumber COLLATE DATABASE_DEFAULT = s.ItemNumber
AND cqic.ComponentItemNumber COLLATE DATABASE_DEFAULT = s.ComponentItemNumber
WHERE s.CustomerNumber = @CustomerNumber
AND s.ItemNumber COLLATE DATABASE_DEFAULT IN (SELECT ItemNumber FROM @CurrentQuoteItemComponentTable)
AND s.SalesQuoteNumber <> @SalesQuoteNumber
GROUP BY
s.SalesQuoteNumber
,s.RequestedDeliveryDate
,s.ItemNumber
) s
WHERE s.rn = 1
What am I missing in my query> how can I change it so it only returns the most recent date?
I hope someone can help me a bit with these question.
Thank you very much.

Viewing 15 posts - 1 through 15 (of 15 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

itortu (6/16/2014)
Hello I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
,s.ItemNumberAS 'PriorItemNumber'
,s.RequestedDeliveryDateAS 'PriorQuoteDate'
,ROW_NUMBER () OVER (PARTITION BY s.ItemNumber ORDER BY s.RequestedDeliveryDate DESC) rn
FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)
LEFT OUTER JOIN @CurrentQuoteItemComponentTable cqic
ON cqic.ItemNumber COLLATE DATABASE_DEFAULT = s.ItemNumber
AND cqic.ComponentItemNumber COLLATE DATABASE_DEFAULT = s.ComponentItemNumber
WHERE s.CustomerNumber = @CustomerNumber
AND s.ItemNumber COLLATE DATABASE_DEFAULT IN (SELECT ItemNumber FROM @CurrentQuoteItemComponentTable)
AND s.SalesQuoteNumber <> @SalesQuoteNumber
GROUP BY
s.SalesQuoteNumber
,s.RequestedDeliveryDate
,s.ItemNumber
) s
WHERE s.rn = 1
What am I missing in my query> how can I change it so it only returns the most recent date?
I hope someone can help me a bit with these question.
Thank you very much.

We are missing most of the details here but it seems you could greatly simplify this by just using top 1.

Something like this.

SELECT top 1

s.SalesQuoteNumberAS 'PriorQuoteNumber'

,s.ItemNumberAS 'PriorItemNumber'

,s.RequestedDeliveryDateAS 'PriorQuoteDate'

FROM dw.cost.vStd_SalesQuoteCostLineBOM s (NOLOCK)

LEFT OUTER JOIN @CurrentQuoteItemComponentTable cqic

ON cqic.ItemNumber COLLATE DATABASE_DEFAULT = s.ItemNumber

AND cqic.ComponentItemNumber COLLATE DATABASE_DEFAULT = s.ComponentItemNumber

WHERE s.CustomerNumber = @CustomerNumber

AND s.ItemNumber COLLATE DATABASE_DEFAULT IN (SELECT ItemNumber FROM @CurrentQuoteItemComponentTable)

AND s.SalesQuoteNumber <> @SalesQuoteNumber

ORDER BY s.RequestedDeliveryDate DESC

Do you really need NOLOCK? Do you know all the things it brings to the table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- Table @CurrentQuoteItemComponentTable cqic doesn't play any useful role in the query.

SELECT

[PriorQuoteNumber]= s.SalesQuoteNumber,

[PriorItemNumber]= s.ItemNumber,

[PriorQuoteDate]= s.RequestedDeliveryDate

FROM dw.cost.vStd_SalesQuoteCostLineBOM s

WHERE s.CustomerNumber = @CustomerNumber

AND s.SalesQuoteNumber <> @SalesQuoteNumber

AND s.RequestedDeliveryDate = (

SELECT [most recent delivery date] = MAX(RequestedDeliveryDate)

FROM dw.cost.vStd_SalesQuoteCostLineBOM)

AND EXISTS (

SELECT 1

FROM @CurrentQuoteItemComponentTable

WHERE ItemNumber = s.ItemNumber COLLATE DATABASE_DEFAULT)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

itortu SSCommitted Points: 1914 More actions · Answer 3

Hi Sean,

This works but the thing is that I can have multiple ItemNumbers and each can have their own prior quote number

example

item number prior quote number

X15816 10446-0

X16061 10446-0

X16085 10446-0

X16092 10450-0

X16093 10450-0

10510-0 << this is the most recent one, but I get two for this item

X16128 10454-0

X16129 10454-0

I included some data

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Instead of a spreadsheet full of data that I then have to create a table (guessing at datatypes) and populate with your spreadsheet data can you just post ddl and insert statements?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

itortu SSCommitted Points: 1914 More actions · Answer 5

itortu

SSCommitted

Points: 1914

June 16, 2014 at 10:47 am

#1722039

will do. thank you

itortu SSCommitted Points: 1914 More actions · Answer 6

this is my script, create and insert.

i want to point out that if i run this script:

select * from [dbo].[salesquotestest] WHERE prioriquotedate IN (SELECT MAX(prioriquotedate) FROM [dbo].[salesquotestest] )

i get the item with the latest date, but i need to get each of the items not just the one item with the most recent date

thank you very much

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

I think you have duplicate rows in the data...can you please check.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

itortu SSCommitted Points: 1914 More actions · Answer 8

no duplicates, different quote number and quote date

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Thanks for the ddl and sample data. Based on the sample what do you expect as output? I am not quite sure what you are trying to do here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

itortu SSCommitted Points: 1914 More actions · Answer 10

I would want to obtain the prior quote number, prior item number, prior quote date for all items that are part of quote number QUO-10566-0

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

itortu (6/16/2014)
I would want to obtain the prior quote number, prior item number, prior quote date for all items that are part of quote number QUO-10566-0

I really meant what is the actual output, not the description. 😉

When you say quote number I assume you mean from the column salesquotenumber? It looks like there are 7 distinct item numbers for that order?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

itortu SSCommitted Points: 1914 More actions · Answer 12

please see the image attached.

it only shows for one item number, i want to do it for each item that is

X15816

X16061

X16085

X16092

X16093

X16128

X16129

thank you

Sean Lange SSC Guru Points: 286590 More actions · Answer 13

It still isn't exactly clear what you want but I think it awfully close.

declare @SalesQuoteNumber varchar(20) = 'QUO-10566-0'

select * from

(

select *, ROW_NUMBER() over (partition by salesquotenumber, itemnumber, componentitemnumber order by prioriquotedate desc) as RowNum

from salesquotestest

where salesquotenumber = @SalesQuoteNumber

) s

where s.RowNum = 1

order by itemnumber, componentitemnumber

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

itortu (6/16/2014)
no duplicates, different quote number and quote date

may not be relevant ...but I beg to differ on duplicates in your test data

script

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10024', '2', 'QUO-10450-0', '10024', '3/21/2014')

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10024', '2', 'QUO-10450-0', '10024', '3/21/2014')dupe

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10024', '2', 'QUO-10510-0', '10024', '4/29/2014')

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10024', '2', 'QUO-10510-0', '10024', '4/29/2014')dupe

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10188', '2', 'QUO-10450-0', '10188', '3/21/2014')

insert into dbo.salesquotestest values('QUO-10566-0', 'X16093', '10188', '2', 'QUO-10450-0', '10188', '3/21/2014')dupe

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day