Help with some (probably) basic TSQL, please

[JamesHardiman]

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

 

[jhall156]

You can't combine variable assignment and data retrieval operations in the same query so the only way I know of to assign these values to variables is by running the query first to get the variables and then again using the variables. That would be very inefficient. What you have written looks good to me.

JHall

 

[JayKusch]

I believe you will need to declare each variable you need to create, ie DECLARE @COGS Money, then load each variable with a SET command, ie

SET @COGS = (SELECT isnull(OpeningStockValue,0)
+ isnull(DeliveryValue,0)
- isnull(ClosingStockValue,0) AS COGS
FROM -- load of joined tables)



Thanks

J. Kusch

 

[JamesHardiman]

Hmm! JHall's reply looks like the one that leaves me feeling best, and doing the least work!

So thanks for that!

I started to try JayKusch's approach, but it felt like I was headed in the general direction indicated by JHall, and would need separate SPs or views.

If anyone knows anything we don't know, though ... <wink>

James