Hi,
I am trying to update a date field using the MIN() function. when I run the code below, I just get one date result, instead of a result for each appropriate row in the temp table. Any one any ideas on what I am doing wrong here. All date fields are SMALLDATETIME.
UPDATE i
SET datefst = (SELECT MIN(ar.sales_date)
FROM prodSales ar
JOIN #TCI i
ON i.ProdCode = ar.Prodcode
AND i.catl = ar.catl
WHERE ar.sales_date BETWEEN i.fdate AND i.End_Date)
FROM #TCI i
JOIN prodSales ar
ON i.ProdCode = ar.ProdCode
AND i.catl = ar.catl
AND i.datefst IS NULL;
Many thanks