July 20, 2021 at 9:29 pm
Hello
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.
Thanks !!
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'
SELECT
z.[name]
,y.[whse]
,y.[co_cust_num]
,x.[co_num]
,x.[co_line]
,x.[co_release]
,y.[item]
,x.[ship_date]
,x.[qty_invoiced]
,y.[qty_ordered]
,y.[price]
,x.[qty_shipped]
,x.[qty_returned]
,x.[cost]
,x.[unit_weight]
FROM [EES_App].[dbo].[co_ship] x
INNER JOIN [EES_App].[dbo].[coitem] y
on x.co_num=y.co_num
INNER JOIN [EES_App].[dbo].[custaddr] z
on y.co_cust_num=z.cust_num
where
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
July 20, 2021 at 9:33 pm
One of the tables you're joining to must have an extra matching row in it compared to what you were used to seeing. I can't tell which one from what you've posted.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
July 20, 2021 at 10:08 pm
What is the data type of the ship_date column?
You define the variables @sdate and @edate as date - but then set them to a datetime value. If the column ship_date is actually a datetime data type and that column can have times other than 00:00:00.000 then you would be missing data in your query.
This doesn't address your issue of additional rows though. I suspect multiple addresses for each item - or multiple items - or a combination.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2021 at 7:59 pm
what is pk on these 3 tables?
[EES_App].[dbo].[co_ship]
[EES_App].[dbo].[coitem]
[EES_App].[dbo].[custaddr]
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 28, 2021 at 9:48 am
I agree with Jeffrey that the most likely cause is that someone has created an additional address record for the affected customer.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply