How do I retrieve the last purchase date from a query?

Question

1.00/5 (2 votes)

See more:

This is the query!

SQL

SELECT 
MS_SUPP_MANUFACTURE.NAME SUPPLIER,
MS_ITEMS.SUPPLIER_CODE SUPPLIER_CODE,
MS_ITEMS.ITEM_CODE BB_CODE,
MS_ITEMS_TYPE.ITEM_TYPE,
TR_PURCHD.QTY Quantity,
TR_PURCHH.DOC_DATE Last_Purchase_date
FROM 
TR_PURCHH WITH(NOLOCK) INNER JOIN TR_PURCHD WITH(NOLOCK) ON TR_PURCHD.PURCHH_ID=TR_PURCHH.ID 
                      AND TR_PURCHH.CANCELED = 0 
                      AND TR_PURCHH.ISSUED = 1
                      AND TR_PURCHH.DOC_TYPE_CODE IN ('C-IM','C-FI','C-FT')
					  AND TR_PURCHH.DOC_DATE between '2021-12-01' and '2021-12-31'				 
INNER JOIN MS_ITEMS WITH(NOLOCK) ON TR_PURCHD.ITEM_ID= MS_ITEMS.ID
and ISNULL(MS_ITEMS.SUPPLIER_CODE,'')<>''
INNER JOIN DB_COMPANY WITH (NOLOCK) ON TR_PURCHH.COMP_CODE=DB_COMPANY.COMP_CODE
INNER JOIN MS_PARTNERS WITH(NOLOCK) ON TR_PURCHH.PARTNER_ID=MS_PARTNERS.ID                       
LEFT JOIN  MS_ITEMS_TYPE WITH(NOLOCK) ON MS_ITEMS_TYPE.ID = MS_ITEMS.ITEM_TYPE_ID
LEFT JOIN  MS_SUPP_MANUFACTURE WITH(NOLOCK) ON MS_ITEMS.SUPP_MANUFACTURE_ID=MS_SUPP_MANUFACTURE.ID

What I have tried:

SQL

SELECT top 1 * FROM TR_PURCHH where TR_PURCHH.DOC_DATE between '2021-12-01' and '2021-12-31'
order by DOC_DATE desc

Posted 6-Jan-22 22:39pm

florin orita

Updated 7-Jan-22 1:40am

Herman<T>.Instance

v2

Add a Solution

Comments

Richard Deeming 7-Jan-22 4:49am

Instead of showing a badly-formatted query and expecting us to work it out, show us the structure of your tables and describe precisely what you are trying to do, what you have tried, and where you are stuck.

If possible, create a SQL Fiddle[^] to demonstrate the problem, with suitable anonymized sample data, and link to that in your question.

OriginalGriff 7-Jan-22 5:18am

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-01-07T01:40:00

Assuming you don't want the overall last purchase date (I am making that assumption because that is what you should get with

SQL

SELECT top 1 * FROM TR_PURCHH where TR_PURCHH.DOC_DATE between '2021-12-01' and '2021-12-31'
order by DOC_DATE desc

but that you want the last purchase date based on your initial query, then just use an appropriate GROUP BY e.g.

SQL

SELECT 
MS_SUPP_MANUFACTURE.NAME SUPPLIER,
MS_ITEMS.SUPPLIER_CODE SUPPLIER_CODE,
MS_ITEMS.ITEM_CODE BB_CODE,
MS_ITEMS_TYPE.ITEM_TYPE,
TR_PURCHD.QTY Quantity,
MAX(TR_PURCHH.DOC_DATE) Last_Purchase_date
FROM 
TR_PURCHH WITH(NOLOCK) INNER JOIN TR_PURCHD WITH(NOLOCK) ON TR_PURCHD.PURCHH_ID=TR_PURCHH.ID 
                      AND TR_PURCHH.CANCELED = 0 
                      AND TR_PURCHH.ISSUED = 1
                      AND TR_PURCHH.DOC_TYPE_CODE IN ('C-IM','C-FI','C-FT')
					  AND TR_PURCHH.DOC_DATE between '2021-12-01' and '2021-12-31'				 
INNER JOIN MS_ITEMS WITH(NOLOCK) ON TR_PURCHD.ITEM_ID= MS_ITEMS.ID
and ISNULL(MS_ITEMS.SUPPLIER_CODE,'')<>''
INNER JOIN DB_COMPANY WITH (NOLOCK) ON TR_PURCHH.COMP_CODE=DB_COMPANY.COMP_CODE
INNER JOIN MS_PARTNERS WITH(NOLOCK) ON TR_PURCHH.PARTNER_ID=MS_PARTNERS.ID                       
LEFT JOIN  MS_ITEMS_TYPE WITH(NOLOCK) ON MS_ITEMS_TYPE.ID = MS_ITEMS.ITEM_TYPE_ID
LEFT JOIN  MS_SUPP_MANUFACTURE WITH(NOLOCK) ON MS_ITEMS.SUPP_MANUFACTURE_ID=MS_SUPP_MANUFACTURE.ID
GROUP BY 
MS_SUPP_MANUFACTURE.NAME SUPPLIER,
MS_ITEMS.SUPPLIER_CODE SUPPLIER_CODE,
MS_ITEMS.ITEM_CODE BB_CODE,
MS_ITEMS_TYPE.ITEM_TYPE

or to get the last purchase date by supplier (some of the joins may not be required)

SQL

SELECT 
MS_SUPP_MANUFACTURE.NAME SUPPLIER,
MS_ITEMS.SUPPLIER_CODE SUPPLIER_CODE
MAX(TR_PURCHH.DOC_DATE) Last_Purchase_date
FROM 
TR_PURCHH WITH(NOLOCK) INNER JOIN TR_PURCHD WITH(NOLOCK) ON TR_PURCHD.PURCHH_ID=TR_PURCHH.ID 
                      AND TR_PURCHH.CANCELED = 0 
                      AND TR_PURCHH.ISSUED = 1
                      AND TR_PURCHH.DOC_TYPE_CODE IN ('C-IM','C-FI','C-FT')
					  AND TR_PURCHH.DOC_DATE between '2021-12-01' and '2021-12-31'				 
INNER JOIN MS_ITEMS WITH(NOLOCK) ON TR_PURCHD.ITEM_ID= MS_ITEMS.ID
and ISNULL(MS_ITEMS.SUPPLIER_CODE,'')<>''
INNER JOIN DB_COMPANY WITH (NOLOCK) ON TR_PURCHH.COMP_CODE=DB_COMPANY.COMP_CODE
INNER JOIN MS_PARTNERS WITH(NOLOCK) ON TR_PURCHH.PARTNER_ID=MS_PARTNERS.ID                       
LEFT JOIN  MS_ITEMS_TYPE WITH(NOLOCK) ON MS_ITEMS_TYPE.ID = MS_ITEMS.ITEM_TYPE_ID
LEFT JOIN  MS_SUPP_MANUFACTURE WITH(NOLOCK) ON MS_ITEMS.SUPP_MANUFACTURE_ID=MS_SUPP_MANUFACTURE.ID
GROUP BY 
MS_SUPP_MANUFACTURE.NAME SUPPLIER,
MS_ITEMS.SUPPLIER_CODE SUPPLIER_CODE