Dear Experts.,
I would like to create a production report by using sql query and crystal report. But I am still failed to create the query.
Here is my query:
SELECT distinct pr.[ItemCode], '0' as 'RMCode', sum(isnull(a.PQ1,0)) as [PlanQtyFGood],'0' as [CompletedQty],'0' as [IssuedQty], '0' as [RMPlannedQty] FROM OWOR PR inner join ( select distinct owor.itemcode, (isnull(owor.plannedqty,0)) as 'PQ1' from owor inner join wor1 on wor1.docentry=owor.docentry where wor1.PlannedQty < 0 and owor.status='L' --group by owor.itemcode UNION select distinct owor.itemcode, isnull(owor.plannedqty,0) as 'PQ1' from owor inner join wor1 on wor1.docentry=owor.docentry where wor1.PlannedQty > 0 and owor.status='L' ) A ON A.ItemCode = PR.ItemCode and a.PQ1 = pr.PlannedQty where PR.[PostDate] >= '07/01/2017' AND PR.[PostDate] <= '08/31/2017' AND PR.[Status] ='L' and PR.ItemCode='xxxx00023' group by PR.ItemCode union SELECT distinct t0.itemcode, t1.itemcode as 'RMcode', '0' as [PlanQtyFGood], '0' as [CompletedQty], sum(ISNULL(T1.[IssuedQty],'0')) as [IssuedQty], sum(ISNULL(T1.[PlannedQty],'0')) as [RMPlannedQty] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE t0.itemcode ='xxxx00023' and T0.Status='L' group by t0.itemcode, t1.itemcode
the result is not complete. The PlanQtyFGood column only filled in the first row of the query result but the rests rows are 0.
Please help to solve this issue. Thank you.
BR,
Steve
Try This
SELECT DISTINCT pr.[ItemCode],
'0' AS 'RMCode',
SUM(ISNULL(a.PQ1, 0)) AS [PlanQtyFGood],
'0' AS [CompletedQty],
'0' AS [IssuedQty],
'0' AS [RMPlannedQty]
FROM OWOR PR
INNER JOIN (
SELECT DISTINCT owor.itemcode,
(ISNULL(owor.plannedqty, 0)) AS 'PQ1'
FROM owor
INNER JOIN wor1
ON wor1.docentry = owor.docentry
WHERE wor1.PlannedQty < 0
AND owor.status = 'L'
--group by owor.itemcode
UNION
SELECT DISTINCT owor.itemcode,
ISNULL(owor.plannedqty, 0) AS 'PQ1'
FROM owor
INNER JOIN wor1
ON wor1.docentry = owor.docentry
WHERE wor1.PlannedQty > 0
AND owor.status = 'L'
) A
ON A.ItemCode = PR.ItemCode
AND a.PQ1 = pr.PlannedQty
WHERE PR.[PostDate] >= '07/01/2017'
AND PR.[PostDate] <= '08/31/2017'
AND PR.[Status] = 'L'
AND PR.ItemCode = 'xxxx00023'
GROUP BY
PR.ItemCode
UNION
SELECT DISTINCT t0.itemcode,
t1.itemcode AS 'RMcode',
SUM((ISNULL(t0.plannedqty, 0))) AS [PlanQtyFGood],
'0' AS [CompletedQty],
SUM(ISNULL(T1.[IssuedQty], '0')) AS [IssuedQty],
SUM(ISNULL(T1.[PlannedQty], '0')) AS [RMPlannedQty]
FROM OWOR T0
INNER JOIN WOR1 T1
ON T0.[DocEntry] = T1.[DocEntry]
WHERE t0.itemcode = 'xxxx00023'
AND T0.Status = 'L'
GROUP BY
t0.itemcode,
t1.itemcode
Dear Kennedy,
Thank you for your answer.
The result is still incorrect. Here it is:
I want the result like this:
Please help to solve this issue. Tqvm
BR,
Steve
Any updates ? Will the solution use the Hana SQL store procedure functionality to solve this issue ?
BR,
Steve
Add a comment