Written by 
I am trying to make query to show us the cost per work center for the entire year by using the quote number.
The query works fine but the problem I am facing is that my variable is only showing one result.
Would someone please help me solve my problem by showing me what is wrong in my query or explaining what is wrong and give me a hint about what the right path is to take.
I tried the following query:
DECLARE @Quote INT;
SELECT @Quote = QuoteFK
FROM InvoiceLine
WHERE CreateDate > ‘2024-01-01 00:00:00.000’;
DECLARE @Quantity INT;
SELECT @Quantity = Quantity
FROM InvoiceLine
WHERE QuoteFK = @Quote;
WITH TEMP AS (SELECT
QuoteFK as ‘QuoteNumber’,
o.Name,QuoteAssembly.SetupTime AS SU,
QuoteAssembly.SetupPrice AS ‘SUPrice’,
QuoteAssembly.RunTime*@QuantityQuotePath.QuantityToZero AS RunMinutes,
QuoteAssembly.runprice@Quantity*QuotePath.QuantityToZero AS RunPrice,
(CASE WHEN QuoteAssembly.ParentQuoteAssemblyFK IS NULL
THEN 1
ELSE (SELECT TOP 1 QuantityRequired FROM QuoteAssembly qs WHERE qs.QuoteAssemblyPK=QuoteAssembly.ParentQuoteAssemblyFK)
END) QuantityToParent,
QuotePath.QuantityToZero
FROM usp_functionExplodedQuoteTree(@Quote) QuotePath
JOIN QuoteAssembly
ON QuoteAssembly.QuoteFK = @Quote
AND ISNULL(QuoteAssembly.ParentQuoteFK,0) = (CASE WHEN QuotePath.Level = 0 THEN 0 ELSE QuotePath.ParentID END)
JOIN Operation o
ON o.OperationPK=QuoteAssembly.OperationFK)
SELECT
QuoteNumber,
Name,
SUM(SU) AS ‘SU’,
SUM(RunMinutes) AS ‘RunMinutes’,
SUM(RunMinutes)/60 AS ‘RunHours’,
SUM(SUPrice) AS ‘Setup Price’,
SUM(RunPrice) AS ‘Run Price’,
@Quantity
FROM TEMP
GROUP BY
QuoteNumber,
Name
But this is only giving me one quote number at @Quote.
It is only the @Quote that is giving problems the rest of it works fine.