Min(), MAX() Question

Hi,
I have two tables : Table1, Table2
CREATE TABLE [dbo].[Table1] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SaleDate] [datetime] NULL ,
[Price] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[Product] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReportDate] [datetime] NULL ,
[ReportPrice] [decimal](18, 2) NULL ,
[Customer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-- And here is some data
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',10.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Jan 1 2005
12:00:00:000AM',9.50,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',9.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 1 2005
12:00:00:000AM',6.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Feb 2 2005
12:00:00:000AM',7.00,'001')
INSERT INTO [Table1]
([Product],[SaleDate],[Price],[Customer]
)VALUES('A','Oct 10 2005
12:00:00:000AM',30.00,'001')
INSERT INTO [Table2]
([Product],[ReportDate],[ReportPrice],[C
ustomer])VALUES('A','May 1 2005
12:00:00:000AM',0,'001')
I need a query to update ReportPrice From Table2 with the Maximum Saledate
from table1 less then ReportDate from Table2 and the price=Min for that
date for that specific date.
This is my query
UPDATE t2 SET t2.ReportPrice=t1.Price FROM
(
Select Max(SaleDate) as SaleDate,Min(Price) AS PRICE ,Customer,Product From
Table1
GROUP BY Customer,Product) as t1,Table2 as t2
WHERE t1.SaleDate< t2.ReportDate and t1.Product = t2.Product and
t1.Customer=t2.Customer
Select * FROM Table1
Select * FROM Table2
, but is wrong .. I get 6 instead of 7 . I need 7 to be the ReportPrice.
Thanks guys !Do:
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer ) )
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
With a t-SQL TOP clause in the subquery, it should be a bit more simpler
though:
UPDATE table2
SET ReportPrice = (
SELECT TOP 1 Price FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
ORDER BY table1.SaleDate DESC, Price )
WHERE ...
Anith|||Is not working !
The ideea is good but i need ReportPrice to be 7. In this case will be 30 .
I need somewhere a condition MAX( SaleDate ) <ReportDate
Thanks
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>|||Thanks!
Is working now !
UPDATE table2
SET ReportPrice =
( SELECT MIN( Price ) FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer
AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1 t1
WHERE table1.Product = t1.Product
AND table1.Customer = t1.Customer and
t1.SaleDate<Table2.ReportDate )
)
WHERE EXISTS
( SELECT * FROM table1
WHERE table1.Product = table2.Product
AND table1.Customer = table2.Customer ) ;
Select * FROM Table1
Select * FROM Table2
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ef4W3Z2pFHA.3104@.TK2MSFTNGP12.phx.gbl...
> Do:
> UPDATE table2
> SET ReportPrice =
> ( SELECT MIN( Price ) FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> AND table1.SaleDate = ( SELECT MAX( t1.SaleDate ) FROM Table1
> t1
> WHERE table1.Product = t1.Product
> AND table1.Customer =
> t1.Customer ) )
> WHERE EXISTS
> ( SELECT * FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer ) ;
> With a t-SQL TOP clause in the subquery, it should be a bit more simpler
> though:
> UPDATE table2
> SET ReportPrice = (
> SELECT TOP 1 Price FROM table1
> WHERE table1.Product = table2.Product
> AND table1.Customer = table2.Customer
> ORDER BY table1.SaleDate DESC, Price )
> WHERE ...
> --
> Anith
>