SELECT DISTINCT but only on two columns...

Question

Post reply

SELECT DISTINCT but only on two columns...

stephen.aa

SSC Eights!

Points: 815
More actions
May 18, 2021 at 9:26 pm

#3885038
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 2 years, 11 months ago by stephen.aa.
Steve Anderson

May 27, 2021 at 9:58 am

This was removed by the editor as SPAM

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

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

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

Bredon SSC Journeyman Points: 88 More actions · Answer 2

Hello Stephen

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

Some observations:

JobOperations.jmoOperationQuantity appears twice in the group by clause.
Not obvious what join to table Jobs adds to party?
Looks like some columns in the group by may have been added just to make the group by 'work'.
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

Thom A SSC Guru Points: 99126 More actions · Answer 3

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

stephen.aa SSC Eights! Points: 815 More actions · Answer 4

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 2 years, 11 months ago by stephen.aa.

Steve Anderson

stephen.aa SSC Eights! Points: 815 More actions · Answer 5

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

This reply was modified 2 years, 11 months ago by stephen.aa.

Steve Anderson