How can I make use of the interval between 2 dates in this? 1

[stigejg]

Hello,

I am working with something that for a while ago saw to be an easy job, but now I have used most of the day trying to solve this problem. I have tryed all around me that I thought could help me whitout any luck. So now I have to try you folks out there.

I have two tabels called pricelist, and Production.

The pricelist has those colomns

-Company
-Facility
-Item number
-Costing date from
-Costing date to
-Costing sum 1


The production table contains:

-Company
-Facility
-Product number
-Component number
-Start date
-Transaction quantity basic unit
-Transaction price

Where

[pricelist].[Company]=[production].[Company]
[pricelist].[Facility]=[production].[Facility]
[pricelist].[Item number]=[production].[Component number]


So far so good, and here it all stops.

Is there anyone out there who can tell me how I can use the [Start date] go into the pricelist between [Costing date from]

and [Costing date to] take the [Costing sum 1] and put it into the [Transaction price].

I have tryed the:

CASE WHEN [Start date] BETWEEN [Costing date from] AND [Costing date to]
THEN [Costing sum 1]
ELSE NULL (or skipping this Else clase)
END

This worked in a way when the price came OK into the right date, but the rest was set to NULL but when the priceslit has 120 000 records and the production table has 1 000 000 records, it wasn't possible to use the nice result i got.

So no I hope there is someone out there who can help me with this problem.

Thanks

Stig

 

[bcking]

I'm not sure if I understand but would this work.

Update Production set [Transaction Price] =
(select [Costing Sum 1] from pricelist
where [pricelist].[Company]=[production].[Company] and
[pricelist].[Facility]=[production].[Facility] and
[pricelist].[Item number]=[production].[Component number]
and Production.[Start date] BETWEEN [pricelist].[[Costing
date from] AND [pricelist].[[Costing date to])

I haven't tested the SQL, have I got the where clause in the correct place?

 

[tlbroadbent]

Your question should be posted in forum183 (Microsoft SQL Server: Programming) instead of this forum.

Here is another way to perform the update.

Update pr Set [Transaction Price] = pl.[Costing sum 1]
From Production pr
Join Pricelist pl
On pr.Company = pl.Company
And pr.Facility = pl.Facility
And pr.[Component number] = pl.[Item number]
And pr.[Start date] BETWEEN pl.[Costing date from] AND pl.[Costing date to]


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[stigejg]

Hi Terry,

Thank you, your tips is just what I needed. It works like a dream.