CODEWITHSUNDEEP
sqlt-sql
Leighbee
Leighbee

Reputation: 37

mssql select statement single row of results

Im trying to write a script to return 4 different calculations on the same field, but I really want the results the return in a single row.

At the moment it returns a new row for each result when there is a result.

 SELECT DISTINCT
    sp.PartNumber,
   (StandardUnitMaterialCost+StandardUnitRunCost+StandardUnitSetCost+StandardUnitSubcontractCost+StandardLandedCost1+StandardLandedCost2+StandardLandedCost3+StandardLandedCost4+StandardLandedCost5) 
    ,
    (select  ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) < MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND slp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND slp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND slp.TransactionCodeID IN (1,2,13) AND slp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND slp.TransactionCodeID IN (3,7) AND slp.PartID = Stores.StockLogPart.PartID)


FROM Stores.StockLogPart slp
JOIN Structure.Parts sp ON sp.PartID = slp.PartID
JOIN Stores.ProductGroups pg ON pg.ProductGroupID = sp.ProductGroupID
WHERE pg.ProductGroupCode = 'XX'

Upvotes: 0

Views: 49

Answers (1)

user7415038
user7415038

Reputation:

I think you need to remove one of your joins. It's a bit hard because it's unclear in which tables the fields for the costs are. Try this:

SELECT DISTINCT
    sp.PartNumber,
   (StandardUnitMaterialCost+StandardUnitRunCost+StandardUnitSetCost+StandardUnitSubcontractCost+StandardLandedCost1+StandardLandedCost2+StandardLandedCost3+StandardLandedCost4+StandardLandedCost5) 
    ,
    (select  ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) < MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND sp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND sp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND stores.StockLogPart.TransactionCodeID IN (1,2,13) AND sp.PartID = Stores.StockLogPart.PartID)
    ,
    (select ISNULL(sum(quantity),0) from stores.StockLogPart where month(TransactionDate) = MONTH(getdate()) AND year(TransactionDate) <= year(getdate()) AND stores.StockLogPart.TransactionCodeID IN (3,7) AND sp.PartID = Stores.StockLogPart.PartID)

FROM Structure.Parts sp
JOIN Stores.ProductGroups pg ON pg.ProductGroupID = sp.ProductGroupID
WHERE pg.ProductGroupCode = 'XX'

Upvotes: 1

Related Questions