March 3, 2015 at 8:05 am
Hi,
I need some help with an update query .
Create table #projects (
ID INT IDENTITY(1,1) ,
OrderID INT,
ProjectID INT,
Project VARCHAR(100),
ProductID INT,
Product Varchar(100),
Amount Decimal (18,2),
Unit Decimal (18,2)
)
INSERT INTO #projects (OrderID,ProjectID,Project,ProductID,Product,Amount,Unit)
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00
UNION ALL
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47
UNION ALL
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47
UNION ALL
SELECT 3999,3400,'Lab Sea 22',192,' PROC MIGttt ',418.35,1224.47
SELECT * FROM #projects
DROP TABLE #projects
WHEN OrderID,ProjectID AND ProductID ARE the same , I want update amount and unit fields OF the same combination TO 0 IN ALL rows EXCEPT 1 ,
example FOR OrderID 5392,ProjectID 2989 AND productID 357 , this should be displayed AS
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00
example FOR OrderID 5392,ProjectID 2989 AND productID 367 , this should be displayed AS
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00
example FOR OrderID 3999,ProjectID 4399 AND productID 247 , this should be displayed AS
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47
UNION ALL
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',0.00,0.00
so the final resultset should be LIKE the following
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',7875.00,189.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',357,'PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',7075.00,159.109
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00
UNION ALL
SELECT 5392,2989,'Freedom WAZ',367,' REVERSE PROC MIG',0.00,0.00
UNION ALL
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',8418.35,11224.47
UNION ALL
SELECT 3999,4399,'Lab Sea',247,' PROC MIG',0.00,0.00
UNION ALL
SELECT 3999,3400,'Lab Sea 22',192,' PROC MIGttt ',418.35,1224.47
Thanks,
PSB
March 3, 2015 at 8:30 am
How about this:
;WITH CTE AS
(
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY OrderID,ProjectID,ProductID ORDER BY OrderID,ProjectID,ProductID, Amount DESC)
FROM #projects
)
SELECT OrderID
,ProjectID
,Project
,ProductID
,Product
,Amount = CASE WHEN RN = 1 THEN Amount ELSE 0 END
,Unit = CASE WHEN RN = 1 THEN Unit ELSE 0 END
FROM CTE
ORDER BY OrderID,ProjectID,ProductID, RN;
March 3, 2015 at 9:49 am
If you don't mind can you send an update query instead of the select ?
Thanks,
PSB
March 4, 2015 at 5:11 am
-- Updating a single-table CTE updates the underlying table, so the change is straightforward:
-- (Note that you need to order by Amount DESC to ensure the values are in row 1 for each partition.)
;WITH CTE AS
(
SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY OrderID,ProjectID,ProductID ORDER BY OrderID,ProjectID,ProductID, Amount DESC)
FROM #projects
)
UPDATE CTE
SET Amount = CASE WHEN RN = 1 THEN Amount ELSE 0 END
,Unit = CASE WHEN RN = 1 THEN Unit ELSE 0 END;
-- View results:
SELECT * FROM #projects ORDER BY OrderID,ProjectID,ProductID;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply