Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max Date Query

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-14 : 13:08:29
Ok I am out of my league here and I need some guidance. I have done pretty basic queries and stored procedures but I am stuck on this query which is hopefully simple for you guys.

Here is the query as I have tried it.
SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM items INNER JOIN
porders ON items.PONum = porders.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID
WHERE (items.PartNum = '2122K256') AND (porders.Date =
(SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster - Carr Supply')


I have three tables. porders, items, and vendors. Porders and Items have a one to many relationship. Vendors is just to query by the VendorName instead of VendorID.

I am trying to do a select query but I want to pull the records that match my inputs (PartNum & VendorName) but I only want to pull up the record that in the using the highest Date in the porders.Date field. So basically it should only be returning one record. Mine is returning 10 records.
Because there will be several records that match my query I just want to get the record with the highest date. Anyone know how to do that?
Thanks,
Stacy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-14 : 14:11:02
Try adding another condition to the where clause of the inner query as shown below. However, the logic is not entirely clear to me. Can one Partnum+VendorName combination have more than one PONum on the same date?
WHERE  (items.PartNum = '2122K256')
AND (
porders.Date =(
SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date)
AND lookup.PONum = porders.PONum
)
)
AND (vendors.VendorName LIKE 'McMaster - Carr Supply')
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-14 : 14:29:42
quote:
Originally posted by sunitabeck

Try adding another condition to the where clause of the inner query as shown below. However, the logic is not entirely clear to me. Can one Partnum+VendorName combination have more than one PONum on the same date?
WHERE  (items.PartNum = '2122K256')
AND (
porders.Date =(
SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date)
AND lookup.PONum = porders.PONum
)
)
AND (vendors.VendorName LIKE 'McMaster - Carr Supply')



It would not be likely that there would be duplicate PO's on the same date for the same part from the same vendor. I actually don't even need to return the porders.Date field I just want to use it to return the fields in the other tables but for the PO with the highest date. The way my query is now it is returning all the PO's that have that part# for specified Vendor. I only want it to return basically the last time it was ordered (highest Date).
Stacy
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-25 : 15:16:44
I get an error adding in the red code from above. Error - The multi-part identifier "lookup-PONum" could not be bound.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 15:24:51
quote:
Originally posted by StacyOW

I get an error adding in the red code from above. Error - The multi-part identifier "lookup-PONum" could not be bound.

Did you use "lookup.PONum" or "lookup-PONum"? It should be the former.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-26 : 10:55:26
I entered it as lookup.PONum and I still get the error. This is what I have right now.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM items INNER JOIN
porders ON items.PONum = porders.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID
WHERE (items.PartNum = '2122K256') AND (porders.Date =
(SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 11:28:03
Does the table porders have a column named PONum?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-26 : 11:55:18
quote:
Originally posted by StacyOW

I entered it as lookup.PONum and I still get the error. This is what I have right now.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM items INNER JOIN
porders ON items.PONum = porders.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID
WHERE (items.PartNum = '2122K256') AND (porders.Date =
(SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum
))



is this the full query?

if yes, you're missing some braces

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-26 : 15:42:38
quote:
Originally posted by sunitabeck

Does the table porders have a column named PONum?



Yes the PONum is my Key field and it is auto-increment.
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-26 : 15:44:44
Yes this is the query as I have it right now. Where am I missing braces?
Thank you for your help.
Stacy


quote:
Originally posted by visakh16

quote:
Originally posted by StacyOW

I entered it as lookup.PONum and I still get the error. This is what I have right now.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM items INNER JOIN
porders ON items.PONum = porders.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID
WHERE (items.PartNum = '2122K256') AND (porders.Date =
(SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum
))



is this the full query?

if yes, you're missing some braces

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-26 : 15:50:47
If I do the following query it works but I don't get just the Max Date I get every record that the part was ordered from McMaster. I only want to get the Highest Date or the last time it was ordered.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM items INNER JOIN
porders ON items.PONum = porders.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID
WHERE (items.PartNum = '2122K256') AND (porders.Date =
(SELECT MAX(Date) AS Expr1
FROM porders AS lookup
WHERE (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster')
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-26 : 16:21:32
I'm not sure the query makes sense. Specifically, the predicate is correlated on the DATE. So, you are going to get a MAX date where the date = date..?

Here is a guess at what you want. But, if that doesn’t work, it’ll be easier if you can post the DDL, DML and Expected output so we can help you better. Here are some links that might help with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT     
items.EquipmentID,
items.DepartmentID,
items.Type,
items.ComponentNum,
items.PartNum,
items.ItemDescription,
items.QtyOrd,
items.QtyRcvd,
items.UnitPrice,
items.ItemUpdate,
items.Price,
items.Copy,
items.WorkNum,
items.UpdateOrdered,
items.Modified,
porders.[Date],
items.ItemID,
items.PONum
FROM
items
INNER JOIN
porders
ON items.PONum = porders.PONum
INNER JOIN
vendors
ON porders.VendorID = vendors.VendorID

INNER JOIN
(
SELECT
PONum,
MAX([Date]) AS MaxOrderDate
FROM
porders
GROUP BY
PONum
) AS MaxOrder
ON porders.PONum = MaxOrder.PONum
AND porders.[Date] = MaxOrder.MaxOrderDate
WHERE
items.PartNum = '2122K256'
AND vendors.VendorName LIKE 'McMaster - Carr Supply'
EDIT: Forgot to move the vedor reference out of the derived-table.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 16:38:18
My suggestion is close to Lamprey's, except I the porders table is only joined through subselect.

select items.EquipmentID
,items.DepartmentID
,items.Type
,items.ComponentNum
,items.PartNum
,items.ItemDescription
,items.QtyOrd
,items.QtyRcvd
,items.UnitPrice
,items.ItemUpdate
,items.Price
,items.Copy
,items.WorkNum
,items.UpdateOrdered
,items.Modified
,porders.Date
,items.ItemID
,items.PONum
from items
inner join (select PONum
,max(Date) as Date
from porders
group by PONum
) as porders
on porders.PONum=items.PONum
inner join vendors
on vendors.VendorID=porders.VendorID
where items.PartNum='2122K256'
and vendors.VendorName like 'McMaster'
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-26 : 17:06:19
BitsMead, you lost the ability to join to vendors on the VendorID. But, I like the though process. Perhaps change from MAX(Date) and GROUP BY to use a ROW_NUMBER funciton so you can include the VendorID..?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-26 : 17:17:07
@Lamprey: You are right - I totally missed that.
@StacyOW: Forget my suggestion and go with Lamprey's suggestion

Edit: I really really need to learn the over() function. I think this might work, but my experience with over(), is very very limited


select items.EquipmentID
,items.DepartmentID
,items.Type
,items.ComponentNum
,items.PartNum
,items.ItemDescription
,items.QtyOrd
,items.QtyRcvd
,items.UnitPrice
,items.ItemUpdate
,items.Price
,items.Copy
,items.WorkNum
,items.UpdateOrdered
,items.Modified
,porders.Date
,items.ItemID
,items.PONum
from items
inner join (select row_number() over(partition PONum order by Date desc) as rownum
,*
from porders
) as porders
on porders.PONum=items.PONum
and porders.rownum=1
inner join vendors
on vendors.VendorID=porders.VendorID
where items.PartNum='2122K256'
and vendors.VendorName like 'McMaster'
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-26 : 19:20:43
Tried that query but I get lots of things underlined, specifically Incorrect syntax near 'PONum'.
I'm not sure how else to explain what I am trying to do - than in my first post.

quote:
I have three tables. porders, items, and vendors joined together. Porders and Items have a one to many relationship. Vendors is linked to porders by VendorID just so I can query by the VendorName instead of VendorID.

I am trying to do a select query but I want to pull the records (from the porders table) that match my variables for PartNum & VendorName but I only want to pull up the record that has the highest Date. By the porders.Date field. So basically it should only be returning one record. Mine is returning 10 records. The query I was showing in my original post was returning all of the records for McMaster with my variable Part#. I only want to get the last time it was ordered or the highest date!


quote:
Originally posted by bitsmed

@Lamprey: You are right - I totally missed that.
@StacyOW: Forget my suggestion and go with Lamprey's suggestion

Edit: I really really need to learn the over() function. I think this might work, but my experience with over(), is very very limited


select items.EquipmentID
,items.DepartmentID
,items.Type
,items.ComponentNum
,items.PartNum
,items.ItemDescription
,items.QtyOrd
,items.QtyRcvd
,items.UnitPrice
,items.ItemUpdate
,items.Price
,items.Copy
,items.WorkNum
,items.UpdateOrdered
,items.Modified
,porders.Date
,items.ItemID
,items.PONum
from items
inner join (select row_number() over(partition PONum order by Date desc) as rownum
,*
from porders
) as porders
on porders.PONum=items.PONum
and porders.rownum=1
inner join vendors
on vendors.VendorID=porders.VendorID
where items.PartNum='2122K256'
and vendors.VendorName like 'McMaster'


Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-26 : 19:31:01
I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword:
over(partition BY PONum order by Date desc)


http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-27 : 20:30:21
quote:
Originally posted by Lamprey

I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword:
over(partition BY PONum order by Date desc)


http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx





I'm not sure what a DDL or DML is but I have provide the query as I was trying to do it, which didn't work. I can give you the table fields if that will help, but I can't see how. I already had the table linked in my tried query in my example.
The porders table looks like this.
PONum	int	Unchecked - Key - auto-increment
VendorID int Checked
OriginatorID int Checked
StatusID int Checked
ShippedID int Checked
Date date Unchecked
MPONum varchar(10) Checked
DateRequired date Checked
RefPONum real Checked
ShipTo varchar(30) Checked
ShipAddress varchar(25) Checked
ShipCity varchar(15) Checked
ShipPhone varchar(12) Checked
ShipState varchar(2) Checked
ShipZip varchar(10) Checked
Tax decimal(18, 2) Checked
Freight decimal(18, 2) Checked
PrintStatus varchar(3) Checked
POTotal money Checked
Comments varchar(250) Checked
Description varchar(250) Checked
Modified timestamp Unchecked


The Items Table looks like this.
ItemID	int	Unchecked - key - auto-increment
PONum int Unchecked
EquipmentID int Unchecked
DepartmentID int Unchecked
Type varchar(1) Checked
ComponentNum int Checked
PartNum varchar(50) Checked
ItemDescription varchar(150) Checked
QtyOrd real Checked
QtyRcvd real Checked
UnitPrice real Checked
ItemUpdate varchar(3) Checked
Price real Checked
Copy varchar(3) Checked
WorkNum varchar(12) Checked
UpdateOrdered varchar(3) Checked
Modified timestamp Unchecked

The Vendor table looks like this...
VendorID	int	Unchecked - Key - Auto-increment
VendorName varchar(50) Unchecked
Address varchar(50) Checked
Address2 varchar(50) Checked
City varchar(50) Checked
State varchar(50) Checked
Zip varchar(10) Checked
Phone varchar(50) Checked
Fax varchar(50) Checked
VendorType varchar(50) Checked
ProductsServices varchar(250) Checked
Comments varchar(250) Checked
MinOrdAmnt varchar(25) Checked
WebAddress varchar(50) Checked
Modified timestamp Unchecked


The query I had originally tried was in my first post. It would give me what I want but not the Highest/Max Date - it would return All the records from that Vendor for that Part#, not the last time it was ordered.
Sorry I am new to this and not sure exactly what you need. I thought by giving you the query sample and the explanation of what I was trying to do would be enough. I can't believe it is this hard to do a max date query. Just when I was really starting to like sql.
Stacy
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-09-30 : 11:56:28
quote:
Originally posted by Lamprey

I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword:
over(partition BY PONum order by Date desc)


http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx




tried the partition By but I still get 15 records it is not just returning the highest date.
Thanks for all you help with this.
Stacy
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:24:16
can you try providing some sample data from your tables and try explaining the output you want from them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2012-10-24 : 14:00:57
quote:
Originally posted by visakh16

can you try providing some sample data from your tables and try explaining the output you want from them?


I'm not exactly sure how to provide you with sample data.

I have a form on which the user enters the date, a vendorID, etc. which is in the porders table. I have an items table linked one to many to the porders table and the items table has all of the items that are ordered for each porder. From the items table when the user enters a part# and then leaves that field I wanted to run a query to bring up the last time (Max Date) that part# was ordered from that Vendor. I can get the query to run but I brings up every time that part# was ordered from the vendor not just one record/the LAST time it was ordered. I have attached an image of the table data my query is returning at this point, so you can see that it is returning 11 records. I just can't seem to get it to do the max date part of the query. Perhaps there is some other way to do a query like this but in my old database program I used MaxDate so I just thought that I would use that in SQL server also.

Sorry I can't seem to find a way to add an image from my computer to this post.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------



It would look this this though...
EquipmentID DepartmentID Type ComponentNum PartNum ItemDescription QtyOrd QtyRcvd UnitPrice Price Date ItemID PoNum VendorName

I don't know if that helps. I could email you the image if that would help. Let me know.

Stacy
Go to Top of Page
    Next Page

- Advertisement -