CODEWITHSUNDEEP
sqlsql-serversql-server-2008
dynamicuser
dynamicuser

Reputation: 1552

SQL returning duplicates

I have the following tables -

TimeLogs

ID
UserID
JobID
TimeTaken
JobPhase

MaterialsForJob

ID
StockID
JobID
UserID
Quantity

Stock

ID
Name
CostPrice

The following query is returning duplicate rows, because there is more than one stock item allocated to the job and more than one phase (tl.Phase). My question is, how can I return just 1 row with sum's? By adding SUM() as it stands, the values returned would be wrong.

SELECT 
    TimeTaken as 'HoursSold', s.CostPrice * mfj.Quantity as 'StockCost'
FROM 
    TimeLogs tl
LEFT JOIN 
    MaterialsForJob mfj ON mfj.JobID = tl.JobID AND mfj.UserID = tl.UserID
INNER JOIN 
    Stock s ON s.ID = mfj.StockID
WHERE 
    tl.UserID = 10000 AND
    tl.DateEntry BETWEEN DATEADD(wk, DATEDIFF(wk, 0, '11/07/2013'), 0) 
                     AND DATEADD(wk, DATEDIFF(wk, 0, '11/07/2013'), 6)

At the moment, the above query returns -

Screenshot of invalid data

However, it should show - 

19570633502 - 30.00

Hope this makes sense. Thanks

Upvotes: 0

Views: 86

Answers (1)

Gabriel L.
Gabriel L.

Reputation: 5014

Try this query with SUM and DISTINCT. This should get you the right result :

SELECT t1.JobID AS JID, SUM(DISTINCT(TimeTaken)) as 'HoursSold', SUM(DISTINCT(s.CostPrice * mfj.Quantity)) as 'TotalStockCost'
FROM TimeLogs tl
LEFT JOIN MaterialsForJob mfj on mfj.JobID = tl.JobID and mfj.UserID = tl.UserID
INNER JOIN Stock s on s.ID = mfj.StockID
WHERE 
    tl.UserID = 10000 and 
    tl.DateEntry between dateadd(wk, datediff(wk,0,'11/07/2013'), 0) and
    dateadd(wk, datediff(wk,0,'11/07/2013'), 6)
GROUP BY JID

Upvotes: 3

Related Questions