SQL MIN Statement

I have the following query that I need to add the MIN statement to it. There can be multiple rows of the same PartNo if it is ordered multiple times on the same PONo, or different PO's. There can be multiple lines of the same PO with different LastPromiseDates. What I need to do is only show the PartNo once but using the earliest LastPromiseDate. I just need to see the earliest due date for a specific PartNo on a specific PO. The PartNo can also be on different PO's as well but I only want to see the first instance of that PartNo with the earliest LastPromiseDate. If it's on multiple PO's I only want to see the PO with the earliest LastPromiseDate. So my results would be 1 row for each PartNo and the earliest LastPromiseDate, and the PONo. So I'm guessing the MIN statement is what I would choose to use but just not sure how to add that into my query.

Here's a sample of the data before and after:

BEFORE
605015      2015-05-29 00:00:00.000      128655      OPEN                
605015      2015-06-19 00:00:00.000      128655      OPEN                
605015      2015-07-01 00:00:00.000      129026      OPEN        

AFTER
605015      2015-05-29 00:00:00.000      128655      OPEN                

Since this PartNo was ordered 3 times, twice on the same PO it shows 3 rows in the BEFORE query. After I will only see the PartNo once with the earliest LastPromsieDate
                   
SELECT        RTRIM(P2.fpartno) AS PartNo, P2.flstpdate AS LastPromiseDate, P1.fpono AS PONo, P1.fstatus AS POStatus
FROM            dbo.poitem AS P2 INNER JOIN
                         dbo.pomast AS P1 ON P1.fpono = P2.fpono
WHERE        (P1.fstatus = 'OPEN') AND (P2.fcategory = 'INV')
GROUP BY P2.fpartno, P2.flstpdate, P1.fpono, P1.fstatus

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
PONo    LastPromiseDates     PartNo  fStatus
605015	2015-05-29	      128655	OPEN
605015	2015-06-19	      128655	OPEN
605015	2015-07-01	      129026	OPEN

Open in new window


I have built a temp table to test the query I am building. Please add on or more POs some other variations to make sure it works okay.

Mike
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Here is some more data with BEFORE and AFTER

BEFORE
605122      05/20/2015      129311      OPEN                
605122      05/27/2015      129311      OPEN                
605122      12/16/2015      129341      OPEN                
605125      05/29/2015      129399      OPEN                
605141      05/19/2015      129297      OPEN                
605143      05/18/2015      129283      OPEN                
605143      05/28/2015      129347      OPEN                

AFTER
605122      05/20/2015      129311      OPEN                              
605125      05/29/2015      129399      OPEN                
605141      05/19/2015      129297      OPEN                
605143      05/18/2015      129283      OPEN
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
The PartNo is the first column, the PONo is the 3rd column. I noticed you had the headings backwards in your data that you posted. Just wanted to make sure you saw that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
This works okay for one PO but I possibly need to test it with multiple POs and get back to you shortly. Also revise column names I guess it wrong.
;WITH D
AS
(
Select 
rank() over(order by PONo ) As rn
, PONo
, LastPromiseDates
, PartNo
From #t)
Select 
 top 1 PONo
, LastPromiseDates 
, PartNo
From D 
Order By LastPromiseDates

Open in new window

0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok. No rush. I don't need this until tomorrow morning. I appreciate all your help!! I'm on the east coast and its getting late for me so I will check back in the morning. Thank you again!
0
Mike EghtebasDatabase and Application DeveloperCommented:
I see you are in MA, I was in Needham, MA last week for my kids graduation.

I think, the following should do:
;WITH D 
AS
(
Select 
rank() over(PARTITION BY PartNo order by LastPromiseDates ) As rk
, PONo , LastPromiseDates , PartNo , fStatus
From #t
)
Select PartNo , LastPromiseDates, PONo, fStatus
From D WHERE rk =1; 

Open in new window


produces:
605122      2015-05-20      129311      OPEN
605125      2015-05-29      129399      OPEN
605141      2015-05-19      129297      OPEN
605143      2015-05-18      129283      OPEN

Here is the temp table future readers may need:
create table #t (PONo int, LastPromiseDates date, PartNo int, fStatus varchar(10));
insert into #t(PartNo, LastPromiseDates, PONo, fStatus) values
(605122, '05/20/2015', 129311, 'OPEN')                
, (605122, '05/27/2015', 129311, 'OPEN')                
, (605122, '12/16/2015', 129341, 'OPEN')               
, (605125, '05/29/2015', 129399, 'OPEN')               
, (605141, '05/19/2015', 129297, 'OPEN')                
, (605143, '05/18/2015', 129283, 'OPEN')                 
, (605143, '05/28/2015', 129347, 'OPEN');

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yup. Born and raised in eastern MA. I used to work in Needham years ago too. Hope you had a good time!

I just tried running your code but I'm getting a Query Definitions Differ message that says "Unable to parse query text. I just copied your code exactly and pasted it into a query. Did I miss something?
0
Mike EghtebasDatabase and Application DeveloperCommented:
Could you try it with temp table above? If it works, then you need to do some detective work yourself to figure out why you are getting errors. For example, maybe you are not changing #t to the table name whatever table name you have or field names in my sample doesn't match yours.

Mike
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ahhhh ok. I thought it was just a simple copy and paste. My bad. I thought you wrote it based on the tables I had in my query. I have 2 tables with an INNER JOIN as my sources. Let me see if I can figure it out. I'll post back with my results.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ok I created the temp table based on your code but I can't see to locate it for the life of me. Where does it create the tables when you create them like that? I refreshed all my folders for my DB and can't locate it.
0
Mike EghtebasDatabase and Application DeveloperCommented:
A temp table physically doesn't exists. But you can run:

Select * Ftom #t;

to see the data in it.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'm still learning this SQL stuff. So I ran the create table code and it created it but now when I try to run that query I still get an error message. Am I supposed to run the create table code and the query together? Forgive me for asking but I'm still on the learning curve with SQL.
0
Mike EghtebasDatabase and Application DeveloperCommented:
After you crate and populate #t run Select * Ftom #t; to make sure the data is in it.

If you have created #t once, you cannot run it again because #t already exists. When you run a piece of t-sql code in SSMS, you need to select it and then run it. This way, it doesn't attempt to run everything on the page rather focuses on your selection only.

So, when ready to run the query itself, you need to select it first and then run it.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Gotcha. Let me give it another try. Does it matter that my source is coming from 2 tables though? Just not sure how to set it up for 2 tables when I make the changes to my query.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I based this question on the Before and after data. With this in mind you have the solution but in order to be of any use for you you need to have some more code to better hand shake with your tables.

I am heading to gym for a could of hours so you can either wail for me to get back to you (unless someone else jumps in to do the rest of it); or you can post a new question giving:

I have this before and after data and this solution works. But I want pull data from more than one table and asking for a solution.

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.