Eliminating duplicate entry

[former_member193355]

0down votefavorite

Dear Experts, I have a sample query below: SELECT sites.siteName, value.siteIP, history.date FROM sites left JOIN history ON sites.siteName = history.siteName left join value on value.sitename = sites.sitename ORDER BY siteName,date First part of the output: How can I remove the duplicates in siteName and siteIP columns but the row # is still from 1 to 10 ? Pls advice. Thank you very much Steve

[former_member193355 · Accepted solution]

Dear All,

Thx for your answer.

Here is another my query:

WITH receipt AS (

        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,

        ROW_NUMBER() OVER (partition by oign.docnum, ign1.baseref

ORDER BY oign.docnum, ign1.baseref) as row

        FROM ign1 inner join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode

        inner join OIGN on oign.DocEntry=ign1.DocEntry

        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum

     ),

     issued AS (

        SELECT ige1.ItemCode,  OIGe.DocNum, ige1.BaseRef, sum(ige1.linetotal) as total_issue

        FROM ige1 inner join OWOR on owor.DocNum= ige1.BaseRef

        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry

                group by ige1.ItemCode, ige1.BaseRef, oige.DocNum

     )

SELECT owor.ItemCode,

       owor.DocNum, issued.DocNum as Issued_No,

       SUM(issued.total_issue) AS issuedamount,

       receipt.DocNum as Receipt_No,

       SUM(receipt.total_receipt) AS receiptamount

FROM wor1 inner join owor on owor.DocEntry = wor1.docentry

left outer join issued

on issued.BaseRef = owor.DocNum

left outer join receipt

on receipt.BaseRef = owor.DocNum

where owor.DocNum=154

GROUP BY owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum

The result using the above query is in the screen capture:

The query can't give the expected result. Please help to improve the query so that it can give the expected result as same as in the print screen. Thank you

Rgds,

Steve

[Former Member · Accepted solution]

Please close your thread by marking correct answer. One question for one thread. Post a new one for your new question.

[former_member193355 · Accepted solution]

Hi Gordon,

Actually, my latest reply is the original query. What I mean is the thread subject is the same. The result data and the query are only not the same. So, it is wasting time to create 2 thread with same subject.

Rgds,

Steve

[former_member186712 · Accepted solution]

Hi Steve,

The result that you want is impossible in SQL query. In a SQL Query all columns and all records are shown.

In your query there's no "Duplicate Entry". SQL can remove the "Duplicate Entry" if all columns in a row have the same value and it's not your case.

Only if you use Crystal Report you can remove what you call "Duplicate Entry".

Regards,

Augusto

[former_member193355 · Accepted solution]

Hi,

Thanks for your reply.

What I mean is the duplicate column values in all columns.

In the above example, the duplicate values are FG10000, 154, 1, 2 and 320.

Is it impossible to do that using SQL query ?

Please help.

Thanks,

Steve

[former_member186712 · Accepted solution]

Hi Steve,

The columns Receipt_No and Receiptamount are not the same, so it's not all columns.

What you want is impossible in SQL.

Sorry for that.

[former_member193355 · Accepted solution]

Hi Augusto,

yes you are correct. I am looking for the solution how to remove this value:

FG10000, 154, 1, 2 and 320 in the above sample.

Then the expected result is as follow:

or

Both are expected but which one is possible, it is better.

Do I have to use crystal report ? but if I use crystal report, do I have to use store procedure query ? if yes, can you give the query ?

If it is possible using query generator rather than crystal report, I think it is more better to us

Thank you

Steve

[former_member197621 · Accepted solution]

Hi Steve,

There is so many possibilities are there, for example

For one production , one issue for production 3 Receipt from production

OR

3 Issue for production one receipt from production .

So i think achieving this in query is complicate, better and easy option is use "Crystal report" .

Use this same query in crystal report, there you have one option called "Suppress if duplicate" . Using this option you can achieve your result.

Right Click on the field -> Format Field-> Suppress if duplicate.

I hope this will solve your issue.

[former_member197621 · Accepted solution]

Hi Steve,

Try this below query, am getting your expected result.

WITH receipt AS (
        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,
        ROW_NUMBER() OVER (partition by oign.docnum, ign1.baseref
ORDER BY oign.docnum, ign1.baseref) as row
        FROM ign1 inner join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode
        inner join OIGN on oign.DocEntry=ign1.DocEntry
        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum
     ),
     issued AS (
        SELECT ige1.ItemCode,  OIGe.DocNum, ige1.BaseRef, sum(ige1.linetotal) as total_issue
        FROM ige1 inner join OWOR on owor.DocNum= ige1.BaseRef
        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry
                group by ige1.ItemCode, ige1.BaseRef, oige.DocNum
     )

Select (CASE rnum when 1 then ItemCode  end) [FG-Code],
(CASE rnum when 1 then DocNum  end) [DocNum],
(CASE rnum when 1 then Issued_No  end) [Issued Num],
(CASE rnum when 1 then issuedamount  end) [Issued Amt],
 Receipt_No,receiptamount from (     
SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,owor.ItemCode,
       owor.DocNum, issued.DocNum as Issued_No,
       SUM(issued.total_issue) AS issuedamount,
       receipt.DocNum as Receipt_No,
       SUM(receipt.total_receipt) AS receiptamount
FROM wor1 inner join owor on owor.DocEntry = wor1.docentry
left outer join issued
on issued.BaseRef = owor.DocNum
left outer join receipt
on receipt.BaseRef = owor.DocNum
where owor.DocNum = 154
GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum ) TBL1

[former_member193355 · Accepted solution]

Hi Prasanna,

many thanks for your help.

The result almost closed to our expectation but it missed the 2nd good issue no.

Here is the result in my side:

issued num # 2 is missing. Pls advice how to make it appears.

Thank you

[former_member193355 · Accepted solution]

Hi Prasanna,

I agree to use the crystal report but I have to create store procedure.

I dunno what store procedure to give the expected result.

Could you please create it to me ? Thanks

Steve

[former_member197621 · Accepted solution]

Hi Steve,

Below query is not giving your expected result?

[former_member186712 · Accepted solution]

Hi Steve,

As I told you before, using the query generator is impossible.

In Crystal you can use the same Query that you have and then when you are writing the detail you have to make a condition and if the condition is true the value of the fields will not be printed.

I'm not a great expert in Crystal report.

You have to close this thread and create a new one in the Crystal Reports place. There are much more experts than me in Crystal.

Don't forget to mark the answers as Helpful answer or as Correct answer.

Hope it helps,

[former_member197621 · Accepted solution]

Hi Steve,

Try this query, let me know your feedback

WITH receipt AS (
        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,
        ROW_NUMBER() OVER (partition by oign.docnum, ign1.baseref
ORDER BY oign.docnum, ign1.baseref) as row
        FROM ign1 inner join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode
        inner join OIGN on oign.DocEntry=ign1.DocEntry
        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum
     ),
     issued AS (
        SELECT ige1.ItemCode,  OIGe.DocNum, ige1.BaseRef, sum(ige1.linetotal) as total_issue
        FROM ige1 inner join OWOR on owor.DocNum= ige1.BaseRef
        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry
                group by ige1.ItemCode, ige1.BaseRef, oige.DocNum
     )

Select (CASE rnum when 1 then ItemCode  end) [FG-Code],
(CASE rnum when 1 then DocNum  end) [DocNum],
(CASE ISrnum when 1 then Issued_No  end) [Issued Num],
(CASE ISrnum when 1 then issuedamount  end) [Issued Amt],
 Receipt_No,receiptamount from (     
SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,
ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],
owor.ItemCode,
       owor.DocNum, issued.DocNum as Issued_No,
       SUM(issued.total_issue) AS issuedamount,
       receipt.DocNum as Receipt_No,
       SUM(receipt.total_receipt) AS receiptamount
FROM wor1 inner join owor on owor.DocEntry = wor1.docentry
left outer join issued
on issued.BaseRef = owor.DocNum
left outer join receipt
on receipt.BaseRef = owor.DocNum
where owor.DocNum = 154
GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum ) TBL1

[former_member193355 · Accepted solution]

Hi Prasanna,

The query is correct now but if it is possible, could you give us help to get this following result:

I prefer the above result because the receiptamount is not duplicated per each issued_no.

Please help. Thanks a lot for your kindness

Steve

[former_member197621 · Accepted solution]

Hi Steve,

Try this below query,

WITH receipt AS (
        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,
        ROW_NUMBER() OVER (partition by oign.docnum, ign1.baseref
ORDER BY oign.docnum, ign1.baseref) as row
        FROM ign1 inner join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode
        inner join OIGN on oign.DocEntry=ign1.DocEntry
        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum
     ),
     issued AS (
        SELECT ige1.ItemCode,  OIGe.DocNum, ige1.BaseRef, sum(ige1.linetotal) as total_issue
        FROM ige1 inner join OWOR on owor.DocNum= ige1.BaseRef
        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry
                group by ige1.ItemCode, ige1.BaseRef, oige.DocNum
     )

 select * from ( 
Select (CASE rnum when 1 then ItemCode  end) [FG-Code],
(CASE rnum when 1 then DocNum  end) [DocNum],
(CASE ISrnum when 1 then Issued_No  end) [Issued Num],
(CASE ISrnum when 1 then issuedamount  end) [Issued Amt],
(CASE RSrnum when 1 then Receipt_No  end) [Receipt Num],
(CASE RSrnum when 1 then receiptamount  end) [Receipt Amt]
 from (     
SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,
ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],
ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],
owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,
       max(issued.total_issue) AS issuedamount,
       receipt.DocNum as Receipt_No,
       max(receipt.total_receipt) AS receiptamount
FROM wor1 inner join owor on owor.DocEntry = wor1.docentry
left outer join issued
on issued.BaseRef = owor.DocNum
left outer join receipt
on receipt.BaseRef = owor.DocNum
where owor.DocNum = 22
GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum ) TBL1 ) TBL2
group by 
TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]
having
(Isnull(TBL2.[Issued Amt],0) + Isnull(TBL2.[Receipt Amt],0)) <> 0
order by
TBL2.[FG-Code] desc,TBL2.[Issued Num] desc,TBL2.[Receipt Num] desc

[former_member193355 · Accepted solution]

Hi Prasanna,

It is now more correct but still not 100%.

Your help is really much appreciate.

here is the result:

Actually, we want this following result:

Please give us a help again. Thanks

Steve

[former_member197621 · Accepted solution]

Hi Steve,

Try this, let me know the feedback

WITH receipt AS (
        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,
        ROW_NUMBER() OVER (partition by owor.docentry
ORDER BY owor.docentry) as row
        FROM ign1 inner join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode
        inner join OIGN on oign.DocEntry=ign1.DocEntry
        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum,owor.docentry
     ),
     issued AS (
        SELECT 
        ROW_NUMBER() OVER (partition by owor.docentry
ORDER BY owor.docentry ) as iss_row,
        ige1.ItemCode,  OIGe.DocNum, ige1.BaseRef, sum(ige1.linetotal) as total_issue
        FROM ige1 inner join OWOR on owor.DocNum= ige1.BaseRef
        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry
                group by ige1.ItemCode, ige1.BaseRef, oige.DocNum,owor.docentry
     )

 select * from ( 
Select (CASE rnum when 1 then ItemCode  end) [FG-Code],
(CASE rnum when 1 then DocNum  end) [DocNum],
(CASE ISrnum when 1 then Issued_No  end) [Issued Num],
(CASE ISrnum when 1 then issuedamount  end) [Issued Amt],
(CASE RSrnum when 1 then Receipt_No  end) [Receipt Num],
(CASE RSrnum when 1 then receiptamount  end) [Receipt Amt]
 from (     
SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,
ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],
ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],
owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,
       max(issued.total_issue) AS issuedamount,
       receipt.DocNum as Receipt_No,
       max(receipt.total_receipt) AS receiptamount
FROM wor1 inner join owor on owor.DocEntry = wor1.docentry
left outer join issued
on issued.BaseRef = owor.DocNum
left outer join receipt
on receipt.BaseRef = owor.DocNum and receipt.row = issued.iss_row
where owor.DocNum = 154
GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum ) TBL1 ) TBL2
group by 
TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]


having
(Isnull(TBL2.[Issued Amt],0) + Isnull(TBL2.[Receipt Amt],0)) <> 0
order by
TBL2.[FG-Code] desc,TBL2.[Issued Num] desc,TBL2.[Receipt Num] desc

[former_member193355 · Accepted solution]

Hi,

It is more getting correct.

Howeever the last row is missing. here is the result:

Could you please give us help again? Thank you

Rgds,
steve

[former_member197621 · Accepted solution]

Hi Steve,

Previous query will work correct for below example,

if you have more Issue for production than "Receipt from production" then you will get correct answer.

for example you have 4 Issue and 3 Receipt from production than you will get correct answer.

Try the below query ,

This query will give correct answer until More Receipt from production than Issue for production.

But am not able to write a query for both, so based on your scenario you use any one of the query.

WITH receipt AS (
        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,
        ROW_NUMBER() OVER (partition by owor.docentry
ORDER BY owor.docentry) as row,owor.docentry
        FROM ign1 inner join OWOR on owor.DocEntry= IGN1.BaseEntry and ign1.ItemCode=owor.ItemCode
        inner join OIGN on oign.DocEntry=ign1.DocEntry and ign1.BaseType = '202'
        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum,oign.docentry,owor.DocEntry
     ),
     issued AS (
        SELECT owor.DocEntry,
        ROW_NUMBER() OVER (partition by owor.docentry
ORDER BY owor.docentry ) as iss_row,
          OIGe.DocNum, ige1.BaseRef, sum(Isnull(ige1.linetotal,0)) as total_issue
        FROM ige1 inner join OWOR on owor.DocEntry= ige1.BaseEntry and IGE1.BaseType = '202'
        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry
                group by ige1.BaseRef, oige.DocNum,oige.docentry,owor.DocEntry
     )

 select 
 TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Num],Isnull(TBL2.[Issued Amt],0) [Issued Amt],TBL2.[Receipt Num],TBL2.[Receipt Amt]
  from ( 
Select (CASE rnum when 1 then ItemCode  end) [FG-Code],
(CASE rnum when 1 then Isnull(DocNum,0)  end) [DocNum],
(CASE ISrnum when 1 then Isnull(Issued_No,0)  end) [Issued Num],
(CASE ISrnum when 1 then Isnull(issuedamount,0)  end) [Issued Amt],
(CASE RSrnum when 1 then Isnull(Receipt_No,0)  end) [Receipt Num],
(CASE RSrnum when 1 then Isnull(receiptamount,0)  end) [Receipt Amt],
(forshort) [forshort]


 from (     
SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,
owor.docentry [forshort],
ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],
ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],
owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,
       max(Isnull(issued.total_issue,0)) AS issuedamount,
       receipt.DocNum as Receipt_No,
       max(receipt.total_receipt) AS receiptamount
FROM wor1 inner join owor on owor.DocEntry = wor1.docentry
left outer join receipt
on receipt.DocEntry = owor.DocEntry 
left outer join issued
on issued.DocEntry = owor.DocEntry and Isnull(issued.iss_row,0) = Isnull(receipt.row,0) 
where owor.DocNum = 154
GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum,receipt.row ,issued.iss_row  ) TBL1 ) TBL2
group by 
TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]


order by
Max(TBL2.forshort) , TBL2.DocNum desc

[former_member193355 · Accepted solution]

Hi Prasanna,

Many, many thanks for your answer.

We have uncertainty number of issue for production (IFP) or receipt from production (RFP). Sometimes, both can't be different in numbers.

If it is happened in the future, will you please give the correct query that can accomodate both situation ?

The latest query is now correct but however, if there is a return component, it is not appear in the query.

Another issue using the latest query, if I apply the latest query to one production order that have 15 issue for production documents and 10 receipt from production documents, the result is not correct.

I  think it is probably caused by the return components.

Please give us an help again. Thank you.

Rgds,
Steve

[former_member197621 · Accepted solution]

Hi Steve,

If you got answer for this thread , close this thread and open a new thread.

[former_member193355 · Accepted solution]

Hi Prasanna,

I agree with you.

I think it is better to create new thread.

Rgds,

Steve

[Former Member]

Hi Steve,

If you want to display the data in crystal report then try the "Suppress Duplicate Value" option of crystal report. Else in SQL you can try with table variable. Create one table variable (which act as table), insert your result in that table and update that table using loop logic. Hope this will help to resolve your issue.

Regards

Manoj

[former_member193355]

Hi Manoj,

Could you please help me to give the example of table varialbe and the loop logic.

I appreciate so much

Rgds,,

Steve

[kothandaraman_nagarajan]

Hi Steve,

Actual problem is due to multiple receipt from production causes the duplicate entry of issue no and amount.

Same way, if multiple issue for the same production order, the same receipt from production copied two times.

You may try with min or max function to limit query result. If I try, not able paste query here.

Thanks & Regards,

Nagarajan

[former_member193355]

I have tried it but the result is not expected.

We want the result like this following screen:

Please help to how to obtain the above expected result without using custom report but in the query generator. THank you

[Former Member]

Hi,

You may try:

SELECT S.siteName, V.siteIP, MAX(H.date) Date, S.siteName + H.date

FROM sites S

left JOIN history H ON S.siteName = H.siteName

left join value V on V.sitename = S.sitename

Group BY S.siteName, V.siteIP

UNION

SELECT '', '', H.date, S.siteName + H.date

FROM sites S

left JOIN history H ON S.siteName = H.siteName

left join value V on V.sitename = S.sitename

ORDER BY S.siteName + H.date

Even though you can see an additional column, the last column will not be exported because there is no title.

Thanks,

Gordon

[Former Member]

Hi Steve andre,

select distinct sites.sitename,value.siteip,history.date from site

use distinct to remove duplicates

Hope help full

Thanks and Regards

DEV

[former_member186712]

Hi Steve,

When we run a SQL all the content of the rows is shown and there is no way to remove the duplicates.

In your example, if the date was also duplicated it could be possible with the distinct or with a group by.

Regards,

Augusto

[kothandaraman_nagarajan]

Hi Steve,

Please advice exact table to sort as per above requirement. I think it is not possible to get same  row from 1 to 10 if you eliminate duplicate entry.

Thanks & Regards,

Nagarajan