SELECT O.documenttextID, OP.final_price,OP.products_quantity,OP.lineAmount,OP.tktMisc [Loose Qty],op.tktSPPercentage [Loose Rate], FSTPO.Sremarks,[RR].locationDetail [DIS DRUM],RR.ordersid,[FSTPO].fileNo,RRPO.locationDetail [PODRUM],DHPO.qty as [PODHqty],
P.products_model,FSTO.sRemarks AS DISPATCHBATCHNO,O.date_purchased, DA [REF FST],FSTO.doctypeId,FSTO.filenO,OP.products_id FROM ORDERS [O]
LEFT JOIN orders_products [OP] ON O.orders_id = OP.orders_id
LEFT JOIN CL_METRO_FST.DBO.ORDERS AS [FSTO] ON FSTO.orders_id = ISNULL(DA,0)
LEFT JOIN CL_METRO_FST.DBO.DISPENSEHISTORY [DH] ON DH.batchNo = FSTO.sRemarks
LEFT JOIN CL_METRO_FST.DBO.REgisterRFID [RR] ON RR.RFID = DH.RFID
LEFT JOIN CL_METRO_FST.DBO.ORDERS AS [FSTPO] ON FSTPO.orders_id = RR.ORDERSID
LEFT JOIN CL_METRO_FST.DBO.DISPENSEHISTORY [DHPO] ON [DHPO].batchNo = FSTPO.sRemarks
LEFT JOIN CL_METRO_FST.DBO.REgisterRFID [RRPO] ON [RRPO].RFID = [DHPO].RFID
LEFT JOIN CL_METRO_FST.DBO.products [P] ON P.products_id = DHPO.itemId
WHERE O.doctypeId = 89 AND–O.documentTextId = ‘12210850’ AND
OP.products_id =53817
AND FSTO.fileNo = ‘dispatch’
AND FSTPO.sRemarks = ‘7E66009114’
ORDER BY P.products_model,DHPO.qty
SELECT
[RR].locationDetail [DIS DRUM]
,MAX([RR].NetQty) NetDrumQty
,SUM(OP.products_quantity) SoldQty
,SUM(OP.lineAmount) TotalSale
,SUM(OP.tktMisc) [Sold Loose Qty]
–,op.tktSPPercentage [Loose Rate]
–O.documenttextID, OP.final_price
–, FSTPO.Sremarks,RR.ordersid,[FSTPO].fileNo,RRPO.locationDetail [PODRUM],DHPO.qty as [PODHqty],
–P.products_model,FSTO.sRemarks AS DISPATCHBATCHNO,O.date_purchased, DA [REF FST],FSTO.doctypeId,FSTO.filenO,OP.products_id
FROM ORDERS [O]
LEFT JOIN orders_products [OP] ON O.orders_id = OP.orders_id
LEFT JOIN CL_METRO_FST.DBO.ORDERS AS [FSTO] ON FSTO.orders_id = ISNULL(DA,0)
LEFT JOIN CL_METRO_FST.DBO.DISPENSEHISTORY [DH] ON DH.batchNo = FSTO.sRemarks
LEFT JOIN CL_METRO_FST.DBO.REgisterRFID [RR] ON RR.RFID = DH.RFID
LEFT JOIN CL_METRO_FST.DBO.ORDERS AS [FSTPO] ON FSTPO.orders_id = RR.ORDERSID
LEFT JOIN CL_METRO_FST.DBO.DISPENSEHISTORY [DHPO] ON [DHPO].batchNo = FSTPO.sRemarks
LEFT JOIN CL_METRO_FST.DBO.REgisterRFID [RRPO] ON [RRPO].RFID = [DHPO].RFID
LEFT JOIN CL_METRO_FST.DBO.products [P] ON P.products_id = DHPO.itemId
WHERE O.doctypeId = 89 AND–O.documentTextId = ‘12210850’ AND
OP.products_id =53817
AND FSTO.fileNo = ‘dispatch’
AND FSTPO.sRemarks = ‘7E66009114’
GROUP BY [RR].locationDetail
–ORDER BY P.products_model,DHPO.qty
Report 1
RM DRUM DRUM Cost, DRUM WT, DRUM BAL WT, DRUM SOLD QTY, DRUM SOLD Profit sum of Drum Profit IN FG
FG DRUM, DRUM Ratio, FG_Cost, FG Initial Qty, FG Sold, FG Balance, FG SOLD Amount, FG Profit on Sold Qty, DRUM Ratio in FG , Drum Profit IN FG
FG DRUM
RM DRUM
Report 2:
SUMMARY
RM DRUM
FG Sold (SoldQty + SOLD Lose Qty)
, FG SOLD Amount
FG 500 KG
DRUM 35kg
DRUM Ratio in FG = DRUM USED / FG BATCH WT
DRUM Ratio in FG = 0.07
FG Profit
FG_Cost = 150000
FG Initial Qty = 50 KG @3000 / KG
FG Sold = 32 KG
FG SOLD Amount = 144,000 @ 4500 per KG
FG_Cost, FG Initial Qty, FG Sold, FG Balance, FG SOLD Amount,
== FG SOLD Amount – (FG Sold * (FG_Cost / FG Initial Qty) )
=48000 [FG Profit on Sold Qty]
DRUM Profit
= FG Profit * DRUM Ratio in FG
= 3360
