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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy