Duplicate Rows in Crystal Report Query

[Private_Member_469768]

Good evening experts,

I am getting duplicate rows in a crystal report using the following query and I cannot work out where the joins or aggregates are going wrong!

Any help or comments are appreciated. Thanks:

SELECT

T0.[ItemCode], T0.[ItemName], ' ' as Counted, T9.[OnHand] - T9.[IsCommited] as 'Aval Stock', T0.[OnHand]-T0.[IsCommited] as 'Available Stock', NULLIF(T7.[OnOrder],0) as 'On Order',

CASE WHEN T7.[MinStock]- (T7.[OnOrder]+(T7.[OnHand]-T7.[IsCommited])) < 1 OR T7.[MinStock] = 0

THEN NULL

ELSE

CASE WHEN FLOOR((T7.[MinStock]- (T7.[OnHand]-T7.[IsCommited])) / NULLIF(T0.[OrdrMulti],0)) <> CEILING((T7.[MinStock]- (T7.[OnHand]-T7.[IsCommited])) / NULLIF(T0.[OrdrMulti],0))

Then CEILING((T7.[MinStock]- (T7.[OnHand]-T7.[IsCommited])) / T0.[OrdrMulti]) * T0.[OrdrMulti]

ELSE T7.[MinStock]- (T7.[OnOrder]+(T7.[OnHand]-T7.[IsCommited]))

END

END as 'Requested' ,

NULLIF(T7.[MinStock],0) AS 'Min Stock',

NULLIF(T3.Quantity + T0.IsCommited,0) AS 'Month1 Sales',

NULLIF(T4.Quantity,0) AS 'Month2 Sales',

NULLIF(T5.Quantity,0) AS 'Month3 Sales',

T8.[WhsName] AS 'Warehouse',

T0.[U_CountSeq] AS 'Season',

T6.CARDNAME

FROM OITM T0

INNER JOIN OITB T2 ON T0.ItmsGrpCod = T2.ItmsGrpCod

LEFT OUTER JOIN (

(SELECT T0.ItemCode, SUM(T0.Quantity) Quantity FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-30 AND GETDATE()) AND T1.Canceled='N' GROUP BY T0.ItemCode)

UNION

(SELECT T0.ItemCode, SUM(-T0.Quantity) Quantity FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-30 AND GETDATE()) AND T1.Canceled='N' GROUP BY T0.ItemCode)

) T3 ON T3.ItemCode=T0.ItemCode

LEFT OUTER JOIN (

(SELECT T0.ItemCode, SUM(T0.Quantity) Quantity FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-60 AND GETDATE()-30) AND T1.Canceled='N' GROUP BY T0.ItemCode)

UNION

(SELECT T0.ItemCode, SUM(-T0.Quantity) Quantity FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-60 AND GETDATE()-30) AND T1.Canceled='N' GROUP BY T0.ItemCode)

)T4 ON T4.ItemCode=T0.ItemCode

LEFT OUTER JOIN (

(SELECT T0.ItemCode, SUM(T0.Quantity) Quantity FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-90 AND GETDATE()-60) AND T1.Canceled='N' GROUP BY T0.ItemCode)

UNION

(SELECT T0.ItemCode, SUM(-T0.Quantity) Quantity FROM RIN1 T0 INNER JOIN ORIN T1 ON T0.DocEntry=T1.DocEntry WHERE (T0.DocDate BETWEEN GETDATE()-90 AND GETDATE()-60) AND T1.Canceled='N' GROUP BY T0.ItemCode)

)T5 ON T5.ItemCode=T0.ItemCode

INNER JOIN OCRD T6 ON T0.CardCode = T6.CardCode

INNER JOIN OITW T7 ON T0.[ItemCode] = T7.[ItemCode]

INNER JOIN OWHS T8 ON T7.[WhsCode] = T8.[WhsCode]

LEFT JOIN OITW T9 ON T0.ITEMCODE = T9.ITEMCODE AND T0.DfltWH = T9.WHSCODE

WHERE T6.CardCode= '{?@CardName}'

AND T0.[DfltWH]=T8.[WhsCode]

AND (T8.[WhsName] = '{?@WhsName1}' OR T8.[WhsName] = '{?@WhsName2}' OR T8.[WhsName] = '{?@WhsName3}' OR T8.[WhsName] = '{?@WhsName4}')

And T0.[validFor] = 'Y'

AND T0.[QryGroup31]='N'

GROUP BY

T0.ItemCode, T0.ItemName, T8.[WhsName], T0.OnHand, T0.IsCommited, T3.Quantity, T4.Quantity, T5.Quantity,

T7.[OnOrder], T7.[MinStock],T7.[OnOrder],T7.[OnHand],T7.[IsCommited], T0.[OrdrMulti], T0.[U_CountSeq],

T6.CARDNAME,T9.[OnHand],T9.[IsCommited]

ORDER BY T0.[ItemName]

[JWiseman]

Hi Simon, if you add a text based flag for each select statement, which part(s) of the query is the duplication coming from?...e.g. add 'S1' AS SELECTFLAG to the first, 'S2' to the second, etc. to the final union.