Dates 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 2005 Forums

Transact-SQL (2005)

Dates Query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-08-28 : 07:36:58

Hi i have a query i need a hand with. For a report i need to report on price dates for each delivery. The delivery date will be greater than the price date but the price could have change since that delivery date so it has to pick the correct date for that price.

Example if the delivery date was on the 01/05/2008 it would pick the price date of the 31/04/2008 as this was the price made just before that date.

Example delivery_dates Example price_dates
01/03/2008 01/02/2008
01/05/2008 31/04/2008
10/07/2008 02/05/2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-28 : 07:47:36

quote:
Example delivery_dates Example price_dates
01/03/2008 01/02/2008
01/05/2008 31/04/2008
10/07/2008 02/05/2008

Is is your required result or sample data ?

KH
[spoiler]Time is always against us[/spoiler]

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-08-28 : 07:52:02

required results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 07:54:13

What will be your table data? and explain how you think you'll get the above output out of them.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-08-28 : 08:15:47

the table data will have a supplier code and a delivery date along with the product description ,price ,qty

The references table will have a supplier code also price_date abd the price

I'll use the example data for this
Example delivery_dates Example price_dates
01/03/2008 01/02/2008
01/05/2008 31/04/2008
10/07/2008 02/05/2008

I was trying to get it to work by doing it in a view the join will be on the supplier code and i will be return the delivery date and the price for that delivery date based on the price date less than the deliver date but am stuck on this as it always returns the min price date.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-28 : 09:15:22

can you post your sample data and explain how to get from the sample data into the required result ?

KH
[spoiler]Time is always against us[/spoiler]

JeriHatTrick
Starting Member

11 Posts

Posted - 2008-08-28 : 15:10:49

Try this:

CREATE TABLE Supplier (
supplier_code TINYINT,
delivery_date SMALLDATETIME,
)

CREATE TABLE Pricing (
supplier_code TINYINT,
pricing_date SMALLDATETIME
)

INSERT INTO Supplier
SELECT 1,'2008-03-01'
UNION
SELECT 1,'2008-05-01'
UNION
SELECT 1,'2008-07-10'
UNION
SELECT 2,'2008-08-01'

INSERT INTO Pricing
SELECT 1,'2008-02-01'
UNION
SELECT 1,'2008-04-30'
UNION
SELECT 1,'2008-05-02'
UNION
SELECT 2,'2008-07-30'

SELECT * FROM Supplier
SELECT * FROM Pricing

SELECT
S.Supplier_code,
S.delivery_date,
P.pricing_date
FROM Supplier S
CROSS APPLY
(SELECT TOP 1 pricing_date
FROM Pricing A
WHERE A.pricing_date <+ S.delivery_date AND
A.supplier_code = A.supplier_code
ORDER BY pricing_date DESC
) P

JeriHatTrick
Starting Member

11 Posts

Posted - 2008-08-28 : 15:12:08

There is a typo:

In the CROSS APPLY change "<+" to "<=".
This is what the results are:
1 2008-03-01 00:00:00 2008-02-01 00:00:00
1 2008-05-01 00:00:00 2008-04-30 00:00:00
1 2008-07-10 00:00:00 2008-05-02 00:00:00
2 2008-08-01 00:00:00 2008-07-30 00:00:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources