Aggregate function in update query with case statement in sql server

I am trying to write update query by using aggregate function and case statement.
Some how i am stuck

Initially i have written following query which gave me error tha,"Aggregate function can not be used in update statement

UPDATE report
SET report.LoadDischargeQty =
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' AND cargo.CRG_Quantity is NOT NULL THEN cargo.CRG_Quantity
ELSE
CASE WHEN report.PlaId = 'LP' THEN
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(SUM(CRG_SFgrMT),0) END -
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN ISNULL(SUM(CRG_SFgrMT),0)END
WHEN report.PlaId = 'DP' THEN
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN ISNULL(SUM(CRG_SFgrMT),0) END-
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(SUM(CRG_SFgrMT),0)END
ELSE 0 END
END
from #CargoPerformanceReport report
INNER JOIN POSCARGO cargo ON cargo.POS_ID = report.PositionId AND ISNULL(cargo.CRG_Deleted,0)=0


So I refactored it as follow

UPDATE report
SET report.LoadDischargeQty =
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' AND cargo.CRG_Quantity is NOT NULL THEN cargo.CRG_Quantity
ELSE
select quantity.dischargeQuantity from (SELECT CASE WHEN report.PlaId = 'LP' THEN
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(SUM(CRG_SFgrMT),0) END -
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN ISNULL(SUM(CRG_SFgrMT),0)END
WHEN report.PlaId = 'DP' THEN
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN ISNULL(SUM(CRG_SFgrMT),0) END-
CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(SUM(CRG_SFgrMT),0)END
ELSE 0 END dischargeQuantity ) quantity
END
from #CargoPerformanceReport report
INNER JOIN POSCARGO cargo ON cargo.POS_ID = report.PositionId AND ISNULL(cargo.CRG_Deleted,0)=0


table structure

CREATE TABLE #CargoPerformanceReport
(
PositionId VARCHAR(12),
PortAndActivityName VARCHAR(100),
PlaId VARCHAR(12),
LoadDischargeQty REAL,

);


Insert into #CargoPerformanceReport
Values('100',null,'LP',null)

CREATE TABLE #Poscargo
(
POS_ID VARCHAR(12),
CRG_ArrDep VARCHAR(3),
CRG_SFgrMT REAL,
CRG_Quantity REAL,
CRG_Deleted BIT
);


Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT,null,0)
Values ('100','DD',100)

Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT)
Values ('100','AD',100)

Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT)
Values ('100','DD',200)

Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT)
Values ('100','AD',50)


Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT)
Values ('101','DL',200)


Insert Into #Poscargo(POS_ID,CRG_ArrDep,CRG_SFgrMT)
Values ('101','AL',200)

SELECT * FROM #Poscargo
SELECT * FROM #CargoPerformanceReport


result:-

PositionId | PlaId | LoadDischargeQty
100 | LP | 150


but it is not right way and also has error.

anyone have optimized solution for the same?

CRG_Quantity

CRG_Deleted

CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(CRG_SFgrMT,0) END - CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN ISNULL(CRG_SFgrMT,0) END

CASE WHEN SUBSTRING(CRG_ArrDep,1,1) = 'D' THEN ISNULL(CRG_SFgrMT,0) WHEN SUBSTRING(CRG_ArrDep,1,1) = 'A' THEN -ISNULL(CRG_SFgrMT,0) END

2 Answers
2

Put all that stuff into subquery:

SELECT LoadDischargeQty =
CASE
WHEN report.PlaId = 'LP'
THEN 1
ELSE -1
END * cargo.qty
FROM CargoPerformanceReport report
CROSS APPLY(
SELECT SUM(
CASE
WHEN CRG_ArrDep LIKE 'D%'
THEN 1
WHEN CRG_ArrDep LIKE 'A%'
THEN -1
ELSE 0
END * ISNULL(CRG_SFgrMT, 0)
) qty
FROM POSCARGO cargo
WHERE cargo.POS_ID = report.PositionId
AND ISNULL(cargo.CRG_Deleted,0)=0
) cargo


http://sqlfiddle.com/#!18/648d0/7

I don't understand how is CRG_Quantity column supposed to be used and you did not provide any row with that data so I removed it to show you the aggregation itself. Works fine, you'll get your 150. You may easily convert it into update statement.

CRG_Quantity

update

Perhaps something like this?

It uses cases with summed cases.

Test on SQL Fiddle here

UPDATE t
SET LoadDischargeQty = q.CalcDischargeQty
FROM #CargoPerformanceReport t
JOIN
(
SELECT report.PositionId, report.PlaId,
CASE
WHEN SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'D' THEN cargo.CRG_Quantity END) IS NOT NULL
THEN SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'D' THEN cargo.CRG_Quantity END)
ELSE CASE
WHEN report.PlaId = 'LP'
THEN SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'D' THEN cargo.CRG_SFgrMT END) -
SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'A' THEN cargo.CRG_SFgrMT END)
WHEN report.PlaId = 'DP'
THEN SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'A' THEN cargo.CRG_SFgrMT END) -
SUM(CASE WHEN LEFT(cargo.CRG_ArrDep,1) = 'D' THEN cargo.CRG_SFgrMT END)
ELSE 0
END
END AS CalcDischargeQty
FROM #CargoPerformanceReport report
INNER JOIN #Poscargo cargo
ON cargo.POS_ID = report.PositionId AND (cargo.CRG_Deleted = 0 OR cargo.CRG_Deleted IS NULL)
GROUP BY report.PositionId, report.PlaId
) q ON t.PositionId = q.PositionId;


Sample Data

IF OBJECT_ID('tempdb..#CargoPerformanceReport') IS NOT NULL DROP TABLE #CargoPerformanceReport;
CREATE TABLE #CargoPerformanceReport
(
PositionId VARCHAR(12) PRIMARY KEY,
PortAndActivityName VARCHAR(100),
PlaId VARCHAR(12),
LoadDischargeQty REAL
);

IF OBJECT_ID('tempdb..#Poscargo') IS NOT NULL DROP TABLE #Poscargo;
CREATE TABLE #Poscargo
(
POS_ID VARCHAR(12),
CRG_ArrDep VARCHAR(3),
CRG_SFgrMT REAL,
CRG_Quantity REAL,
CRG_Deleted BIT
);

Insert into #CargoPerformanceReport Values
('100','name1','LP',null),
('101','name2','DP',null),
('102','name3','DP',null);

Insert Into #Poscargo(POS_ID, CRG_ArrDep, CRG_SFgrMT, CRG_Quantity, CRG_Deleted) Values
('100','DD',100,null,0)
,('100','AD',100,null,0)
,('100','DD',200,null,0)
,('100','AD',50,null,0)
,('101','DL',100,null,0)
,('101','AL',200,null,0)
,('102','DL',100,500,0)
,('102','AL',200,null,0);


Result

PositionId PortAndActivityName PlaId LoadDischargeQty
---------- ------------------- ----- ----------------
100 name1 LP 150
101 name2 DP 100
102 name3 DP 500


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.