JamesHardiman
MIS
Morning, everyone.
I'm confused about how to mix setting variables and using them in the middle of my select statement, and don't even know what questions to look up in the online books.
In the following code (which works fine), I keep getting the SQL engine to work out the same stuff over and again, because I don't know how to assign it to variables, and then use the variables. Here's the code (the red bit is the same as the blue bit, and the blue and green bits together are the same as the brown bit):
[tt]
SELECT
ClosingStock.Area,
ClosingStock.Category,
isnull(SalesForPeriod.TotalSales,0) as TotalSales,
isnull(OpeningStockValue,0) as OpeningStockValue,
isnull(DeliveryValue,0) as DeliveryValue,
isnull(ClosingStockValue,0) as ClosingStockValue,
isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0) AS COGS,
isnull(SalesForPeriod.TotalSales,0)
- (isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0)) AS GP,
CASE
WHEN isnull(SalesForPeriod.TotalSales,0) > 0 THEN
(isnull(SalesForPeriod.TotalSales,0)
- isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0))
/isnull(SalesForPeriod.TotalSales,0)
ELSE 0
END AS GPpct
FROM /* There then follows a load of joined tables */
[/tt]
(COGS: Cost of Goods Sold, GP = Gross Profit)
I figure I probably need to declare and set some variables, such as @TotalSales, @COGS, @GP, but just can't work out the syntax, and the sequence of doing it within my SP.
Any/all help gratefully received!
Cheers,
James Hardiman
JamesH@Sunsail.com
I'm confused about how to mix setting variables and using them in the middle of my select statement, and don't even know what questions to look up in the online books.
In the following code (which works fine), I keep getting the SQL engine to work out the same stuff over and again, because I don't know how to assign it to variables, and then use the variables. Here's the code (the red bit is the same as the blue bit, and the blue and green bits together are the same as the brown bit):
[tt]
SELECT
ClosingStock.Area,
ClosingStock.Category,
isnull(SalesForPeriod.TotalSales,0) as TotalSales,
isnull(OpeningStockValue,0) as OpeningStockValue,
isnull(DeliveryValue,0) as DeliveryValue,
isnull(ClosingStockValue,0) as ClosingStockValue,
isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0) AS COGS,
isnull(SalesForPeriod.TotalSales,0)
- (isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0)) AS GP,
CASE
WHEN isnull(SalesForPeriod.TotalSales,0) > 0 THEN
(isnull(SalesForPeriod.TotalSales,0)
- isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0))
/isnull(SalesForPeriod.TotalSales,0)
ELSE 0
END AS GPpct
FROM /* There then follows a load of joined tables */
[/tt]
(COGS: Cost of Goods Sold, GP = Gross Profit)
I figure I probably need to declare and set some variables, such as @TotalSales, @COGS, @GP, but just can't work out the syntax, and the sequence of doing it within my SP.
Any/all help gratefully received!
Cheers,
James Hardiman
JamesH@Sunsail.com