max function on two tables - SQL Server Forums

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 function on two tables

Author

Topic

frani444
Starting Member

4 Posts

Posted - 2007-05-18 : 00:47:43

hey everyone.
I'm new too SQL and have been having a little trouble trying to make an invoice report.
I have a database for a fake shop and want to pull out 5 colums of information from two different tables and only display the most recent case(latest date).

The 5 pieces of information i want displayed are called:
Product .ProductNo, Product .Description, Product .UnitPrice, Sale_Line.Qty, Sale_Line.SalePrice
...and saledate is stored in Invoice.SaleDate

My current attempts doesn't work(or even run) but i have included it below:

SELECT Invoice.SaleDate, Product .ProductNo, Product .Description, Product .UnitPrice, Sale_Line.Qty, Sale_Line.SalePrice
FROM Invoice INNER JOIN
Sale_Line ON Invoice.InvoiceNo = Sale_Line.InvoiceNo INNER JOIN
Product ON Sale_Line.ProductNo = Product .ProductNo
(SELECT max(SaleDate) AS maxdate
FROM Invoice) maxresults
WHERE Invoice.SaleDate = maxresults.maxdate;

....Any help with this problem would be much appreciated!
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-18 : 00:56:34

try this


SELECT 	Invoice.SaleDate, Product.ProductNo, Product.Description, Product.UnitPrice, Sale_Line.Qty, Sale_Line.SalePrice
FROM 	Invoice 
INNER JOIN Sale_Line 	ON Invoice.InvoiceNo 	= Sale_Line.InvoiceNo 
INNER JOIN Product 	ON Sale_Line.ProductNo 	= Product.ProductNo
WHERE Invoice.SaleDate = (SELECT max(SaleDate) FROM Invoice)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-18 : 00:57:18

[code]SELECT
Invoice.SaleDate, Product .ProductNo, Product .Description, Product .UnitPrice, Sale_Line.Qty, Sale_Line.SalePrice
FROM
Invoice JOIN Sale_Line ON Invoice.InvoiceNo = Sale_Line.InvoiceNo
JOIN Product ON Sale_Line.ProductNo = Product .ProductNo
Join (SELECT max(SaleDate) AS maxdate FROM Invoice) maxresults on Invoice.SaleDate = maxresults.maxdate[/code]

Posting some sample data and expected output also might help get you more accurate and efficient answer.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-18 : 01:03:33

Arghhh!!

Mere 44 seconds apart..!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

frani444
Starting Member

4 Posts

Posted - 2007-05-18 : 01:42:33

yeah that solved the problem...thanks a lot guys for the fast reply...much appreciated!

sibirus
Starting Member

1 Post

Posted - 2007-05-21 : 16:02:26

how about this query?

SELECT tblApt.Id, tblApt.Name, tblApt.MSA, Max([tblAptDet].[DHiRate]/[tblApt].[AverageSF]) AS Expr1

FROM tblApt INNER JOIN tblAptDet ON (tblApt.LastUpdated = tblAptDet.Period) AND (tblApt.Id = tblAptDet.Id)

GROUP BY tblApt.Id, tblApt.Name, tblApt.MSA, tblApt.AverageSF, tblAptDet.DHiRate, tblAptDet.PlanNumber

HAVING (((tblApt.MSA)=1920) AND ((tblAptDet.PlanNumber)=5))
ORDER BY Max([tblAptDet].[DHiRate]/[tblApt].[AverageSF]) DESC;

But I need Max([tblAptDet].[DHiRate]/[tblApt].[AverageSF]) to return only single number. Is it possible to do in just one query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources