SELECT DISTINCT but only on two columns...

  • Hello:

    I have the following query...

    SELECT JobOperations.jmoJobID AS JobNo, JobAssemblies.jmaPartID AS PartID, JobAssemblies.jmaPartShortDescription AS Description, JobOperations.jmoOperationQuantity AS Qty, JobOperations.jmoWorkCenterID AS WorkCenter, JobOperations.jmoProcessID AS Process, SUM(JobOperations.jmoCompletedProductionHours) AS Hours, JobOperations.jmoProductionComplete AS C, CAST(JobOperations.jmoCreatedDate AS DATE) AS StartDate, CAST(JobAssemblies.jmaCompletedDate AS DATE) AS CompleteDate, PartBins.imbQuantityOnHand, PartBins.imbQuantityAllocated

    FROM JobOperations

    INNER JOIN JobAssemblies On JobOperations.jmoJobID = JobAssemblies.jmaJobID

    INNER JOIN Jobs ON JobOperations.jmoJobID = Jobs.jmpJobID

    INNER JOIN PartBins ON JobAssemblies.jmaPartID = PartBins.imbPartID

    WHERE jmoJobID LIKE '2%' AND LEN(jmoJobID) = 5 AND JobOperations.jmoProductionComplete = 0

    GROUP BY JobOperations.jmoJobID, JobAssemblies.jmaPartID, JobAssemblies.jmaPartShortDescription, JobOperations.jmoOperationQuantity, JobOperations.jmoWorkCenterID, JobOperations.jmoProcessID, JobOperations.jmoOperationQuantity, JobOperations.jmoProductionComplete, JobOperations.jmoCreatedDate, JobAssemblies.jmaCompletedDate, PartBins.imbQuantityOnHand, PartBins.imbQuantityAllocated

    ORDER BY JobOperations.jmoJobID, JobOperations.jmoWorkCenterID

    For some reason, these columns (at the end) ae doubling up on quantities when there are quantities other than zero.

    PartBins.imbQuantityOnHand, PartBins.imbQuantityAllocated

    I do not need the zero quantities when there are non-zero quantities, otherwise I am fine with the zero quantities.  The fact is, there is only one record to contend with, with the correct quantity.

    Thanks!

     

     

    • This topic was modified 4 weeks ago by  stephen.aa.

    Steve Anderson

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Hello Stephen

    Its tricky coming up with a solution when there is no schema or test data posted with a complex query...

    Some observations:

    1. JobOperations.jmoOperationQuantity appears twice in the group by clause.
    2. Not obvious what join to table Jobs adds to party?
    3. Looks like some columns in the group by may have been added just to make the group by 'work'.
    4. Haven't spotted an obvious primary key of table JobOperations anywhere.

    Suggest pulling out the group by and sum clauses.  Evaluate the sum in a separate Common Table Expression or 'CTE'.   CTEs are really powerful, see https://www.sqlservercentral.com/steps/stairway-to-advanced-t-sql-level-3-understanding-common-table-expressions-ctes

    Have had a go at this but , without schema and test data, code suggestion can not be tested.  Am sticking my neck out here.  Don't judge me too harshly...

    ;  -- any sql statement prior to WITH must be terminated with the sql statement terminator, a semicolum

    with JMO as (
    select jmoJobID, SUM(jmoCompletedProductionHours) AS Hours
    from JobOperations
    WHERE jmoJobID LIKE '2%' AND LEN(jmoJobID) = 5 AND jmoProductionComplete = 0
    group by jmoJobID
    )
    SELECT JobOperations.jmoJobID AS JobNo, JobAssemblies.jmaPartID AS PartID, JobAssemblies.jmaPartShortDescription AS Description,
    JobOperations.jmoOperationQuantity AS Qty, JobOperations.jmoWorkCenterID AS WorkCenter, JobOperations.jmoProcessID AS Process,
    JMO.Hours, JobOperations.jmoProductionComplete AS C,
    CAST(JobOperations.jmoCreatedDate AS DATE) AS StartDate, CAST(JobAssemblies.jmaCompletedDate AS DATE) AS CompleteDate,
    PartBins.imbQuantityOnHand, PartBins.imbQuantityAllocated
    FROM JMO
    inner join JobOperations on JMO.jmoJobID = JobOperations.jmoJobID
    INNER JOIN JobAssemblies On JobOperations.jmoJobID = JobAssemblies.jmaJobID
    INNER JOIN PartBins ON JobAssemblies.jmaPartID = PartBins.imbPartID
    ORDER BY JobOperations.jmoJobID, JobOperations.jmoWorkCenterID;

    All the best.

    Bredon

  • Without sample data, nor expected results, it's difficult/impossible to give a definitive answer, however, you CAST your 2 date columns to a date in your SELECT but not in the GROUP BY. If those columns are a date and time value, then you're not grouping on the same values you have in your SELECT and you will get a row for each distinct date and time on those columns, not just the date.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • SELECT JobOperations.jmoJobID AS JobNo, JobAssemblies.jmaPartID AS PartID, JobAssemblies.jmaPartShortDescription AS Description, JobOperations.jmoOperationQuantity AS Qty, JobOperations.jmoWorkCenterID AS WorkCenter, JobOperations.jmoProcessID AS Process, SUM(JobOperations.jmoCompletedProductionHours) AS Hours, JobOperations.jmoProductionComplete AS C, CAST(JobOperations.jmoCreatedDate AS DATE) AS StartDate, CAST(JobAssemblies.jmaCompletedDate AS DATE) AS CompleteDate, MAX(PartBins.imbQuantityOnHand) AS QuantityOnHand, PartBins.imbQuantityAllocated AS QuantityAllocated

    FROM JobOperations

    INNER JOIN JobAssemblies ON JobOperations.jmoJobID = JobAssemblies.jmaJobID

    INNER JOIN Jobs ON JobOperations.jmoJobID = Jobs.jmpJobID

    INNER JOIN PartBins ON JobAssemblies.jmaPartID = PartBins.imbPartID

    WHERE jmoJobID LIKE '2%' AND LEN(jmoJobID) = 5 AND JobOperations.jmoProductionComplete = 0

    GROUP BY JobOperations.jmoJobID, JobAssemblies.jmaPartID, JobAssemblies.jmaPartShortDescription, JobOperations.jmoOperationQuantity, JobOperations.jmoWorkCenterID, JobOperations.jmoProcessID, JobOperations.jmoProductionComplete, JobOperations.jmoCreatedDate, JobAssemblies.jmaCompletedDate, PartBins.imbQuantityOnHand, PartBins.imbQuantityAllocated

    ORDER BY JobOperations.jmoJobID, JobOperations.jmoWorkCenterID

     

    Attached is a pic of the sample data.  What bothers me is we are just adding a row for zero value for Quantity on Hand.  Look at the end of JobNo 20611.  There is no value of zero in the data.  The query is producing this information.2021-05-26 15_10_42-sql - Remote Desktop Connection a row with a zero value for

    • This reply was modified 3 weeks ago by  stephen.aa.

    Steve Anderson

  • I did figure this out, so much thanks to everyone for the help.  There was zer0 data after all....

    • This reply was modified 2 weeks, 6 days ago by  stephen.aa.

    Steve Anderson

  • This was removed by the editor as SPAM

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

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