UPDATE S
SET [Date] = CONVERT(datetime, CAST(Maxdate AS CHAR(8)), 112)
, [LoadStatus] = CASE WHEN Maxdate = CAST(CONVERT(char(8), Dateadd(dd,-1,GETDATE()), 112) AS INT) THEN 1 ELSE 0 END
FROM rpt.DHWStatus S
INNER JOIN
( SELECT B.CompanyKey, max([DateKey]) MaxDate
FROM fact.Sales S
INNER JOIN
dim.Branch B
ON S.BranchKey = B.BranchKey
WHERE [DateKey] < CAST(CONVERT(char(8), GETDATE (), 112) AS INT)
GROUP BY B.CompanyKey
) T ON S.CompanyKey = T.CompanyKey
SET [Date] = CONVERT(datetime, CAST(Maxdate AS CHAR(8)), 112)
, [LoadStatus] = CASE WHEN Maxdate = CAST(CONVERT(char(8), Dateadd(dd,-1,GETDATE()), 112) AS INT) THEN 1 ELSE 0 END
FROM rpt.DHWStatus S
INNER JOIN
( SELECT B.CompanyKey, max([DateKey]) MaxDate
FROM fact.Sales S
INNER JOIN
dim.Branch B
ON S.BranchKey = B.BranchKey
WHERE [DateKey] < CAST(CONVERT(char(8), GETDATE (), 112) AS INT)
GROUP BY B.CompanyKey
) T ON S.CompanyKey = T.CompanyKey