CODEWITHSUNDEEP
sqlsql-servert-sql
beejm
beejm

Reputation: 2481

SQL Set SUM IN Update with inner join

I'm having issues updating a column of my temp table in sql. Here is my query.

UPDATE #temp
SET #temp.totalcredit = 
SUM(CASE WHEN c.ratetype = 'NRP' THEN d.Credit ELSE d.Credit * f.equivalent END)
from #temp a 
inner join theader b
on a.IV = b.docnum
and a.SOANUM is not null
inner join BillRun c
on rtrim(replace(
    case
        when charindex('-',b.comments,1) <> 6 AND charindex('-',b.comments,1) > 0
        then substring(ltrim(rtrim(b.comments)),1, charindex('-',b.comments,1)-1)
        when charindex(' ',b.comments,1) > 0
        then substring(ltrim(rtrim(b.comments)),1, charindex(' ',b.comments,1)-1)
        else left(ltrim( comments),10)
    end,'''','')) = c.BillRunCode OR c.BillRunCode = b.runcode
inner join BillRunProposalSummary d
on c.BillRunCode = d.BillRunCode and d.Credit > 0
inner join configforexrates f
on f.date = c.DocDate

Is there a way to get the sum and still update it this way?

Upvotes: 0

Views: 509

Answers (1)

3BK
3BK

Reputation: 1348

Have you tried something like this?

UPDATE A
SET A.TotalCredit = T.Credit
FROM #temp A
CROSS APPLY
(
SELECT SUM(
    CASE C.RateType 
        WHEN 'NRP' THEN D.Credit 
    ELSE 
        D.Credit * F.Equivalent 
    END) AS Credit
FROM Theader B
inner join BillRun c
on rtrim(replace(
    case
        when charindex('-',b.comments,1) <> 6 AND charindex('-',b.comments,1) > 0
        then substring(ltrim(rtrim(b.comments)),1, charindex('-',b.comments,1)-1)
        when charindex(' ',b.comments,1) > 0
        then substring(ltrim(rtrim(b.comments)),1, charindex(' ',b.comments,1)-1)
        else left(ltrim( comments),10)
    end,'''','')) = c.BillRunCode OR c.BillRunCode = b.runcode
inner join BillRunProposalSummary d
on c.BillRunCode = d.BillRunCode and d.Credit > 0
inner join configforexrates f
on f.date = c.DocDate
WHERE B.DocNum = A.IV
) T
WHERE A.SOANUM IS NOT NULL
;

Upvotes: 1

Related Questions