Post reply

duplicates

  • August 24, 2015 at 4:32 pm

    #320762

    1.There are duplicates of part.num and I want to remove duplicates and add quantities of those duplicates into one single quantity.

    Code is below

    SELECT

    part.num , (woitem.qtytarget/wo.qtytarget) AS woitemqty,

    (SELECT LIST(wo.num, ',') FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id WHERE moitem.moid = mo.id) AS wonums

    FROM mo

    INNER JOIN moitem ON mo.id = moitem.moid

    LEFT JOIN wo ON moitem.id = wo.moitemid

    LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10

    LEFT JOIN (Select sum(woitem.qtytarget) as labor, woitem.woid, uom.code as uom

    from woitem

    JOIN part on woitem.partid = part.id and part.typeid = 21

    JOIN uom on woitem.uomid = uom.id

    group by 2,3) as labor on wo.id = labor.woid

    LEFT JOIN part ON woitem.partid = part.id

    Where part.num = 'xo254'

    // So, There are many xo254, but they have different quantities and different MO numbers. I want to add quantities of all similar x0254 part numbers.

  • August 24, 2015 at 9:35 pm

    #1822127

    Welcome to SSC...

    For a first timer, pretty good post. Two things that would help us to help you are:

    1. CREATE TABLE scripts

    2. INSERT scripts to populate the table(s)

    so that we can see what you're working on. We don't need a million records, just enough to reproduce your problem. Here's a really good article that's pretty much required reading for anyone posting here: http://www.sqlservercentral.com/articles/Best+Practices/61537/

    If all you're trying to do is determine how many of each itemType you need, it's simple. If the number is coming from the WorkOrders table, then it's

    SELECT Item.ItemType, SUM(WorkOrders.Quantity) AS TotalQtyRequired

    FROM Item INNER JOIN WorkOrders wo ON Item.ItemID = wo.ItemID

    GROUP BY Item.ItemType

    ORDER BY Item.ItemType;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply