Referencing CTE Multiple Times for Updates 1

[briangriffin]

I'm trying to update a summary table multiple times using a cte for the values - it works fine the first time, but then I get "Invalid object name cte_receipts"

This works:

with cte_receipts as
(select 
BillingID,
InsuranceID,
count(BillingID) as ReceiptCount,
sum(Amount) as ReceiptTotal,
max(BatchDateTime) as LatestReceipt

from Livendb.dbo.BarCollectionTransactions
where Type = 'R'

group by BillingID, InsuranceID)
	
update OSUMC_MT6.dbo.OSUMC_BarSummary
set Insurance1NumberOfReceipts = b.ReceiptCount,
Insurance1LatestReceipt = b.LatestReceipt
from OSUMC_MT6.dbo.OSUMC_BarSummary a inner join cte_receipts b
	on a.BillingID = b.BillingID
	and a.Insurance1 = b.InsuranceID;

But adding additional updates generates the error:

with cte_receipts as
(select 
BillingID,
InsuranceID,
count(BillingID) as ReceiptCount,
sum(Amount) as ReceiptTotal,
max(BatchDateTime) as LatestReceipt

from Livendb.dbo.BarCollectionTransactions
where Type = 'R'

group by BillingID, InsuranceID)
	
update OSUMC_MT6.dbo.OSUMC_BarSummary
set Insurance1NumberOfReceipts = b.ReceiptCount,
Insurance1LatestReceipt = b.LatestReceipt
from OSUMC_MT6.dbo.OSUMC_BarSummary a inner join cte_receipts b
	on a.BillingID = b.BillingID
	and a.Insurance1 = b.InsuranceID

[bold]
update OSUMC_MT6.dbo.OSUMC_BarSummary
set Insurance2NumberOfReceipts = c.ReceiptCount,
Insurance2LatestReceipt = c.LatestReceipt
from OSUMC_MT6.dbo.OSUMC_BarSummary a inner join cte_receipts c
	on a.BillingID = c.BillingID
	and a.Insurance2 = c.InsuranceID
[/bold]

Surely I won't need to redeclare the cte for each update - what am I missing?

Thanks in advance.

 

[gmmastros]

Unfortunately you do need to repeat the CTE for each query. Alternatively, you could replace the CTE with a temp table or table variable.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Thank you George. Always appreciate your contributions.

 

[gmmastros]

Now that I look a little closer, you may be able to use this:

with cte_receipts as
(
  select  BillingID,
          InsuranceID,
          count(BillingID) as ReceiptCount,
          sum(Amount) as ReceiptTotal,
          max(BatchDateTime) as LatestReceipt
  from    Livendb.dbo.BarCollectionTransactions
  where   Type = 'R'
  group by BillingID, InsuranceID
)
update	OSUMC_MT6.dbo.OSUMC_BarSummary
set     Insurance1NumberOfReceipts = Case When A.Insurance1 = b.InsuranceId 
                                          Then B.ReceiptCount 
                                          Else Insurance1NumberOfReceipts 
                                          End,
        Insurance1LatestReceipt =    Case When A.Insurance1 = B.InsuranceId 
		                          Then b.LatestReceipt
                                          Else A.Insurance1LatestReceipt
                                          End,
        Insurance2NumberOfReceipts = Case When A.Insurance1 = b.InsuranceId 
                                          Then B.ReceiptCount 
                                          Else Insurance2NumberOfReceipts 
                                          End,
        Insurance2LatestReceipt =    Case When A.Insurance1 = B.InsuranceId 
		                          Then b.LatestReceipt
                                          Else A.Insurance2LatestReceipt
                                          End
from	OSUMC_MT6.dbo.OSUMC_BarSummary a 
        inner join cte_receipts b
          on a.BillingID = b.BillingID
          And b.InsuranceId In (B.Insurance1, B.Insurance2)

Note that I combined your 2 update queries in to 1.

I don't necessarily advocate doing this. I think it's important that SQL Code be readable and I think this code is unnecessarily complex because of the way it is done. If you decide to use this code, I strongly encourage you to test it a lot to make sure it works properly. I think I would only use this code if there was a significant difference in performance.

Basically, I would expect this code to run faster, but it is more complex so it may be more difficult to maintain. Only if there is a big difference in performance would I even consider using this.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom