Solved

Help With SQL Statement and Table Variables

Posted on 2011-09-18
3
260 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
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
akku101 earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

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.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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