Good day, i am working with this query where i want to remove duplicate data from two different rows, so for example i have this record query result from here:
SELECT DISTINCT
T9.SlpName, T1.CardName [Customer Name],T1.DocNum [SO No.],
T1.DocDate [SO Date],T1.DocTotal [SO Total], T3.DocNum [Delivery Doc Num],
T5.DocNum [TRA No], T5.DocDate [TRA Date],T5.DocTotal [TRA Total],
T5.GrosProfit [Gross Profit]
FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry
left outer join DLN1 T2 on T2.BaseEntry = T0.DocEntry
left outer join ODLN T3 on T2.DocEntry = T3.DocEntry
left Outer join INV1 T4 on T4.BaseEntry = T3.DocEntry and T4.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T4.Basetype=17 and T4.BaseEntry=T0.DocEntry and T4.BaseLine=T0.LineNum)
left outer join OINV T5 on T5.DocEntry = T4.DocEntry
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode
WHERE T1.DocDate BETWEEN '10.01.16' AND '10.27.16' AND T1.CardCode='C-ACQUA TECH'
Group by T9.SlpName, T1.[CardName], T1.[DocNum], T1.[DocDate], T1.DocTotal,
T3.DocNum, T5.DocNum, T5.DocDate, T5.DocTotal,T5.GrosProfit
ORDER BY T9.SlpName,T1.CardName
Customer | SO-Date | SO-Number | SO-Amount | INV-Date | INV-Amount
B1 10-07-16 000001 80,000.50 11-26 54,000.00
B1 10-07-16 000001 80,000.50 11-29 24,000.00
The SO was released on the same date, but have different invoice date so when i do a crystal report.. the SO amount was SUMMED up even though its just duplicated. What i want(cause i cant find a way in crystal report summing duplicate values) is that:
Customer | SO-Date | SO-Number | SO-Amount | INV-Date | INV-Amount
B1 10-07-16 000001 80,000.50 11-26-16 54,000.00
null null null null 11-29-16 24,000.00
If a row has multiple join-partners on the other side of your join, you will always duplicate that row. You will need two separate queries to aggregate SO-Amount and INV-Amount.
--- EDIT ---
Consider this simple example: We have three tables. One that saves company departments, one that stores the annual revenue for the departments and one that stores the monthly costs of these departments.
Table 1
DepartmentId | DepartmentName | NumberEmployees
5234 | "Software Development" | 20
3465 | "Sales" | 120
Table 2
DepartmentId | Year | Revenue
5234 | 2015 | 2,000,000
5234 | 2014 | 1,500,000
Table 3
DepartmentId | Year | Month | Cost
5234 | 2015 | Jan | 120,000
5234 | 2015 | Feb | 150,000
5234 | 2014 | Jan | 80,000
Out task is now to sum up the overall revenue of department 5234 as well as the overall costs.
If we join table 1 and table 2 we get:
DepartmentId | DepartmentName | NumberEmployees| Year | Revenue
5234 | "Software Development" | 20 | 2015 | 2,000,000
5234 | "Software Development" | 20 | 2014 | 1,500,000
With this table we could calculate the overall revenue.
If we join table 1 and 3 we get:
DepartmentId | DepartmentName | NumberEmployees | Year | Month | Cost
5234 | "Software Development" | 20 | 2015 | Jan | 120,000
5234 | "Software Development" | 20 | 2015 | Feb | 150,000
5234 | "Software Development" | 20 | 2014 | Jan | 80,000
With this table you can calculate the overall costs.
What you don't want to do though is joining all 3 tables, because then you get:
DepartmentId | DepartmentName | NumberEmployees| Year | Revenue | Month | Cost
5234 | "Software Development" | 20 | 2015 | 2,000,000 | Jan | 120,000
5234 | "Software Development" | 20 | 2015 | 2,000,000 | Feb | 150,000
5234 | "Software Development" | 20 | 2014 | 1,500,000 | Jan | 80,000
As you can see the 2015 revenue is duplicated because there are too costs entries for 2015 (Jan and Feb). If you use this table to compute both the overall revenue and the cost, you will end up with the wrong value.
So to wrap up and relate to your problem: You should use two separate queries to calculate your aggregations.