August 22, 2011 at 8:29 am
I would like to combine 2 conditional update scripts into 1 if possible. They are below. One updates if column IsBillingArea = 1 and the other if it = 0. If IsBillingArea = 1 then I have to have an INNER JOIN in the FROM statement. Is it possible to combine these? I tried using a CASE but fail in all my attempts but I am guessing I was doing it wrong.
UPDATE b
SET b.OPPORTUNITIES =
(
SELECT COUNT(a.INVNUM)
FROM invoices a INNER JOIN BillingAreas d ON d.BILLINGAREA = a.BA
WHERE b.DIVISION = d.BILLINGAREA AND b.PROVIDER = a.PROVIDER AND b.MEASURE = a.MEASURE
AND b.PERIOD = CONVERT(VARCHAR(6), INV_SER_DT, 112)
)
FROM ProvAnal b
WHERE b.IsBillingArea = 1
GO
UPDATE b
SET b.OPPORTUNITIES =
(
SELECT COUNT(a.INVNUM)
FROM invoices a
WHERE b.DIVISION = a.DIVISION AND b.PROVIDER = a.PROVIDER AND b.MEASURE = a.MEASURE
AND b.PERIOD = CONVERT(VARCHAR(6), INV_SER_DT, 112)
)
FROM ProvAnal b
WHERE b.IsBillingArea = 0
Thanks for any help you might provided.
Lee
August 22, 2011 at 8:37 am
You can but you won't see a huge speed improvement from this and it'll be a little harder to read for the juniors.
Turn the 2 updates into a select, the rest will be easy to figure out to do an update instead.
SELECT *, CASE WHEN IsBillingArea = 1 THEN dt1.ColName ELSE dt2.ColName END AS MergeColName
FROM ProvAnal
LEFT OUTER JOIN ( SELECT COUNT(a.INVNUM)
FROM invoices a INNER JOIN BillingAreas d ON d.BILLINGAREA = a.BA
WHERE b.DIVISION = d.BILLINGAREA AND b.PROVIDER = a.PROVIDER AND b.MEASURE = a.MEASURE
AND b.PERIOD = CONVERT(VARCHAR(6), INV_SER_DT, 112)
) dt1 ON...
LEFT OUTER JOIN
(SELECT COUNT(a.INVNUM)
FROM invoices a
WHERE b.DIVISION = a.DIVISION AND b.PROVIDER = a.PROVIDER AND b.MEASURE = a.MEASURE
AND b.PERIOD = CONVERT(VARCHAR(6), INV_SER_DT, 112)
) dt2 on...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply