February 14, 2015 at 9:15 am
Hi,
I need halp with a sql query .
CREATE TABLE #Temp
(
OrderID INT,
ProjectID INT,
ProductID INT,
ProductName VARCHAR(500),
ProjAmt DECIMAL(10,2)
)
INSERT INTO #Temp (OrderID,ProjectID,ProductID,ProductName,ProjAmt)
SELECT 2115,2351,571,'HORIZON SET - 571',10839580.00
UNION ALL
SELECT 2115,2351,617,'KIRPSDM - PROC MIG - 617',10839580.00
UNION ALL
SELECT 2115,2351,370,'MIGVEL FINAL DEPTH - 370',10839580.00
UNION ALL
SELECT 2115,2351,368,'MIGVEL SED DEPTH - 368',10839580.00
UNION ALL
SELECT 2115,2351,364,'PSDM - PROC MIG - 364',10839580.00
UNION ALL
SELECT 2115,2736,650,'FINAL ANISOTROPY DELTA FIELD - 650',839580.00
UNION ALL
SELECT 2115,2736,417,'HORIZON - TOP SALT - 417',839580.00
UNION ALL
SELECT 2118,2190,307,'PSDM - PROC MIG - 307',89580.00
UNION ALL
SELECT 2118,2190,407,'SED ANISOTROPY EPSILON FIELD -407 ',89580.00
SELECT * FROM #Temp ORDER BY ProjectID,ProductName
DROP TABLE #Temp
I want to update ProjAmt field based on OrderID AND ProjectID ordered by ProductName . ONLY the TOP ProjAmt should be LEFT AS is based ON OrderID AND ProjectID ordered by ProductName others should be updated TO 0.
Example REQUIRED dataset :
SELECT 2115,2351,571,'HORIZON SET - 571',10839580.00
UNION ALL
SELECT 2115,2351,617,'KIRPSDM - PROC MIG - 617',0
UNION ALL
SELECT 2115,2351,370,'MIGVEL FINAL DEPTH - 370',0
UNION ALL
SELECT 2115,2351,368,'MIGVEL SED DEPTH - 368',0
UNION ALL
SELECT 2115,2351,364,'PSDM - PROC MIG - 364',0
UNION ALL
SELECT 2115,2736,650,'FINAL ANISOTROPY DELTA FIELD - 650',839580.00
UNION ALL
SELECT 2115,2736,417,'HORIZON - TOP SALT - 417',0
UNION ALL
SELECT 2118,2190,307,'PSDM - PROC MIG - 307',89580.00
UNION ALL
SELECT 2118,2190,407,'SED ANISOTROPY EPSILON FIELD -407 ',0
February 14, 2015 at 9:27 am
UPDATE r SET ProjAmt = 0
FROM
(
SELECT OrderID, ProjectID,ProductID,ProductName,ProjAmt,ROW_NUMBER() OVER (PARTITION BY OrderID, ProjectID ORDER BY ProductName) rn
FROM #Temp
) r
WHERE rn > 1
February 14, 2015 at 10:35 am
Thanks . I worked perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply