Skip to Content
avatar image
Former Member

Need a SQL query report

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Aug 22, 2017 at 04:08 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 22, 2017 at 06:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 22, 2017 at 08:37 AM

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

    BR,

    Steve

    Add comment
    10|10000 characters needed characters exceeded