SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max Date Query
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 09/14/2012 :  13:08:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/14/2012 :  14:11:02  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/14/2012 :  14:29:42  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/25/2012 :  15:16:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  15:24:51  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/26/2012 :  10:55:26  Show Profile  Reply with Quote
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

Edited by - StacyOW on 09/26/2012 10:56:39
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/26/2012 :  11:55:18  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/26/2012 :  15:42:38  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/26/2012 :  15:44:44  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/26/2012 :  15:50:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 09/26/2012 :  16:21:32  Show Profile  Reply with Quote
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.

Edited by - Lamprey on 09/26/2012 16:24:35
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 09/26/2012 :  16:38:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 09/26/2012 :  17:06:19  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

277 Posts

Posted - 09/26/2012 :  17:17:07  Show Profile  Reply with Quote
@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'

Edited by - bitsmed on 09/26/2012 17:45:39
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 09/26/2012 :  19:20:43  Show Profile  Reply with Quote
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'



Edited by - StacyOW on 09/26/2012 19:21:17
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4361 Posts

Posted - 09/26/2012 :  19:31:01  Show Profile  Reply with Quote
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


Edited by - Lamprey on 09/26/2012 19:31:24
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 09/27/2012 :  20:30:21  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 09/30/2012 :  11:56:28  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 10/01/2012 :  10:24:16  Show Profile  Reply with Quote
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

USA
87 Posts

Posted - 10/24/2012 :  14:00:57  Show Profile  Reply with Quote
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

Edited by - StacyOW on 10/24/2012 14:21:12
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000