Sudden Duplicate rows

  • 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
  • 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."

  • 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

  • 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/

  • 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