Updating field in Temporary table

Question

I have a more massive sp, in which I use some temporary tables, below is a part of the sp where I am updating a field in a temporary table two times.

Update #BalSheet 
set OBCr = OBDr + isnull(trialDeb.amount,0) 
from (
    Select v.AccountId,
        v.CompanyId, 
        Sum(isnull(v.Amount,0)) Amount 
    From #vwJournalDetails v , #TempJournalHeader h 
    where v.JournalCredit = 1 and // 1
        h.JournalId = v.JournalId and
        h.CompanyId = v.CompanyId and 
        h.JournalDate >= @dtPrevFinUnclosedSDate and
        h.JournalDate <= @dtLastFinUnclosedEDate 
    group by v.CompanyId, v.AccountId
) trialDeb
Where trialDeb.Accountid = #BalSheet.AccId and 
    trialDeb.CompanyId = #BalSheet.CompanyID and 
    trialDeb.CompanyId = @intCompanyId and 
    #BalSheet.AcType ='L'

Update #BalSheet 
set OBCr = OBDr - isnull(trialDeb.amount,0) 
from (
    Select v.AccountId,
        v.CompanyId, 
        Sum(isnull(v.Amount,0)) Amount 
    From #vwJournalDetails v , #TempJournalHeader h 
    where v.JournalCredit = 0 and // 2
        h.JournalId = v.JournalId and
        h.CompanyId = v.CompanyId and 
        h.JournalDate >= @dtPrevFinUnclosedSDate and
        h.JournalDate <= @dtLastFinUnclosedEDate 
    group by v.CompanyId, v.AccountId
) trialDeb
Where trialDeb.Accountid = #BalSheet.AccId and 
    trialDeb.CompanyId = #BalSheet.CompanyID and 
    trialDeb.CompanyId = @intCompanyId and 
    #BalSheet.AcType ='L'

As you can see, the two update query looks similar, and the only difference is in the where condition checking v.JournalCredit value.

If the v.JournalCredit = 1 I add the amount to the current value in the table, and when the v.JournalCredit = 0 I subtract the amount from the current value.

it is working correctly, but there are other update queries similar to this in the sp, So I was wondering if there is any way to mix these update query into one?

Wes H · Accepted Answer · 2019-07-15 17:14:04Z

You can use a CASE statement in your SET to choose the operation to perform. Not only will this make maintenance easier, it will improve performance as well since you'll only be touching the target table once.

UPDATE  #BalSheet
SET OBCr = CASE
             WHEN trialDeb.JournalCredit = 1 THEN OBDr + ISNULL(trialDeb.Amount, 0)
             WHEN trialDeb.JournalCredit = 0 THEN OBDr - ISNULL(trialDeb.Amount, 0)
           END
FROM  ( SELECT  v.AccountId,
                v.CompanyId,
                v.JournalCredit,
                SUM(ISNULL(v.Amount, 0)) AS Amount
        FROM  #vwJournalDetails AS v,
              #TempJournalHeader AS h
        WHERE v.JournalCredit IN ( 0, 1 )
          AND h.JournalId = v.JournalId
          AND h.CompanyId = v.CompanyId
          AND h.JournalDate >= @dtPrevFinUnclosedSDate
          AND h.JournalDate <= @dtLastFinUnclosedEDate
        GROUP BY v.CompanyId,
                 v.AccountId,
                 v.JournalCredit) AS trialDeb
WHERE trialDeb.Accountid = #BalSheet.AccId
  AND trialDeb.CompanyId = #BalSheet.CompanyID
  AND trialDeb.CompanyId = @intCompanyId
  AND #BalSheet.AcType = 'L' ;

Note that I don't know if you still need the v.JournalCredit filter so I just changed it to an IN clause to ensure only 0 & 1 were touched. If those are the only two possible values, you can remove that predicate to improve the performance as well.

Stack Exchange Network

Updating field in Temporary table

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Updating field in Temporary table

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions