March 31, 2015 at 9:25 am
Hi,
I need to update ProductAmt field when ProductAmts are same and different using two use cases given below :
--If Amounts are different
--When ProjectID,ProductID and OrderID are the same, then sum ProductAmt and store value in one of the rows if the Amounts are different and make the other value 0
CREATE TABLE #Orders (ID INT IDENTITY(1,1),OrderID INT,ProjectId INT,ProductID INT,ProjectAmt DECIMAL(10,2),ProductAmt DECIMAL(10,2))
INSERT INTO #Orders (OrderID,ProjectID,ProductID,ProjectAmt,ProductAmt)
SELECT 855,1393,6,0.00,125.00
UNION
SELECT 855,1393,6,0.00,62.82
UNION
SELECT 855,1197,6,0.00,63.45
UNION
SELECT 855,1197,6,0.00,125.00
UNION
SELECT 5392,2989,652,0.00,7875.00
UNION
SELECT 5392,2989,652,3.00,7875.00
UNION
SELECT 5392,2989,652,4.00,7875.00
UNION
SELECT 5392,2989,652,0.00,7875.00
SELECT * FROM #Orders
DROP TABLE #Orders
------------------------------------------------------------------------------------------
SELECT 855,1197,6,0.00,63.45
UNION
SELECT 855,1197,6,0.00,125.00
--desired result set
SELECT 855,1197,6,0.00,188.45
UNION
SELECT 855,1197,6,0.00,0.00
--and from
SELECT 855,1393,6,0.00,125.00
UNION
SELECT 855,1393,6,0.00,62.82
--desired result set
SELECT 855,1393,6,0.00,187.82
UNION
SELECT 855,1393,6,0.00,0.00
--If Amounts are same
--When ProjectID,ProductID and OrderID are the same, then select max of ProductAmt if Amount is same and store value in one of the rows and make the other values 0
select ID,CSORderID,CSOProjectid,CSOProductID,CSOFProjectAmount,CSOFProductAmount FROM Fact_GeoPhysicalCube WHERE CSORderID =5392 ORDER BY 3,4
SELECT 5392,2989,652,0.00,7875.00 UNION
SELECT 5392,2989,652,0.00,7875.00 UNION
SELECT 5392,2989,652,0.00,7875.00 UNION
SELECT 5392,2989,652,0.00,7875.00
--desired result set
SELECT 5392,2989,652,0.00,7875.00 UNION
SELECT 5392,2989,652,0.00,0.00 UNION
SELECT 5392,2989,652,0.00,0.00 UNION
SELECT 5392,2989,652,0.00,0.00
Thanks,
Plabita Baruah
March 31, 2015 at 11:20 am
Please help .
March 31, 2015 at 6:21 pm
Not 100% sure I understand what you are trying to do, but I'm assuming your orders always have either one line or two.
Not one of the cleverest solutions I've ever constructed, but here it is.
WITH OneRowOrders AS
(
SELECT OrderID, ProjectID, ProductID, ProjectAmt, ID=MIN(ID)
FROM #Orders
GROUP BY OrderID, ProjectID, ProductID, ProjectAmt
HAVING COUNT(*) = 1
)
SELECT ID, OrderID, ProjectID, ProductID, ProjectAmt
,ProductAmt = ISNULL(ProductAmt +
LEAD(ProductAmt, 1) OVER
(
PARTITION BY OrderID, ProjectID, ProductID, ProjectAmt
ORDER BY ID, rn
), 0)
FROM
(
SELECT ID, OrderID, ProjectID, ProductID, ProjectAmt, ProductAmt
,rn=ROW_NUMBER() OVER (PARTITION BY OrderID, ProjectID, ProductID, ProjectAmt ORDER BY ID)
FROM #Orders
UNION ALL
SELECT ID, OrderID, ProjectID, ProductID, ProjectAmt, 0, 2
FROM OneRowOrders
) a
ORDER BY ID, rn;
It does assume that if you are posting in a SQL 2012 forum you are running on SQL 2012.
Edit: Fixed indentation.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 1, 2015 at 6:03 am
It works for others except ProjectID 2989 and ProductId 652 . When OrderID,ProjectID,ProductId and ProjectAmt are same then it should keep the amount in one of the rows and make Amt 0 for the other rows of the combination.
Sample result :
SELECT 5392, 2989, 652 ,1.00, 7875.00
UNION
SELECT 5392, 2989, 652 ,3.00, 0.00
UNION
SELECT 5392, 2989, 652, 4.00, 0.00
UNION
SELECT 5392, 2989, 652, 0.00, 0.00
Right now, it's inserting a new row for this combination .
5539229896520.007875.00
5539229896520.000.00
6539229896521.007875.00
6539229896521.000.00
7539229896523.007875.00
7539229896523.000.00
8539229896524.007875.00
8539229896524.000.00
Thanks,
PSB
April 1, 2015 at 6:04 am
Also it will be helpful if you can provide me with an Update to #orders table instead of Select .
April 1, 2015 at 6:49 am
Something like: -
UPDATE a
SET ProductAmt = ISNULL(a.ProductAmt + CASE WHEN a.pos <> 1 THEN NULL
WHEN a.next = a.ProductAmt
THEN 0
ELSE a.next
END, 0)
FROM ( SELECT ID,
OrderID,
ProjectId,
ProductID,
ProjectAmt,
ProductAmt,
LEAD(ProductAmt) OVER ( PARTITION BY OrderID, ProjectId,
ProductID ORDER BY ID ) AS [next],
ROW_NUMBER() OVER ( PARTITION BY OrderID, ProjectId,
ProductID ORDER BY ID ) AS [pos]
FROM #Orders
) a;
Returns: -
ID OrderID ProjectId ProductID ProjectAmt ProductAmt
----------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
1 855 1393 6 0.00 187.82
2 855 1393 6 0.00 0.00
3 855 1197 6 0.00 188.45
4 855 1197 6 0.00 0.00
5 5392 2989 652 0.00 7875.00
6 5392 2989 652 3.00 0.00
7 5392 2989 652 4.00 0.00
8 5392 2989 652 0.00 0.00
--EDIT--
Bear in mind that this won't work if you included a non-pair match, e.g.
INSERT INTO #Orders
(
OrderID,
ProjectId,
ProductID,
ProjectAmt,
ProductAmt
)
SELECT 855, 1393, 6, 0.00, 125.00
UNION ALL
SELECT 855, 1393, 6, 0.00, 62.82
UNION ALL
SELECT 855, 1393, 6, 0.00, 11.82
UNION ALL
SELECT 855, 1197, 6, 0.00, 63.45
UNION ALL
SELECT 855, 1197, 6, 0.00, 125.00
UNION ALL
SELECT 5392, 2989, 652, 0.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 3.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 4.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 0.00, 7875.00;
If this is a possibility, we can fix the query.
April 1, 2015 at 8:12 am
Plain Goup By
CREATE TABLE #Orders (ID INT IDENTITY(1,1),OrderID INT,ProjectId INT,ProductID INT,ProjectAmt DECIMAL(10,2),ProductAmt DECIMAL(10,2));
INSERT INTO #Orders
(
OrderID,
ProjectId,
ProductID,
ProjectAmt,
ProductAmt
)
SELECT 855, 1393, 6, 0.00, 125.00
UNION ALL
SELECT 855, 1393, 6, 0.00, 62.82
UNION ALL
SELECT 855, 1393, 6, 0.00, 11.82
UNION ALL
SELECT 855, 1197, 6, 0.00, 63.45
UNION ALL
SELECT 855, 1197, 6, 0.00, 125.00
UNION ALL
SELECT 5392, 2989, 652, 0.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 3.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 4.00, 7875.00
UNION ALL
SELECT 5392, 2989, 652, 0.00, 7875.00;
with totals as (
select OrderID, ProjectID, ProductID,
minId = min(id), minAmt = min(ProductAmt), maxAmt=max(ProductAmt), sumAmt=sum(ProductAmt)
from #orders
group by OrderID, ProjectID, ProductID
)
update #orders
set ProductAmt = case o.ID when minId then
case minAmt when maxAmt then minAmt else sumAmt end
else 0 end
from #orders O
join totals on totals.OrderID = o.OrderID
and totals.ProjectID = o.ProjectId
and o.ProductID = totals.ProductID ;
April 1, 2015 at 9:50 am
Thanks . Both solution works perfectly .
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply