Solved

Help With SQL Statement and Table Variables

Posted on 2011-09-18
3
259 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 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