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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/24/2012 :  14:14:56  Show Profile  Reply with Quote
quote:
Originally posted by StacyOW

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.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






see format to give sample data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Go to Top of Page

StacyOW
Yak Posting Veteran

USA
93 Posts

Posted - 10/24/2012 :  21:29:36  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by StacyOW

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.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






see format to give sample data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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




Ok here is the table information for Porders...

USE [MT]
GO

/****** Object: Table [dbo].[porders] Script Date: 10/24/2012 19:57:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[porders](
[PONum] [int] IDENTITY(1,1) NOT NULL,
[VendorID] [int] NULL,
[OriginatorID] [int] NULL,
[StatusID] [int] NULL,
[ShippedID] [int] NULL,
[Date] [date] NOT NULL,
[MPONum] [varchar](10) NULL,
[DateRequired] [date] NULL,
[RefPONum] [real] NULL,
[ShipTo] [varchar](30) NULL,
[ShipAddress] [varchar](25) NULL,
[ShipCity] [varchar](15) NULL,
[ShipPhone] [varchar](12) NULL,
[ShipState] [varchar](2) NULL,
[ShipZip] [varchar](10) NULL,
[Tax] [decimal](18, 2) NULL,
[Freight] [decimal](18, 2) NULL,
[PrintStatus] [varchar](3) NULL,
[POTotal] [money] NULL,
[Comments] [varchar](250) NULL,
[Description] [varchar](250) NULL,
[Modified] [timestamp] NOT NULL,
CONSTRAINT [PK_porders] PRIMARY KEY CLUSTERED
(
[PONum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Here is Items Table information...
USE [MT]
GO

/****** Object: Table [dbo].[items] Script Date: 10/24/2012 19:59:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[PONum] [int] NOT NULL,
[EquipmentID] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
[Type] [varchar](1) NULL,
[ComponentNum] [int] NULL,
[PartNum] [varchar](50) NULL,
[ItemDescription] [varchar](150) NULL,
[QtyOrd] [real] NULL,
[QtyRcvd] [real] NULL,
[UnitPrice] [real] NULL,
[ItemUpdate] [varchar](3) NULL,
[Price] [real] NULL,
[Copy] [varchar](3) NULL,
[WorkNum] [varchar](12) NULL,
[UpdateOrdered] [varchar](3) NULL,
[Modified] [timestamp] NOT NULL,
CONSTRAINT [PK_items] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and Vendors Table Information...
USE [MT]
GO

/****** Object: Table [dbo].[vendors] Script Date: 10/24/2012 20:00:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[vendors](
[VendorID] [int] IDENTITY(1,1) NOT NULL,
[VendorName] [varchar](50) NOT NULL,
[Address] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](10) NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](50) NULL,
[VendorType] [varchar](50) NULL,
[ProductsServices] [varchar](250) NULL,
[Comments] [varchar](250) NULL,
[MinOrdAmnt] [varchar](25) NULL,
[WebAddress] [varchar](50) NULL,
[Modified] [timestamp] NOT NULL,
CONSTRAINT [PK_vendors_1] PRIMARY KEY CLUSTERED
(
[VendorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

The directions are different than the version I am using so I hope that was what you needed. Next I will attempt to get you some data.
I have never used an insert into query to add data to all the fields. I imported all the data from another database or we enter it using a winform program I built.

Porders Data:

INSERT INTO porders (PONum,VendorID, OriginatorID, StatusID, ShippedID, Date,MPONum, DateRequired, RefPONum, ShipTo, ShipAddress, ShipCity,ShipPhone, ShipState, ShipZip, Tax, Freight, PrintStatus,POTotal, Comments, Description)
VALUES (9687, 790, 1, 17, 2, 2008-05-06, 'M9687', 2008-05-07, 'NULL','Tilsner Carton Company', '162 York Avenue East', 'St. Paul', 612-227-8261, 'MN', 55117, 0.00, 19.35, 'NULL', 'NULL', 'NULL','NULL')

GO

INSERT INTO porders (PONum,VendorID, OriginatorID, StatusID, ShippedID, Date,MPONum, DateRequired, RefPONum, ShipTo, ShipAddress, ShipCity,ShipPhone, ShipState, ShipZip, Tax, Freight, PrintStatus,POTotal, Comments, Description)
VALUES (10241, 790, 1, 17, 2, 2009-03-23, 'M10241', 2009-03-24, 'NULL','Tilsner Carton Company', '162 York Avenue East', 'St. Paul', 612-227-8261, 'MN', 55117, 0.00, 17.49, 'NULL', 'NULL', 'NULL','NULL')

GO

INSERT INTO porders (PONum,VendorID, OriginatorID, StatusID, ShippedID, Date,MPONum, DateRequired, RefPONum, ShipTo, ShipAddress, ShipCity,ShipPhone, ShipState, ShipZip, Tax, Freight, PrintStatus,POTotal, Comments, Description)
VALUES (11547, 790, 1, 17, 2, 2011-05-03, 'M11547', 2011-05-04, 'NULL','Tilsner Carton Company', '162 York Avenue East', 'St. Paul', 612-227-8261, 'MN', 55117, 0.00, 72.28, 'NULL', 'NULL', 'NULL','NULL')

GO


Vendors Data:
INSERT INTO vendors (VendorID, VendorName, Address, Address2, City, State, Zip, Phone, Fax, VendorType, ProductsServices, Comments, MinOrdAmnt, WebAddress)
VALUES (790, 'McMaster', '600 County Line Rd.', 'P.O. Box 4355', 'Elmhurst', 'IL', 60126-2081, 630-833-0300, 630-834-9427, 'Supplier', 'Industrial supplies', 'On line user name, NULL, NULL)

GO

Items Data:
INSERT INTO items (ItemID, PONum, EquipmentID, DepartmentID, Type, ComponentNum, PartNum, ItemDescription, QtyOrd, QtyRcvd, UnitPrice, ItemUpdate, Price, Copy, WorkNum,UpdateOrdered)
VALUES (4923, 9687, 155, 1, 'M', 0, '2122K256', ,Fresh air intake filter (bulk 90' roll)', 3, 3, 58.31, 174.93, 'Yes')

GO

INSERT INTO items (ItemID, PONum, EquipmentID, DepartmentID, Type, ComponentNum, PartNum, ItemDescription, QtyOrd, QtyRcvd, UnitPrice, ItemUpdate, Price, Copy, WorkNum,UpdateOrdered)
VALUES (6970, 10241, 155, 1, 'M', 0, '2122K256', ,Fresh air intake filter (bulk 90' roll)', 3, 3, 61.45, 184.35, 'Yes')

GO


INSERT INTO items (ItemID, PONum, EquipmentID, DepartmentID, Type, ComponentNum, PartNum, ItemDescription, QtyOrd, QtyRcvd, UnitPrice, ItemUpdate, Price, Copy, WorkNum,UpdateOrdered)
VALUES (12112, 11547, 155, 1, 'M', 0, '2122K256', ,Fresh air intake filter (bulk 90' roll)', 4, 4, 66.96, 267.84, 'Yes')

GO

visakh16 - OK I think you have everything you need - I hope. I really appreciate all your help with this. I have tried this query several different ways and I can get it to give me the Vendor and Part# I am looking for but I can't get it to give me the Highest Date (porder - Date) for that part# and vendor.

Stacy

Edited by - StacyOW on 10/25/2012 11:49:27
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
93 Posts

Posted - 10/30/2012 :  21:58:21  Show Profile  Reply with Quote
Ok I had this working earlier this evening and now it won't work. This is the query I had working.
SELECT     vendors.VendorName, items.ItemDescription, items.PartNum, items.ComponentNum, items.UnitPrice
FROM         porders INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID INNER JOIN
                      items ON porders.PONum = items.PONum
WHERE     (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS porders_1
                            WHERE      (VendorID = @zVendor1))) AND (items.PartNum LIKE '%' + @zPart + '%')

I don't have a clue why it won't work now.
Thanks,
Stacy
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
93 Posts

Posted - 10/31/2012 :  15:17:28  Show Profile  Reply with Quote
I got it working!
Here is what I ended up needing to do.
SELECT VendorName,
       ItemDescription,
       PartNum,
       ComponentNum,
       UnitPrice
FROM   (SELECT vendors.VendorName,
               items.ItemDescription,
               items.PartNum,
               items.ComponentNum,
               items.UnitPrice,
               Row_number()
                 OVER(
                   partition BY porders.VendorID, items.PartNum
                   ORDER BY porders.Date DESC ) rn
        FROM   porders
               INNER JOIN vendors
                       ON porders.VendorID = vendors.VendorID
               INNER JOIN items
                       ON porders.PONum = items.PONum
        WHERE  porders.VendorID = @zVendors
               AND items.PartNum LIKE '%' + @zPart + '%') t
WHERE  rn = 1

Thanks!
Stacy
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.14 seconds. Powered By: Snitz Forums 2000