Solved

update from select

Posted on 2013-05-30
8
214 Views
Last Modified: 2013-05-31
Can't seem to get this to work. Any ideas?

UPDATE    WW.ClientOrders
SET              TotalPrice = T.TP, ToatalGST = T.TG, TotalPST = T.TP, TotalIncludingTax = T.TotalIncludingTaxes
SELECT     SUM(TotalPrice) AS TP, SUM(TotalGST) AS TG, SUM(TotalPST) AS TP, SUM(TotalPrice+TotalGST+TotalPST) AS TotalIncludingTaxes
FROM         (SELECT   ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity,2) AS TotalPrice
,ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity*GST,2) AS TotalGST
,ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity*PST,2) AS TotalPST
FROM  (SELECT po.PaymentDiscount, cod.OrderDetailsID, cod.OrderID, cod.DeliveryDateRequest, cod.GrowthStageID, cod.StandingOrder, cod.Comments, 
	  cod.Quantity, cod.ClientSpecialDiscount, cod.ResellerSpecialDiscount, cod.ProductID, 
	  CASE cod.GrowthStageID WHEN 3 THEN p.ProductPrice - 2 WHEN 2 THEN p.ProductPrice - 1 ELSE p.ProductPrice END AS ProductPrice, 
	  CASE WHEN cod.Quantity < 5 THEN 0 WHEN cod.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount, p.TaxCategoryID, tc.GST, 
	  tc.PST
FROM  WW.ClientOrderDetails AS cod INNER JOIN
	  WW.Products AS p ON cod.ProductID = p.ProductID INNER JOIN
	  WW.TaxCategories AS tc ON p.TaxCategoryID = tc.TaxCategoryID INNER JOIN
	  WW.ClientOrders AS co ON cod.OrderID = co.OrderID INNER JOIN
	  WW.PaymentOptions AS po ON co.PaymentOptionID = po.PaymentOptionID
WHERE(cod.OrderID = @OrderID)) AS p) AS T

Open in new window

0
Comment
Question by:Shawn
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39210114
re-looking - sorry
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 39210117
How about this:

WITH T (TP, TG, TPST, TotalIncludingTaxes) AS
(
SELECT SUM(TotalPrice) AS TP, SUM(TotalGST) AS TG, SUM(TotalPST) AS TPST, SUM(TotalPrice+TotalGST+TotalPST) AS TotalIncludingTaxes
FROM (SELECT   ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity,2) AS TotalPrice
,ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity*GST,2) AS TotalGST
,ROUND(((p.ProductPrice-ProductPrice*VolumeDiscount)-((p.ProductPrice-p.ProductPrice*VolumeDiscount)*PaymentDiscount))*Quantity*PST,2) AS TotalPST
FROM (SELECT po.PaymentDiscount, cod.OrderDetailsID, cod.OrderID, cod.DeliveryDateRequest, cod.GrowthStageID, cod.StandingOrder, cod.Comments, 
	  cod.Quantity, cod.ClientSpecialDiscount, cod.ResellerSpecialDiscount, cod.ProductID, 
	  CASE cod.GrowthStageID WHEN 3 THEN p.ProductPrice - 2 WHEN 2 THEN p.ProductPrice - 1 ELSE p.ProductPrice END AS ProductPrice, 
	  CASE WHEN cod.Quantity < 5 THEN 0 WHEN cod.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount, p.TaxCategoryID, tc.GST, 
	  tc.PST
FROM  WW.ClientOrderDetails AS cod INNER JOIN
	  WW.Products AS p ON cod.ProductID = p.ProductID INNER JOIN
	  WW.TaxCategories AS tc ON p.TaxCategoryID = tc.TaxCategoryID INNER JOIN
	  WW.ClientOrders AS co ON cod.OrderID = co.OrderID INNER JOIN
	  WW.PaymentOptions AS po ON co.PaymentOptionID = po.PaymentOptionID
WHERE(cod.OrderID = @OrderID)) AS p
)
UPDATE    WW.ClientOrders
SET              TotalPrice = T.TP, ToatalGST = T.TG, TotalPST = T.TPST, TotalIncludingTax = T.TotalIncludingTaxes
FROM T;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39210119
This is your existing query - reformatted

there is a FROM ( at line 6 missing

but also at lines 7-10 refer to SUM()s but there is no corresponding GROUP BY
(and I don't know hat it should be)

additionally there are more SUM()s at lines 12-14, but again there is not matching GROUP BY (and I don't know hat it should be)

do you know what those group bys should be?
UPDATE WW.ClientOrders
SET TotalPrice = T.TP
	, ToatalGST = T.TG
	, TotalPST = T.TP
	, TotalIncludingTax = T.TotalIncludingTaxes

SELECT SUM(TotalPrice) AS TP
	, SUM(TotalGST) AS TG
	, SUM(TotalPST) AS TP
	, SUM(TotalPrice + TotalGST + TotalPST) AS TotalIncludingTaxes
FROM (
	SELECT ROUND(((p.ProductPrice - ProductPrice * VolumeDiscount) - ((p.ProductPrice - p.ProductPrice * VolumeDiscount) * PaymentDiscount)) * Quantity, 2) AS TotalPrice
		, ROUND(((p.ProductPrice - ProductPrice * VolumeDiscount) - ((p.ProductPrice - p.ProductPrice * VolumeDiscount) * PaymentDiscount)) * Quantity * GST, 2) AS TotalGST
		, ROUND(((p.ProductPrice - ProductPrice * VolumeDiscount) - ((p.ProductPrice - p.ProductPrice * VolumeDiscount) * PaymentDiscount)) * Quantity * PST, 2) AS TotalPST
	FROM (
		SELECT po.PaymentDiscount
			, cod.OrderDetailsID
			, cod.OrderID
			, cod.DeliveryDateRequest
			, cod.GrowthStageID
			, cod.StandingOrder
			, cod.Comments
			, cod.Quantity
			, cod.ClientSpecialDiscount
			, cod.ResellerSpecialDiscount
			, cod.ProductID
			, CASE cod.GrowthStageID WHEN 3 THEN p.ProductPrice - 2 WHEN 2 THEN p.ProductPrice - 1 ELSE p.ProductPrice END AS ProductPrice
			, CASE WHEN cod.Quantity < 5 THEN 0 WHEN cod.Quantity < 10 THEN 0.05 ELSE 0.1 END AS VolumeDiscount
			, p.TaxCategoryID
			, tc.GST
			, tc.PST
		FROM WW.ClientOrderDetails AS cod
		INNER JOIN WW.Products AS p ON cod.ProductID = p.ProductID
		INNER JOIN WW.TaxCategories AS tc ON p.TaxCategoryID = tc.TaxCategoryID
		INNER JOIN WW.ClientOrders AS co ON cod.OrderID = co.OrderID
		INNER JOIN WW.PaymentOptions AS po ON co.PaymentOptionID = po.PaymentOptionID
		WHERE (cod.OrderID = @OrderID)
		) AS p
	) AS T

Open in new window

{edit for typos - sorry}
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:Shawn
ID: 39210780
PortletPaul:

I'm getting the same error as before:
The multi-part identifier "T.TP" could not be bound.

looking into group by
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39211707
Did you try the post# 39210117 by pkwan?
0
 
LVL 1

Author Comment

by:Shawn
ID: 39211808
yes, I got an error
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'UPDATE'.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39211982
try this.
;WITH CTE 
     AS (SELECT SUM(TotalPrice)                       AS TP, 
                SUM(TotalGST)                         AS TG, 
                SUM(TotalPST)                         AS TP, 
                SUM(TotalPrice + TotalGST + TotalPST) AS TotalIncludingTaxes 
           FROM (SELECT ROUND(( ( p.ProductPrice - ProductPrice * VolumeDiscount ) - ( ( p.ProductPrice - p.ProductPrice * VolumeDiscount ) * PaymentDiscount ) ) * Quantity, 2)       AS TotalPrice,
                        ROUND(( ( p.ProductPrice - ProductPrice * VolumeDiscount ) - ( ( p.ProductPrice - p.ProductPrice * VolumeDiscount ) * PaymentDiscount ) ) * Quantity * GST, 2) AS TotalGST,
                        ROUND(( ( p.ProductPrice - ProductPrice * VolumeDiscount ) - ( ( p.ProductPrice - p.ProductPrice * VolumeDiscount ) * PaymentDiscount ) ) * Quantity * PST, 2) AS TotalPST
                   FROM (SELECT po.PaymentDiscount, 
                                cod.OrderDetailsID, 
                                cod.OrderID, 
                                cod.DeliveryDateRequest, 
                                cod.GrowthStageID, 
                                cod.StandingOrder, 
                                cod.Comments, 
                                cod.Quantity, 
                                cod.ClientSpecialDiscount, 
                                cod.ResellerSpecialDiscount, 
                                cod.ProductID, 
                                CASE cod.GrowthStageID 
                                  WHEN 3 THEN p.ProductPrice - 2 
                                  WHEN 2 THEN p.ProductPrice - 1 
                                  ELSE p.ProductPrice 
                                END AS ProductPrice, 
                                CASE 
                                  WHEN cod.Quantity < 5 THEN 0 
                                  WHEN cod.Quantity < 10 THEN 0.05 
                                  ELSE 0.1 
                                END AS VolumeDiscount, 
                                p.TaxCategoryID, 
                                tc.GST, 
                                tc.PST 
                           FROM WW.ClientOrderDetails AS cod 
                                INNER JOIN WW.Products AS p 
                                        ON cod.ProductID = p.ProductID 
                                INNER JOIN WW.TaxCategories AS tc 
                                        ON p.TaxCategoryID = tc.TaxCategoryID 
                                INNER JOIN WW.ClientOrders AS co 
                                        ON cod.OrderID = co.OrderID 
                                INNER JOIN WW.PaymentOptions AS po 
                                        ON co.PaymentOptionID = po.PaymentOptionID 
                          WHERE( cod.OrderID = @OrderID )) AS p) AS T) 
UPDATE T1 
   SET TotalPrice = T.TP, 
       ToatalGST = T.TG, 
       TotalPST = T.TP, 
       TotalIncludingTax = T.TotalIncludingTaxes 
  FROM WW.ClientOrders T1 
       CROSS JOIN CTE T 
 WHERE T1.OrderID = @OrderID 

Open in new window

0
 
LVL 1

Author Comment

by:Shawn
ID: 39212031
nice! thank you. that did it
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Load Fact table in SQL Server SSIS package 14 47
SQL Percentage Formula 7 33
SQL: Transformation or Pivot 3 36
Query Result column name from data 16 11
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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