March 14, 2013 at 1:11 pm
Table Image : http://tinypic.com/r/1075g6v/6
So I have this query which searches id by id and calculates the cost accordingly. But is very slow and I would like to understand how I could convert it into a set based operation.
So depending on our condition we calculate our modeled costs differently.
When user updates a driver we can run a update on the entire driver column based on where it has changed.
But when it comes to calculating the modeled cost. We do it row by row as the fixed cost differs and then divide by months. I have pasted the code below. Is there still a way to this by set based operation ?
First we update the drivers in the same table depending on what value has changed using an update and then update the modeled cost row by row (which is really slow)
Code :
SELECT @rowCounter = 1, @totalrows = @@ROWCOUNT
WHILE @rowCounter <= @totalrows
BEGIN
SELECT @currentId = tempId
FROM @temp
WHERE row = @rowCounter
SELECT
@newModeledCost =
case when not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model') then
ISNULL(DriverValue1,0)*ISNULL(DriverValue2,0)*ISNULL(UnitA,0)*ISNULL(UnitB,0)+ISNULL(FixedCost,0)
-- normal allocation for all scenarios
else
(ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(NULLIF(DriverValue2,0),1))* ISNULL(UnitB,0))+ISNULL(FixedCost,0)
--(ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(DriverValue2,0))*ISNULL(UnitB,0))+ISNULL(FixedCost,0)
-- allocation for model scenarios
end
,
@oldModeledCost = ISNULL(ModeledCost,0),
@newOct = (ISNULL(@newModeledCost,0) * (ISNULL(Oct, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newNov = (ISNULL(@newModeledCost,0) * (ISNULL(Nov, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newDec = (ISNULL(@newModeledCost,0) * (ISNULL(Dec, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJan = (ISNULL(@newModeledCost,0) * (ISNULL(Jan, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newFeb = (ISNULL(@newModeledCost,0) * (ISNULL(Feb, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newMar = (ISNULL(@newModeledCost,0) * (ISNULL(Mar, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newApr = (ISNULL(@newModeledCost,0) * (ISNULL(Apr, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newMay = (ISNULL(@newModeledCost,0) * (ISNULL(May, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJun = (ISNULL(@newModeledCost,0) * (ISNULL(Jun, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJul = (ISNULL(@newModeledCost,0) * (ISNULL(Jul, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newAug = (ISNULL(@newModeledCost,0) * (ISNULL(Aug, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newSep = (ISNULL(@newModeledCost,0) * (ISNULL(Sep, 0) /ISNULL(NULLIF(@oldModeledCost,0),1)))
FROM dbo.TBF_BUDGETExpenses
WHERE BudgetId = @currentId
--and not exists (select 1 from dbo.DIMSTD_SCENARIO where SCENARIO0_Name = SCENARIO and SCENARIO2_Name = 'Model')
UPDATE dbo.TBF_BUDGETExpenses
SET ModeledCost = @newModeledCost,
Oct = @newOct,
Nov = @newNov,
Dec = @newDec,
Jan = @newJan,
Feb = @newFeb,
Mar = @newMar,
Apr = @newApr,
May = @newMay,
Jun = @newJun,
Jul = @newJul,
Aug = @newAug,
Sep = @newSep,
Username = 'Cascade',
lastmodified = getdate()
WHERE BudgetId = @currentId
AND @oldModeledCost <> 0
Print 'Record Update ' + CAST(@currentId AS VARCHAR(15))
SET @rowCounter = @rowCounter + 1
END
March 14, 2013 at 1:20 pm
We will really need the DDL (CREATE TABLE statement(s)) for the table(s), some sample data (as opposed to real data) as a series of INSERT INTO statements, and the expected results based on the processing that needs to be done.
My guess; yes, we can do it set based. we just don't have the info to do it.
March 14, 2013 at 6:14 pm
Here's the DDL statement:
CREATE TABLE dbo.TBF_BUDGETExpenses
(
BudgetID int not null,
Scenario varchar(50) not null,
DriverValue1 float null,
DriverValue2 float null,
UnitA float null,
UnitB float null,
FixedCost float null,
ModeledCost decimal(18,2)null,
Oct decimal(18,2) null,
Nov decimal(18,2) null,
Dec decimal(18,2) null,
Jan decimal(18,2) null,
Feb decimal(18,2) null,
Mar decimal(18,2) null,
Apr decimal(18,2) null,
Jun decimal(18,2) null,
Jul decimal(18,2) null,
Aug decimal(18,2) null,
Sep decimal(18,2) null,
)
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71600, N'Model 1', 6330, 5918, 70791.61, 1.025, 0, CAST(77612.99 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14915.98 AS Decimal(18, 2)), CAST(29371.84 AS Decimal(18, 2)), CAST(33325.17 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71601, N'Model 1', 7600, 6555, 12357.32, 1.025, 0, CAST(14685.51 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(146.17 AS Decimal(18, 2)), CAST(146.17 AS Decimal(18, 2)), CAST(2447.59 AS Decimal(18, 2)), CAST(2447.59 AS Decimal(18, 2)), CAST(4749.00 AS Decimal(18, 2)), CAST(4749.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71602, N'Model 1', 6330, 5918, 53577.89, 1.025, 0, CAST(58740.58 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(22492.53 AS Decimal(18, 2)), CAST(36248.05 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71603, N'Model 1', 7600, 6555, 16176.24, 1.025, 0, CAST(19223.94 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(77.96 AS Decimal(18, 2)), CAST(77.96 AS Decimal(18, 2)), CAST(3216.98 AS Decimal(18, 2)), CAST(3216.98 AS Decimal(18, 2)), CAST(6356.01 AS Decimal(18, 2)), CAST(6278.05 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71604, N'Model 1', 6330, 5918, 37751.7, 1.025, 0, CAST(41389.40 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(14986.17 AS Decimal(18, 2)), CAST(26403.23 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71605, N'Model 1', 7600, 6555, 11768.04, 1.025, 0, CAST(13985.21 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(64.97 AS Decimal(18, 2)), CAST(64.97 AS Decimal(18, 2)), CAST(4596.77 AS Decimal(18, 2)), CAST(4596.77 AS Decimal(18, 2)), CAST(4596.77 AS Decimal(18, 2)), CAST(64.97 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (70673, N'Model 1', 457.04, 329, 4117.43, 1.025, 0, CAST(5862.84 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2876.11 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (70674, N'Model 1', 300, 200, 4175, 1.025, 0, CAST(6419.06 AS Decimal(18, 2)), CAST(810.26 AS Decimal(18, 2)), CAST(814.88 AS Decimal(18, 2)), CAST(811.80 AS Decimal(18, 2)), CAST(807.19 AS Decimal(18, 2)), CAST(801.04 AS Decimal(18, 2)), CAST(799.50 AS Decimal(18, 2)), CAST(785.66 AS Decimal(18, 2)), CAST(788.74 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
March 14, 2013 at 6:36 pm
So the calculation goes like this :
Modeled Cost = (Driver1/Driver2)*UnitA*UnitB+FixedCost
For eg :
Old Modeled Cost (6330/5918)*70791.61*1.025+0 = 77612.98
So If I was to update Driver1 from 6330 to 7000
New Modeled Cost (7000/5918)*70791.61*1.025+0 = 85827...
Now for the month calculation
newOct = (NewModeled Cost/ Old Modeled Cost)*OldOct.
....
...
...
newSep = (NewModeled Cost/ Old Modeled Cost)*OldSep
So we can include a condition that if 0 or null exclude the update and will only update in cells where numbers are there and the calculation is not (newModeledCost /12).
Hope this makes sense.. Thanks for helping out.. I'm just wondering if there is a way to do away from the row by row update, because our fixed cost and 2 other columns could have different values per row.
🙁
March 15, 2013 at 3:04 pm
Any suggestions ?
March 15, 2013 at 3:51 pm
Haven't had a chance to even look at it yet.
March 27, 2013 at 11:32 am
Hi Lynn did you get a change to look into this. I need to figure out if we can't do this.
March 27, 2013 at 12:29 pm
venkyzrocks (3/27/2013)
Hi Lynn did you get a change to look into this. I need to figure out if we can't do this.
It can be done but we still don't have all the information. We don't have a definition or data for dbo.DIMSTD_SCENARIO
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2013 at 2:59 pm
1. Sorry guys for my oversight.Totally my mistake, it was just a condition that really didn't make much of a difference, but here's the new DDL script (Please see below).
2. I have modified the case condition and have included the stored procedure script. We have some additional code in the stp that checks if the driver value has been updated form the frontend, I have removed that because here I was hoping we could build a new stp based on set based operation.
3. I have provided a sample of the data . I can provide more data if you'll want. It's has about 70K+ records, but it may or may not update all rows at one time.(It depends on what driver value gets updated). Lets assume the worst case scenario of we can.
Let me know if I can clear any doubts. I'll respond the very same day. Thanks guys.
/* Copy this DDL Script */
USE [Test]
GO
/****** Object: Table [dbo].[TBF_BUDGETExpenses] Script Date: 03/27/2013 16:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBF_BUDGETExpenses](
[BudgetID] [int] NOT NULL,
[Scenario] [varchar](50) NOT NULL,
[DriverValue1] [float] NULL,
[DriverValue2] [float] NULL,
[UnitA] [float] NULL,
[UnitB] [float] NULL,
[FixedCost] [float] NULL,
[ModeledCost] [decimal](18, 2) NULL,
[Oct] [decimal](18, 2) NULL,
[Nov] [decimal](18, 2) NULL,
[Dec] [decimal](18, 2) NULL,
[Jan] [decimal](18, 2) NULL,
[Feb] [decimal](18, 2) NULL,
[Mar] [decimal](18, 2) NULL,
[Apr] [decimal](18, 2) NULL,
[Jun] [decimal](18, 2) NULL,
[Jul] [decimal](18, 2) NULL,
[Aug] [decimal](18, 2) NULL,
[Sep] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71600, N'Model 1', 6330, 5918, 70791.61, 1.025, 512, CAST(78124.99 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(15014.38 AS Decimal(18, 2)), CAST(29565.60 AS Decimal(18, 2)), CAST(33545.01 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71601, N'Model 1', 7600, 6555, 12357.32, 1.025, 212, CAST(14897.51 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(148.28 AS Decimal(18, 2)), CAST(148.28 AS Decimal(18, 2)), CAST(2482.92 AS Decimal(18, 2)), CAST(2482.92 AS Decimal(18, 2)), CAST(4817.56 AS Decimal(18, 2)), CAST(4817.56 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71602, N'Model 1', 6330, 5918, 53577.89, 1.025, 112, CAST(58852.58 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(22535.42 AS Decimal(18, 2)), CAST(36317.16 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71603, N'Model 1', 7600, 6555, 16176.24, 1.025, 322, CAST(19545.94 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(79.27 AS Decimal(18, 2)), CAST(79.27 AS Decimal(18, 2)), CAST(3270.86 AS Decimal(18, 2)), CAST(3270.86 AS Decimal(18, 2)), CAST(6462.47 AS Decimal(18, 2)), CAST(6383.21 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71604, N'Model 1', 6330, 5918, 37751.7, 1.025, 5223, CAST(46612.40 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(16877.30 AS Decimal(18, 2)), CAST(29735.10 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (71605, N'Model 1', 7600, 6555, 11768.04, 1.025, 542354, CAST(556339.21 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2584.54 AS Decimal(18, 2)), CAST(2584.54 AS Decimal(18, 2)), CAST(182861.99 AS Decimal(18, 2)), CAST(182861.99 AS Decimal(18, 2)), CAST(182861.99 AS Decimal(18, 2)), CAST(2584.54 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (70673, N'Model 1', 457.04, 329, 4117.43, 1.025, 2223, CAST(8085.84 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3966.64 AS Decimal(18, 2)))
INSERT [dbo].[TBF_BUDGETExpenses] ([BudgetID], [Scenario], [DriverValue1], [DriverValue2], [UnitA], [UnitB], [FixedCost], [ModeledCost], [Oct], [Nov], [Dec], [Jan], [Feb], [Mar], [Apr], [Jun], [Jul], [Aug], [Sep]) VALUES (70674, N'Model 1', 300, 200, 4175, 1.025, 33322, CAST(39741.06 AS Decimal(18, 2)), CAST(5016.40 AS Decimal(18, 2)), CAST(5045.01 AS Decimal(18, 2)), CAST(5025.94 AS Decimal(18, 2)), CAST(4997.40 AS Decimal(18, 2)), CAST(4959.32 AS Decimal(18, 2)), CAST(4949.79 AS Decimal(18, 2)), CAST(4864.10 AS Decimal(18, 2)), CAST(4883.17 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)))
/****** Object: StoredProcedure [dbo].[stp_BudgetCascadeDriverUpdates] Script Date: 03/27/2013 16:30:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[stp_BudgetCascadeDriverUpdates]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @temp TABLE (tempId INT, row INT IDENTITY (1,1))
DECLARE @rowCounter INT, @totalrows INT, @currentId INT
DECLARE @newModeledCost DECIMAL(18,2), @oldModeledCost DECIMAL(18,2)
DECLARE @newOct DECIMAL(18,2), @newNov DECIMAL(18,2), @newDec DECIMAL(18,2)
DECLARE @newJan DECIMAL(18,2), @newFeb DECIMAL(18,2), @newMar DECIMAL(18,2)
DECLARE @newApr DECIMAL(18,2), @newMay DECIMAL(18,2), @newJun DECIMAL(18,2)
DECLARE @newJul DECIMAL(18,2), @newAug DECIMAL(18,2), @newSep DECIMAL(18,2)
SELECT @rowCounter = 1, @totalrows = @@ROWCOUNT
WHILE @rowCounter <= @totalrows
BEGIN
SELECT @currentId = tempId
FROM @temp
WHERE row = @rowCounter
SELECT
@newModeledCost =
case when not exists (select 1 from dbo.TBF_BUDGETExpenses where SCENARIO = 'Model 1') then
ISNULL(DriverValue1,0)*ISNULL(DriverValue2,0)*ISNULL(UnitA,0)*ISNULL(UnitB,0)+ISNULL(FixedCost,0)
else
(ISNULL(unita,0) * (ISNULL(DriverValue1,0)/ISNULL(NULLIF(DriverValue2,0),1))* ISNULL(UnitB,0))+ISNULL(FixedCost,0)
end
,
@oldModeledCost = ISNULL(ModeledCost,0),
@newOct = (ISNULL(@newModeledCost,0) * (ISNULL(Oct, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newNov = (ISNULL(@newModeledCost,0) * (ISNULL(Nov, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newDec = (ISNULL(@newModeledCost,0) * (ISNULL(Dec, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJan = (ISNULL(@newModeledCost,0) * (ISNULL(Jan, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newFeb = (ISNULL(@newModeledCost,0) * (ISNULL(Feb, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newMar = (ISNULL(@newModeledCost,0) * (ISNULL(Mar, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newApr = (ISNULL(@newModeledCost,0) * (ISNULL(Apr, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJun = (ISNULL(@newModeledCost,0) * (ISNULL(Jun, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newJul = (ISNULL(@newModeledCost,0) * (ISNULL(Jul, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newAug = (ISNULL(@newModeledCost,0) * (ISNULL(Aug, 0) /ISNULL(NULLIF(@oldModeledCost,0),1))),
@newSep = (ISNULL(@newModeledCost,0) * (ISNULL(Sep, 0) /ISNULL(NULLIF(@oldModeledCost,0),1)))
FROM dbo.TBF_BUDGETExpenses
WHERE BudgetId = @currentId
UPDATE dbo.TBF_BUDGETExpenses
SET ModeledCost = @newModeledCost,
Oct = @newOct,
Nov = @newNov,
Dec = @newDec,
Jan = @newJan,
Feb = @newFeb,
Mar = @newMar,
Apr = @newApr,
Jun = @newJun,
Jul = @newJul,
Aug = @newAug,
Sep = @newSep
WHERE BudgetId = @currentId
AND @oldModeledCost <> 0
Print 'Record Update ' + CAST(@currentId AS VARCHAR(15))
SET @rowCounter = @rowCounter + 1
END
END
GO
Initial Result
BudgetID Scenario DriverValue1 DriverValue2 UnitA UnitB FixedCost ModeledCost Oct Nov Dec Jan Feb Mar Apr Jun Jul Aug Sep
71600 Model 1 6330 5918 70791.61 1.025 512 78124.99 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 15014.38 29565.60 33545.01
71601 Model 1 7600 6555 12357.32 1.025 212 14897.51 0.00 0.00 148.28 148.28 2482.92 2482.92 4817.56 4817.56 0.00 0.00 0.00
71602 Model 1 6330 5918 53577.89 1.025 112 58852.58 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 22535.42 36317.16 0.00
71603 Model 1 7600 6555 16176.24 1.025 322 19545.94 0.00 0.00 79.27 79.27 3270.86 3270.86 6462.47 6383.21 0.00 0.00 0.00
71604 Model 1 6330 5918 37751.7 1.025 5223 46612.40 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 16877.30 29735.10 0.00
71605 Model 1 7600 6555 11768.04 1.025 542354 556339.21 0.00 0.00 2584.54 2584.54 182861.99 182861.99 182861.99 2584.54 0.00 0.00 0.00
70673 Model 1 457.04 329 4117.43 1.025 2223 8085.84 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 3966.64
70674 Model 1 300 200 4175 1.025 33322 39741.06 5016.40 5045.01 5025.94 4997.40 4959.32 4949.79 4864.10 4883.17 0.00 0.00 0.00
Expected Result : Lets say I updated the driver value1 from 6330 to 7330, then it should update 3 rows based on
(Driver1Value/Driver2Value)*UnitA*UNiTB+FixedCost = Modeled Cost and then split according to its ratios
BudgetID Scenario DriverValue1 DriverValue2 UnitA UnitB FixedCost ModeledCost Oct Nov Dec Jan Feb Mar Apr Jun Jul Aug Sep
71600 Model 1 7330 5918 70791.61 1.025 512 90386.12 0 0 0 0 0 0 0 0 17370.8 34205.7 38809.6
71601 Model 1 7600 6555 12357.32 1.025 212 14897.51 0 0 148.28 148.28 2482.924 2482.924 4817.557 4817.6 0 0 0
71602 Model 1 7330 5918 53577.89 1.025 112 68132.29 0 0 0 0 0 0 0 0 26088.7 42043.6 0
71603 Model 1 7600 6555 16176.24 1.025 322 19545.94 0 0 79.266 79.266 3270.864 3270.864 6462.472 6383.2 0 0 0
71604 Model 1 7330 5918 37751.7 1.025 5223 53151.01 0 0 0 0 0 0 0 0 19244.8 33906.2 0
71605 Model 1 7600 6555 11768.04 1.025 542354 556339.21 0 0 2584.5 2584.5 182862 182862 182862 2584.5 0 0 0
70673 Model 1 457.04 329 4117.43 1.025 2223 8085.84 0 0 0 0 0 0 0 0 0 0 3966.64
70674 Model 1 300 200 4175 1.025 33322 39741.06 5016.4 5045 5025.9 4997.4 4959.321 4949.787 4864.102 4883.2 0 0 0
March 27, 2013 at 5:25 pm
Uploaded the data zip file at
http://wikisend.com/download/240868/Data .zip
This contains the insert statement for about 75000 rows, which will really test the performance.
April 3, 2013 at 1:49 pm
Hi Guys,
Any more inputs required or do you'll think this would still work ?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply