help with nearest date query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

help with nearest date query

Author

Topic

calculon
Starting Member

3 Posts

Posted - 2004-01-29 : 23:47:22

I have two tables: orders and products. I'm trying to see if the entries in the orders table used the correct price when they were sold(the price of the product fluctuated). So when the order was made it should have been priced at the price nearest to its order date without going over. I'm really trying to see if there have been any overcharges.

I can't figure out how to do this without using a temporary table.

CREATE TABLE [orders] (
[Id] [char] (7) NULL,
[ProductId] [char] (11) NULL,
[Date] [smalldatetime] NULL,
[Price] [smallmoney] NULL
)
GO

CREATE TABLE [products] (
[ProductId] [char] (11) NULL,
[Date] [smalldatetime] NULL,
[Price] [smallmoney] NULL
)
GO

insert into products values(00355226254, '20000222', 1.65)
insert into products values(00355226254, '20000603', 1.67)
insert into products values(00355226254, '20010112', 1.51)
insert into products values(00355226254, '20020306', 1.64)

insert into orders values(7436284, 00355226254, '20000701', 1.68)
insert into orders values(7362752, 00355226254, '19990201', 1.91)
insert into orders values(7365752, 00355226254, '20010501', 1.66)
insert into orders values(7775752, 00355226254, '20020201', 1.58)
insert into orders values(7375482, 00355226254, '20000101', 1.44)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-01-30 : 00:18:12

Calculon,

I like that username, a Futurama fan I take it.

What is the required result from your example. I have a solution but it depends on the Product dat being less than the Order Date, therefore 2 of the orders are automatically rejected (1.91 & 1.44)


Select X.ID, P.Date AS ProductDate, O.Date as OrderDate, 
P.Price as ProductPrice, O.Price as OrderPrice, o.Price-p.Price as OverCharge from
(
Select ID, MAX(P.Date) as PDate, MIN(O.Date) as ODate
from Products P
INNER JOIN Orders O on P.ProductID = O.ProductID AND P.Date <= O.Date
GROUP BY ID
) X
INNER JOIN Orders O on O.ID = X.ID and ODate = O.Date
INNER JOIN Products P on P.Date = X.PDate
WHERE P.Price < O.Price

DavidM

"SQL-3 is an abomination.."

calculon
Starting Member

3 Posts

Posted - 2004-01-30 : 00:43:53

quote:
Originally posted by byrmol

Calculon,

I like that username, a Futurama fan I take it.

Hey byrmol, yep...I'm a Futurama fan.

quote:

What is the required result from your example. I have a solution but it depends on the Product dat being less than the Order Date, therefore 2 of the orders are automatically rejected (1.91 & 1.44)

The result I'm looking for is any transaction where an overcharge occured. I'm basically doing an audit and I have some old data I'm looking at. The price of the product changed and the companies selling the product should have used the most current price when they sold the product, whether it went up or down. Some companies didn't update their data when the price went down, for obvious reasons. So the price they used should have been the most recent price, whether it had changed the day of the transaction, two weeks before the transaction, or two years before the transaction. Whenever the last offical price changed occured. I hope that makes sense. Thanks for help.

quote:


Select X.ID, P.Date AS ProductDate, O.Date as OrderDate, 
P.Price as ProductPrice, O.Price as OrderPrice, o.Price-p.Price as OverCharge from
(
Select ID, MAX(P.Date) as PDate, MIN(O.Date) as ODate
from Products P
INNER JOIN Orders O on P.ProductID = O.ProductID AND P.Date <= O.Date
GROUP BY ID
) X
INNER JOIN Orders O on O.ID = X.ID and ODate = O.Date
INNER JOIN Products P on P.Date = X.PDate
WHERE P.Price < O.Price

DavidM

"SQL-3 is an abomination.."

I'm trying the code you posted right now...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources