Sage Evolution
Space shortcuts
Page tree

Versions Compared

Old Version 1

changes.mady.by.user Alexander Toufexis

Saved on

compared with

New Version 2

changes.mady.by.user Alexander Toufexis

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


Stock valuation - updated with job costing crap

Code Block
languagesql
 Select
 ItemCode =S.Code,
 ItemDescription1 =S.Description_1,
 ItemDescription2 =S.Description_2,
 ItemGroupCode =G.StGroup,
 ItemGroupDesc = G.Description,
 Qty =
  SUM(CASE 
  WHEN Debit > 0 THEN Quantity
  ELSE -Quantity 
 END)
 --,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
 ,SV = SUM(CASE
			WHEN PST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
			WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 THEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
			WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PST.Quantity*PST.Cost
			WHEN PST.Credit > 0 AND PST.Id IN ('OInv', 'Crn') THEN -PST.Quantity*PST.Cost
			WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
			ELSE 0 END)

 from PostST P
 LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
 LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
 LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
 WHERE P.TxDate <='2015/12/31' 
	AND S.ServiceItem = 0
 GROUP BY
 S.Code,
 S.Description_1,
 S.Description_2,
 S.ItemGroup,
 G.StGroup,
 G.Description
 Order by  S.Code



Stock valuation - query v1

Code Block
 Select
 ItemCode =S.Code,
 ItemDescription1 =S.Description_1,
 ItemDescription2 =S.Description_2,
 ItemGroupCode =G.StGroup,
 ItemGroupDesc = G.Description,
 Qty =
  SUM(CASE 
  WHEN Debit > 0 THEN Quantity
  ELSE -Quantity 
 END)
 --,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
 ,SV = SUM(CASE
		WHEN P.Id NOT IN ('OInv', 'Crn') THEN ROUND(P.Debit,2)-ROUND(P.Credit,2)
		WHEN Debit > 0 AND P.Id IN ('OInv', 'Crn') THEN P.Quantity*P.Cost
		WHEN Credit > 0 AND P.Id IN ('OInv', 'Crn') THEN -P.Quantity*P.Cost
		ELSE 0 END)

 from PostST P
 LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
 LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
 LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
 WHERE P.TxDate <='2015/12/31' 
	AND S.ServiceItem = 0
 GROUP BY
 S.Code,
 S.Description_1,
 S.Description_2,
 S.ItemGroup,
 G.StGroup,
 G.Description
 Order by  S.Code

...

Stock valuation - incl avg cost and by WH

Code Block
languagesql


Select
 ItemCode =S.Code,
 ItemDescription1 =S.Description_1,
 ItemDescription2 =S.Description_2,
 ItemGroupCode =G.StGroup,
 ItemGroupDesc = G.Description,
 W.Code as WHCode,
 Qty =
  SUM(CASE 
  WHEN Debit > 0 THEN Quantity
  ELSE -Quantity 
 END)
 --,StockValue =SUM( ROUND(Debit,2)-ROUND(Credit,2))
 ,SV = SUM(CASE
			WHEN PPST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PPST.Debit,2)-ROUND(PPST.Credit,2)
			WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
			WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 AND PTHEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
			WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PPST.Quantity*PPST.Cost
			WHEN PST.Credit > 0 AND PPST.Id IN ('OInv', 'Crn') THEN -PPST.Quantity*PPST.Cost
			WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
			ELSE 0 END)
 , SVAvgCost = CASE WHEN SUM(CASE  WHEN Debit > 0 THEN Quantity ELSE -Quantity  END) <> 0 THEN
	 SUM(CASE
			WHEN PPST.Id NOT IN ('OInv', 'Crn','JCS','JCM','OGrv','JCI') THEN ROUND(PPST.Debit,2)-ROUND(PPST.Credit,2)
			WHEN PST.Id = 'JCM' THEN PST.Debit --note:exclude any credits! they are crediting the WIP acc, not the Stock acc.
			WHEN PST.Id = 'JCS' THEN ROUND(PST.Credit,2)-ROUND(PST.Debit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink = 0 THEN ROUND(PST.Debit,2)-ROUND(PST.Credit,2)
			WHEN PST.Id = 'OGrv' AND PST.JobCodeLink > 0 AND PTHEN 0 --if job code link is not 0, then the stock goes straight to WIP (so not stock account).
			WHEN PST.Debit > 0 AND PST.Id IN ('OInv', 'Crn') THEN PPST.Quantity*PPST.Cost
			WHEN PST.Credit > 0 AND PPST.Id IN ('OInv', 'Crn') THEN -PPST.Quantity*PPST.Cost
			WHEN PST.Id = 'JCI' THEN 0 -- JCI invoices get their cost from the WIP account, therefore 0 influence on stock account
			ELSE 0 END) / 
		 SUM(CASE  WHEN Debit > 0 THEN Quantity ELSE -Quantity  END)
		ELSE 0 END
 , WS.fAverageCost as WHAvgCost

 from PostST P
 LEFT JOIN StkItem S ON P.AccountLink = S.StockLink
 LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup
 LEFT JOIN WhseMst W on WhseLink = P.WarehouseID
 LEFT JOIN WhseStk WS ON P.WarehouseID = WS.WHWhseID AND P.AccountLink = WS.WHStockLink
 WHERE P.TxDate <='2018-12-31' 
	AND S.ServiceItem = 0
	AND S.StockLink = 53
 GROUP BY
 S.Code,
 W.Code,
 S.Description_1,
 WS.fAverageCost,
 S.Description_2,
 S.ItemGroup,
 G.StGroup,
 G.Description
 Order by  S.Code