Forum Discussion

AhmadGiveSteel's avatar
AhmadGiveSteel
Copper Contributor
Dec 14, 2022

Grouping a SUM in a Having statement inside a case when inside an update

I have this piece of SQL where I want to sum pv.[ACCOUNTINGCURRENCYAMOUNT] by some variables that are contained in the table pv. I have tried to put in the group by clause in different places in the code, with no correct result. Can anyone point me in the right direction as to where I should place the group by clause. 

 

Thanks in advance!

 

UPDATE s
SET s.ProjId =
CASE WHEN d.PROJECT = 'N/A' OR d.PROJECT = '009021' THEN
ISNULL((SELECT TOP 1 ProjId FROM [dbo].[tmpProjTransPostingVouchers] pv
WHERE
s.SUBLEDGERVOUCHER = pv.VOUCHER AND s.DATAAREA = PV.OPTdataareaid
Sgroup by ProjId
Having
s.[ACCOUNTINGCURRENCYAMOUNT] = sum(pv.[ACCOUNTINGCURRENCYAMOUNT]) pvgroup by ProjID, VOUCHER, OPTDATAAREAID, LEDGERDIMENSION),'')


ELSE d.PROJECT END

FROM stLedgerTrans s
inner join dDimensionUnion d on s.LEDGERDIMENSION = d.CombinationId
INNER JOIN [dbo].[eLedgerPostingType] lp ON s.POSTINGTYPE = lp.ENUMITEMVALUE AND lp.PostingType like '%proj%'

No RepliesBe the first to reply

Resources