Skip to Content
0

Need a SQL query report

Aug 21, 2017 at 10:57 PM

53

avatar image
Former Member

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

query-result.jpg (54.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Kennedy T Aug 22, 2017 at 04:08 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 22, 2017 at 06:46 AM
0

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


fileresult.jpg (59.5 kB)
required.jpg (58.6 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Aug 22, 2017 at 08:37 AM
0

Any updates ? Will the solution use the Hana SQL store procedure functionality to solve this issue ?

BR,

Steve

Share
10 |10000 characters needed characters left characters exceeded