April 24, 2012 at 12:03 pm
I have a pricing table where I need to updated the ending pricing amount to be the next value of the begining pricing amount based on the companyid that the price belongs to.
I have attached the SQL for the example table and have listed my desired outcome.
What is the best way to proceed? Most people suggest not using a cursor. Does anyone have any good workarounds?
--SQL Server 2008 Insert construct was used
CREATE TABLE #PriceGroup
(
[PriceKey] [int] IDENTITY(1,1) NOT NULL,
[PriceCompanyID] [int] NULL,
[PriceBeginAmt] [numeric](10, 2) NULL,
[PriceEndAmt] [numeric](10, 2) NULL,
)
INSERT INTO #PriceGroup
( PriceCompanyID
,PriceBeginAmt
,PriceEndAmt
)
VALUES (101 ,0.01 ,NULL),
(101 ,0.11 ,NULL),
(101 ,0.21 ,NULL),
(101 ,0.31 ,NULL),
(101 ,0.41 ,NULL),
(101 ,0.51 ,NULL),
(101 ,0.61 ,NULL),
(101 ,0.71 ,NULL),
(101 ,0.81 ,NULL),
(101 ,0.91 ,NULL),
(101 ,1.01 ,NULL),
(101 ,1.11 ,NULL),
(101 ,1.21 ,NULL),
(101 ,1.31 ,NULL),
(102 ,0.01 ,NULL),
(102 ,0.10 ,NULL),
(102 ,0.20 ,NULL),
(102 ,0.30 ,NULL),
(102 ,0.40 ,NULL),
(102 ,0.50 ,NULL),
(102 ,0.60 ,NULL),
(102 ,0.70 ,NULL),
(102 ,0.80 ,NULL),
(102 ,0.90 ,NULL),
(102 ,1.00 ,NULL),
(102 ,1.10 ,NULL),
(102 ,1.20 ,NULL),
(102 ,1.30 ,NULL)
SELECT *
FROM #PriceGroup
-- Desired Outcome
/*
PriceKeyPriceCompanyIDPriceBeginAmtPriceEndAmt
11010.010.00
21010.110.21
31010.210.31
--etc
PriceKeyPriceCompanyIDPriceBeginAmtPriceEndAmt
151020.010.10
161020.100.20
171020.200.30
--etc
*/
Thanks in advance.
April 24, 2012 at 12:07 pm
Read this article and the discussion following it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply