My query has been running for ages without a hitch but yesterday , Some totals on one of the customers exactly doubled in value , I noticed that the rows had doubled in the query instead of 22 rows there were 44. but when I stripped out the query , I got 22 rows which is correct , so something is off with my query. but it only happened for the one customer that I can tell.
I use 2 joins to get all the customer , order and shipment data together,
To try troubleshoot this , I thought of using distinct , but that doesn't give the correct results either , beside I wasn't able to get distinct operator on one column only. anyhow I don't think this is the correct approach.
Not sure of the next steps to fix this , Id appreciate any thoughts and direction on my query joins below to somehow avoid this.
declare @item NVARCHAR(10)
declare @cus NVARCHAR(10)
declare @sdate date
declare @edate date
--set @item = '3'
--set @cus = 'EPE'
set @sdate = '2021-07-01 00:00:00.000'
set @edate = '2021-07-31 00:00:00.000'
FROM [EES_App].[dbo].[co_ship] x
INNER JOIN [EES_App].[dbo].[coitem] y
INNER JOIN [EES_App].[dbo].[custaddr] z
x.ship_date between @sdate and @edate
and x.co_line = 1
and x.co_release = y.co_release
--and x.co_num = 6440
and x.co_num =19131