February 15, 2012 at 8:37 am
Hi,
We have two similar files, one header file and one line file. Under certain conditions*, the line file is populated with a value in the ‘Linked_File_Name’ column. There are some examples where the column should have a value but is currently set to NULL.
* conditions
lf.Mill = hf.Mill
AND lf.Customer = hf.Customer
WHERE
lf.Date_Produced >= hf.Date_Produced
AND lf.[YearMonth] >= hf.[YearMonth]
(i.e. lf = Line_File AND hf = Header_File)
Here are a few examples in each of the files:
Header_File
File_NameCodeCustomerMillDate_ProducedYearMonth
CUL_20110815_aug11_sw,44,FF,CUL,20110815,201108
CUL_20110817_aug11_sw,44,FF,CUL,20110817,201108
CUL_20110824_aug11_sw,44,FF,CUL,20110824,201108
CUL_20110815_aug11_sw,45,FF,CUL,20110815,201108
CUL_20110816_aug11_sw,45,FF,CUL,20110816,201108
CUL_20110817_aug11_sw,45,FF,CUL,20110817,201108
CUL_20110824_aug11_sw,45,FF,CUL,20110824,201108
CUL_20110815_aug11_sw,47,FF,CUL,20110815,201108
CUL_20110817_aug11_sw,47,FF,CUL,20110817,201108
CUL_20110824_aug11_sw,47,FF,CUL,20110824,201108
CUL_20110815_aug11_sw,53,FF,CUL,20110815,201108
CUL_20110816_aug11_sw,53,FF,CUL,20110816,201108
CUL_20110817_aug11_sw,53,FF,CUL,20110817,201108
CUL_20110824_aug11_sw,53,FF,CUL,20110824,201108
Line_File
File_NameCodeCustomerMillDate_ProducedYearMonthLinked_File_Name
CUL_20110817_aug11_sw,44,FF,CUL,20110817,201108,CUL_20110817_aug11_sw
CUL_20110817_aug11_sw,45,FF,CUL,20110817,201108,CUL_20110817_aug11_sw
CUL_20110812_aug11_sw,47,FF,CUL,20110812,201108,NULL
CUL_20110818_aug11_sw,47,FF,CUL,20110818,201108,NULL
CUL_20110818_aug11_sw,53,FF,CUL,20110818,201108,NULL
In the above Line_File example, the first 2 records hold the correct value. However, whereas the 3rd record should be NULL due to the 'Date_Produced' condition, the 4th and 5th records should have values of 'CUL_20110817_aug11_sw'.
If I use the following query, it will obviously overwrite all of the Linked_File_Name fields with the same (top 1) value.
UPDATE dbo.Line_File
SET Linked_File_Name =
(SELECT TOP 1 hf.[File_Name] FROM dbo.Line_File lf LEFT JOIN dbo.Header_File hf ON
lf.Mill = hf.Mill
AND lf.Customer = hf.Customer
WHERE
lf.Date_Produced >= hf.Date_Produced
AND lf.[YearMonth] >= hf.[YearMonth]
AND (lf.Linked_File_Name IS NULL)
ORDER BY
hf.[YearMonth] DESC,
hf.Date_Produced DESC)
Any ideas please.
Thanks in advance,
Neal
February 15, 2012 at 9:25 am
Not 100% sure what you're after, but this should do the trick:
UPDATE lf
SET Linked_File_Name = (
SELECT TOP 1 hf.[File_Name]
FROM dbo.Header_File hf
WHERE lf.Date_Produced >= hf.Date_Produced
AND lf.[YearMonth] >= hf.[YearMonth]
AND lf.Mill = hf.Mill
AND lf.Customer = hf.Customer
AND lf.Linked_File_Name IS NULL
ORDER BY hf.[YearMonth] DESC,
hf.Date_Produced DESC
)
FROM dbo.Line_File AS lf
-- Gianluca Sartori
February 16, 2012 at 3:07 am
Thanks GianLuca - Think that solved the issue
February 16, 2012 at 3:21 am
You're welcome.
Happy to help
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply