Update Values from Sum of Select Statement in SQL Server -
@dt tvp_api_delivery_order readonly update stkm set stkm.itm_qty_onhand = case when tvp.flag='i' (itm_qty_onhand- (select sum(t1.orderqty) @dt t1 t1.itemcode=tvp.itemcode)) when tvp.flag='u' ((itm_qty_onhand+(select sum(t2.dod_qty) dod t2 t2.itm_cd=tvp.itemcode , t2.do_no=@orderno , t2.do_year=@year))-(select sum(t3.orderqty) @dt t3 t3.itemcode=tvp.itemcode)) when tvp.flag='d' (itm_qty_onhand + (select sum(t4.dod_qty) dod t4 t4.itm_cd=tvp.itemcode , t4.do_no=@orderno , t4.do_year=@year)) else 0 end @dt tvp inner join stkm on tvp.itemcode=stkm.itm_cd
how simplify above query. there other method faster execution above query? iam updating values table value parameter (tvp) table
try way helpful you: if found difficulty let me know. @dt tvp_api_delivery_order readonly update stkm set stkm.itm_qty_onhand = case when tvp.flag='i' (itm_qty_onhand- tmporderqunatity.orderquantity) when tvp.flag='u' ((itm_qty_onhand+tmpdodqunatity.dodquantity)-tmporderqunatity.orderquantity) when tvp.flag='d' (itm_qty_onhand + tmpdodqunatity.dodquantity) else 0 end @dt tvp inner join stkm on tvp.itemcode=stkm.itm_cd cross apply ( select sum(t1.orderqty) orderquantity @dt t1 t1.itemcode=tvp.itemcode )as tmporderqunatity cross apply ( select sum(t2.dod_qty) dodquantity @dt t2 t2.itemcode=tvp.itemcode , t2.do_no=@orderno , t2.do_year=@year )as tmpdodqunatity
Comments
Post a Comment