?
Solved

Help With SQL Statement and Table Variables

Posted on 2011-09-18
3
Medium Priority
?
267 Views
Last Modified: 2012-05-12
I have a table in my database that holds products for an online store.  For each record there are the following fields:

- Weight
- Physical Weight 1
- Physical Weight 2
- Physical Weight 3
- Dimensional Weight 1
- Dimensional Weight 2
- Dimensional Weight 3

I need to compare the physical weights to the dimensional weights and select the largest.  I then have to sum the three values to compute the weight.  I have attached the code that I am using to do this but the table variables that I am using are retaining the last records information and populating the weight with that information for all of the records.  

I have little to no experience writing SQL statements so I would appreciate any help.
DECLARE @sw1 int,
		@sw2 int,
		@sw3 int;	
		
UPDATE dbo.New_Products_To_Add
SET @sw1 =
	CASE
		WHEN dc_custom_physical_weight_1 > dc_custom_dimensional_weight_1
			THEN dc_custom_physical_weight_1
		ELSE
			dc_custom_dimensional_weight_1
	END; 

UPDATE dbo.New_Products_To_Add
SET @sw2 = 
	CASE
		WHEN dc_custom_physical_weight_2 > dc_custom_dimensional_weight_2
			THEN dc_custom_physical_weight_2
		ELSE
			dc_custom_dimensional_weight_2
	END;

UPDATE dbo.New_Products_To_Add
SET @sw3 = 
	CASE
		WHEN dc_custom_physical_weight_3 > dc_custom_dimensional_weight_3
			THEN dc_custom_physical_weight_3
		ELSE
			dc_custom_dimensional_weight_3
	END;	
	
UPDATE dbo.New_Products_To_Add
SET weight = @sw1 + @sw2 + @sw3

Open in new window

0
Comment
Question by:csimmons1324
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 2000 total points
ID: 36557768
Update NPTA
set
Weight =
(
CASE
            WHEN dc_custom_physical_weight_1 > dc_custom_dimensional_weight_1
                  THEN dc_custom_physical_weight_1
            ELSE
                  dc_custom_dimensional_weight_1
End

) +
(

CASE
            WHEN dc_custom_physical_weight_2 > dc_custom_dimensional_weight_2
                  THEN dc_custom_physical_weight_2
            ELSE
                  dc_custom_dimensional_weight_2
      END


)+
(

CASE
            WHEN dc_custom_physical_weight_3 > dc_custom_dimensional_weight_3
                  THEN dc_custom_physical_weight_3
            ELSE
                  dc_custom_dimensional_weight_3
      END
)
From
dbo.New_Product_To_Add NPTA
0
 

Author Comment

by:csimmons1324
ID: 36557778
That seemed to work.  Thank you!  What is the NPTA that you added?
0
 
LVL 2

Expert Comment

by:akku101
ID: 36557782
Given table alias name for New_Product_To_Add  as NPTA.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question