Page History

select  S.code, S.Description_1,S.ServiceItem, WG.StGroup, WG.Description as WHGroupDesc
	, StockAccount.Master_Sub_Account + ' - ' + StockAccount.Description as StockAccount
	, CoSAccount.Master_Sub_Account + ' - ' + CoSAccount.Description as CosAccount
	, P.iGLAccountID as PostST_iGLAccountID, StockAccount.AccountLink as StockAcc_GLID
	, P.cAuditNumber
	, Qty = CASE WHEN Debit > 0 THEN P.Quantity ELSE -P.Quantity END
	, StockValue = CASE
		WHEN Debit > 0 AND P.Id NOT IN ('OInv', 'Crn') THEN ROUND(P.Debit,2)-ROUND(Quantity*P.Credit,2)Cost
		WHEN DebitCredit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
		WHEN CreditP.Id > = 'JCI' THEN 0 AND --jc invoice - takes out WIP.
		WHEN P.Id NOT IN ('OInv', 'Crn') THEN -ROUND(P.Quantity*Debit,2)-ROUND(P.CostCredit,2)
		ELSE 0 END --to handle more ID types, especially job costing ones!
	,* 
	from PostST P
	left join StkItem S ON P.AccountLink = S.StockLink
	left join GrpTbl G ON S.ItemGroup = G.StGroup
	left join WhseStk WS ON P.WarehouseID = WS.IdWhseStk AND P.AccountLink = WS.WHStockLink
	left join GrpTbl WG on WS.WHStockGroup = WG.StGroup
	left join TrCodes ON P.TrCodeID = TrCodes.idTrCodes
	left join Accounts SalesAccount ON WG.SalesAccLink = SalesAccount.AccountLink
	left join Accounts CoSAccount ON CoSAccount.AccountLink =  CASE WHEN COALESCE(WG.COSAccLink,0) <> 0 THEN  WG.COSAccLink WHEN COALESCE(G.COSAccLink,0) <> 0 THEN G.COSAccLink ELSE TrCodes.Account2Link END
	left join Accounts StockAccount ON StockAccount.AccountLink = CASE WHEN COALESCE(WG.StockAccLink,0) <> 0 THEN  WG.StockAccLink WHEN COALESCE(G.StockAccLink,0) <> 0 THEN G.StockAccLink ELSE TrCodes.Account1Link END
	ORDER BY P.AutoIdx