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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy