scottsanpedro
asked on
Transact-SQL Group By Clause
I am writing a rather complicated query and have run into problems..
I seem to have the query correct so far but i'm unsure how to resolve
the problem of what the call the group by clause. Here's the query
I'm only concerned with the area surrounded by ***(added for viewing
purposes only)
I need to name this part in the Group By Clause.
My Question..do I need to add the whole select part into the end of the
Group BY or can I give it an alise??
CREATE Procedure ROYALGetStockByPeriod
@StartPeriod int , @EndPeriod int , @StockCode varChar(20)
As
SELECT SM_STOCK_CODE, SM_Location ,
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) As TotalQty,
***CASE SM_Location
WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) > ST_CalcPrice THEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *10 / 100
ELSE
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *5 / 100
END
END, ***
DET_NETT As SellingPrice
FROM GMCPUBS.dbo.STK_MOVEMENTS
INNER JOIN GMCPUBS.dbo.SL_PL_NL_Detai l
ON GMCPUBS.dbo.STK_MOVEMENTS. SM_Order_L ink = GMCPUBS.dbo.SL_PL_NL_Detai l.DET_Orde r_Link
INNER JOIN Royalties.dbo.tblStockMain
ON GMCPUBS.dbo.STK_MOVEMENTS. SM_Stock_C ode = Royalties.dbo.tblStockMain .ST_Code
WHERE SM_STOCK_CODE = @StockCode
AND SM_PERIODNUM >=
CASE SM_Location
WHEN 'Lew' THEN @StartPeriod
WHEN 'ORC' THEN @StartPeriod
WHEN 'SCH' THEN @StartPeriod
WHEN 'STE' THEN (@StartPeriod - 3)
WHEN 'CAP' THEN (@StartPeriod - 3)
END
AND SM_PERIODNUM <=
CASE SM_Location
WHEN 'Lew' THEN @EndPeriod
WHEN 'ORC' THEN @EndPeriod
WHEN 'SCH' THEN @EndPeriod
WHEN 'STE' THEN (@EndPeriod- 3)
WHEN 'CAP' THEN (@EndPeriod- 3)
END
AND SM_Location IN('Lew','Orc','Ste','Cap' ,'Sch')
AND SM_Year = 'L' AND SM_Type = 'S'
AND SM_Status In('I','O')
GROUP BY SM_STOCK_CODE , SM_Location , DET_NETT
RETURN
Thanks in advance
Scott
I seem to have the query correct so far but i'm unsure how to resolve
the problem of what the call the group by clause. Here's the query
I'm only concerned with the area surrounded by ***(added for viewing
purposes only)
I need to name this part in the Group By Clause.
My Question..do I need to add the whole select part into the end of the
Group BY or can I give it an alise??
CREATE Procedure ROYALGetStockByPeriod
@StartPeriod int , @EndPeriod int , @StockCode varChar(20)
As
SELECT SM_STOCK_CODE, SM_Location ,
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) As TotalQty,
***CASE SM_Location
WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) > ST_CalcPrice THEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *10 / 100
ELSE
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *5 / 100
END
END, ***
DET_NETT As SellingPrice
FROM GMCPUBS.dbo.STK_MOVEMENTS
INNER JOIN GMCPUBS.dbo.SL_PL_NL_Detai
ON GMCPUBS.dbo.STK_MOVEMENTS.
INNER JOIN Royalties.dbo.tblStockMain
ON GMCPUBS.dbo.STK_MOVEMENTS.
WHERE SM_STOCK_CODE = @StockCode
AND SM_PERIODNUM >=
CASE SM_Location
WHEN 'Lew' THEN @StartPeriod
WHEN 'ORC' THEN @StartPeriod
WHEN 'SCH' THEN @StartPeriod
WHEN 'STE' THEN (@StartPeriod - 3)
WHEN 'CAP' THEN (@StartPeriod - 3)
END
AND SM_PERIODNUM <=
CASE SM_Location
WHEN 'Lew' THEN @EndPeriod
WHEN 'ORC' THEN @EndPeriod
WHEN 'SCH' THEN @EndPeriod
WHEN 'STE' THEN (@EndPeriod- 3)
WHEN 'CAP' THEN (@EndPeriod- 3)
END
AND SM_Location IN('Lew','Orc','Ste','Cap'
AND SM_Year = 'L' AND SM_Type = 'S'
AND SM_Status In('I','O')
GROUP BY SM_STOCK_CODE , SM_Location , DET_NETT
RETURN
Thanks in advance
Scott
All you really have to do is make sure that all fields within the select ( except those within aggregate funtions ) need to be in the group by statement.
It looks ugly, but you should be able to group by it by placing that entire phrase in the GROUP BY; for example:
GROUP BY SM_STOCK_CODE , SM_Location ,
***CASE SM_Location
WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) > ST_CalcPrice THEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *10 / 100
ELSE
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *5 / 100
END
END, ***
DET_NETT
GROUP BY SM_STOCK_CODE , SM_Location ,
***CASE SM_Location
WHEN 'ORC' THEN CASE WHEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) > ST_CalcPrice THEN SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *10 / 100
ELSE
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END) / SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1)
ELSE DET_NETT
END) *5 / 100
END
END, ***
DET_NETT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think your correct. I spent the day working on this problem and came out with the same answer
CASE WHEN (SELECT ST_CalcPrice FROM Royalties.dbo.tblStockMain WHERE ST_Code = @StockCode)
<
SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1 )
ELSE DET_NETT
END) /
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END)
THEN
10
ELSE
5
END As RoyPercent,
Thanks you all for your time on this
Scott
CASE WHEN (SELECT ST_CalcPrice FROM Royalties.dbo.tblStockMain
<
SUM(CASE SM_Status
WHEN 'I' THEN (DET_NETT * -1 )
ELSE DET_NETT
END) /
SUM(CASE SM_Status
WHEN 'I' THEN (SM_Quantity * -1)
ELSE SM_Quantity
END)
THEN
10
ELSE
5
END As RoyPercent,
Thanks you all for your time on this
Scott