Forum Discussion
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%'