"progressive update" query - urgent help needed.

Question

Post reply

"progressive update" query - urgent help needed.

Scott Toberman

Grasshopper

Points: 21
More actions
March 11, 2006 at 12:14 am

#168621

I have the following snippet as part of a query I'm running:

CREATE TABLE #TempTable (ID1 int, ID2 int, WeekEndDate smalldatetime, RowNum int,

   Amount float, factor float, CalcedAmount float)

-- Fill #TempTable here

DECLARE @numWeeks int, @j-2 int, @val float

SELECT @numWeeks = MAX(RowNum) FROM #TempTable

WHILE @j-2 <= @numWeeks

BEGIN

    UPDATE #TempTable

        SET @val = CASE WHEN RowNum = @j-2 THEN Amount*decay ELSE @val * (1-decay) end,

     Adstock_Amount = Adstock_Amount + @val

      WHERE RowNum >= @j-2

    SET @j-2 = @j-2 + 1

END

When the table is filled, RowNum receives a value of 1..N for each ID1/ID2 combination. Typically, i will have between 52 weeks in scope each time this executes, which means 52 trips thru the loop. I've struggled with getting this right, and can't find any way to keep the "residual" correct using only a single update. Anybody out there got any ideas? With 5 ID1s and 220 ID2s and 52 weeks, This query is taking 5 seconds, and this is only 1 step of a process that must complete in < 10.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

paramind SSCrazy Points: 2130 More actions · Answer 1

paramind

SSCrazy

Points: 2130

March 11, 2006 at 4:41 am

#626129

What the heck are you trying to do

Where does "decay" come from?

_/_/_/ paramind _/_/_/

Scott Toberman Grasshopper Points: 21 More actions · Answer 2

Sorry, dumb mistake on my part. I've updated the code below.

What we are trying to do here is to apply a "decay" algorithm which spreads the effect of money spend in week X accross subsequent weeks. So if i spend $100 this week, i don't get $100 of effect this week. Some of that effect appears next week, and the week after, and the week after, etc. Meanwhile, in week X+1, I spend another $100. So in X+1, the CalcedAmount is the effect from Week X + the effect from Week X+1.

That's really the best explanation i can give you. We have PhD-level modeling experts that understand this (one of whom wrote the original version of the algorithm using nested while loops - what that degenerated badly. I'm just trying to speed this up so it's usable in our app - this step in the algorithm has the potential to run every time the user makes a change to an input value in the app. Right now, for 1 ID1 and 5 ID2s, it screens. For 220 ID1s, it's 5-7 seconds. Some of our clients could have 600 ID1 values (and several more ID2s) so this is critical for me to figure out.

Here's the updated (and consistent) code.

CREATE TABLE #TempTable (ID1 int, ID2 int, WeekEndDate smalldatetime, RowNum int,

Amount float, decay float, CalcedAmount float)

-- Fill #TempTable here

DECLARE @numWeeks int, @j-2 int, @val float

SELECT @numWeeks = MAX(RowNum) FROM #TempTable

WHILE @j-2 <= @numWeeks

BEGIN

UPDATE #TempTable

SET @val = CASE WHEN RowNum = @j-2 THEN Amount*decay ELSE @val * (1-decay) end,

Adstock_Amount = Adstock_Amount + @val

WHERE RowNum >= @j-2

SET @j-2 = @j-2 + 1

END

I'll post some sample data in a couple of hours - i have a conflict right now. Thanks in advance for all your help.

UPDATE:

Below is some code go generate test data. Note that I've included a column called ExpectedResults, which shows the expected results of the calculation.

CREATE TABLE TempTable (ID1 int, ID2 int, WeekEndDate smalldatetime, RowNum int,

Amount float, decay float, ExpectedResult float, CalcedAmount float)

INSERT TempTable VALUES(78,1,'1/8/06',1,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'1/15/06',2,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'1/22/06',3,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'1/29/06',4,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'2/5/06',5,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'2/12/06',6,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'2/19/06',7,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'2/26/06',8,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'3/5/06',9,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'3/12/06',10,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'3/19/06',11,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'3/26/06',12,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'4/2/06',13,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'4/9/06',14,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'4/16/06',15,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'4/23/06',16,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'4/30/06',17,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'5/7/06',18,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,1,'5/14/06',19,16.404253,0.2062994740159,3.38418876552375,0)

INSERT TempTable VALUES(78,1,'5/21/06',20,30.8047551287027,0.2062994740159,9.04103718346566,0)

INSERT TempTable VALUES(78,1,'5/28/06',21,58.7821879999999,0.2062994740159,19.3026104338622,0)

INSERT TempTable VALUES(78,1,'6/4/06',22,84.6029029999999,0.2062994740159,32.7740264433408,0)

INSERT TempTable VALUES(78,1,'6/11/06',23,86.22413,0.2062994740159,43.800754693175,0)

INSERT TempTable VALUES(78,1,'6/18/06',24,33.2709479999999,0.2062994740159,41.6284611108839,0)

INSERT TempTable VALUES(78,1,'6/25/06',25,59.314047,0.2062994740159,45.2769881774716,0)

INSERT TempTable VALUES(78,1,'7/2/06',26,73.153404,0.2062994740159,51.0278780991077,0)

INSERT TempTable VALUES(78,1,'7/9/06',27,46.1834920173646,0.2062994740159,50.0284837985141,0)

INSERT TempTable VALUES(78,1,'7/16/06',28,60.7420854065372,0.2062994740159,52.2386941750652,0)

INSERT TempTable VALUES(78,1,'7/23/06',29,59.6350256925434,0.2062994740159,53.7645534767682,0)

INSERT TempTable VALUES(78,1,'7/30/06',30,44.6962553626149,0.2062994740159,51.893768345599,0)

INSERT TempTable VALUES(78,1,'8/6/06',31,3.116,0.2062994740159,41.8309403922325,0)

INSERT TempTable VALUES(78,1,'8/13/06',32,51.810039,0.2062994740159,43.8896231861677,0)

INSERT TempTable VALUES(78,1,'8/20/06',33,58.899229,0.2062994740159,46.9860969707473,0)

INSERT TempTable VALUES(78,1,'8/27/06',34,49.5894899999999,0.2062994740159,47.5231755833388,0)

INSERT TempTable VALUES(78,1,'9/3/06',35,1.77599999999999,0.2062994740159,38.0855573227829,0)

INSERT TempTable VALUES(78,1,'9/10/06',36,34.884063,0.2062994740159,37.4250907279279,0)

INSERT TempTable VALUES(78,1,'9/17/06',37,56.3441239999999,0.2062994740159,41.3280773408457,0)

INSERT TempTable VALUES(78,1,'9/24/06',38,29.726798,0.2062994740159,38.9347395149177,0)

INSERT TempTable VALUES(78,1,'10/1/06',39,48.6844019999999,0.2062994740159,40.9460897574227,0)

INSERT TempTable VALUES(78,1,'10/8/06',40,1.476,0.2062994740159,32.803431001106,0)

INSERT TempTable VALUES(78,1,'10/15/06',41,28.874069,0.2062994740159,31.9928056870598,0)

INSERT TempTable VALUES(78,1,'10/22/06',42,42.7063359999999,0.2062994740159,34.2030013554727,0)

INSERT TempTable VALUES(78,1,'10/29/06',43,47.607978,0.2062994740159,36.9684409864341,0)

INSERT TempTable VALUES(78,1,'11/5/06',44,7.84102145045965,0.2062994740159,30.9594696567221,0)

INSERT TempTable VALUES(78,1,'11/12/06',45,41.069989,0.2062994740159,33.0452644792679,0)

INSERT TempTable VALUES(78,1,'11/19/06',46,33.6987297297297,0.2062994740159,33.1800740167259,0)

INSERT TempTable VALUES(78,1,'11/26/06',47,43.2552032686414,0.2062994740159,35.2585678820382,0)

INSERT TempTable VALUES(78,1,'12/3/06',48,4.87099999999999,0.2062994740159,28.9896286113513,0)

INSERT TempTable VALUES(78,1,'12/10/06',49,35.237639,0.2062994740159,30.2785898681754,0)

INSERT TempTable VALUES(78,1,'12/17/06',50,59.3337519999999,0.2062994740159,36.2726545334175,0)

INSERT TempTable VALUES(78,1,'12/24/06',51,63.854357,0.2062994740159,41.9627452447365,0)

INSERT TempTable VALUES(78,1,'12/31/06',52,2.98099999999999,0.2062994740159,33.9208317045255,0)

INSERT TempTable VALUES(78,2,'1/8/06',1,64.932954,0.10910128185966,7.08426851633438,0)

INSERT TempTable VALUES(78,2,'1/15/06',2,50.406813,0.10910128185966,11.8108136529244,0)

INSERT TempTable VALUES(78,2,'1/22/06',3,65.8291499999999,0.10910128185966,17.7042833923167,0)

INSERT TempTable VALUES(78,2,'1/29/06',4,55.0250999999999,0.10910128185966,21.7760323242642,0)

INSERT TempTable VALUES(78,2,'2/5/06',5,54.025348,0.10910128185966,25.2944740035838,0)

INSERT TempTable VALUES(78,2,'2/12/06',6,49.932537,0.10910128185966,27.9825182590319,0)

INSERT TempTable VALUES(78,2,'2/19/06',7,47.358223,0.10910128185966,30.0964324832058,0)

INSERT TempTable VALUES(78,2,'2/26/06',8,70.3570519999999,0.10910128185966,34.4889176809521,0)

INSERT TempTable VALUES(78,2,'3/5/06',9,53.0393669999999,0.10910128185966,36.5127954807329,0)

INSERT TempTable VALUES(78,2,'3/12/06',10,48.517076,0.10910128185966,37.8224778731879,0)

INSERT TempTable VALUES(78,2,'3/19/06',11,45.7321539999999,0.10910128185966,38.6854336777179,0)

INSERT TempTable VALUES(78,2,'3/26/06',12,45.1575629999999,0.10910128185966,39.3915512831403,0)

INSERT TempTable VALUES(78,2,'4/2/06',13,69.376356,0.10910128185966,42.6629319140613,0)

INSERT TempTable VALUES(78,2,'4/9/06',14,76.154227,0.10910128185966,46.3168751390774,0)

INSERT TempTable VALUES(78,2,'4/16/06',15,70.1678629999999,0.10910128185966,48.9190484883232,0)

INSERT TempTable VALUES(78,2,'4/23/06',16,74.993817,0.10910128185966,51.7638391571411,0)

INSERT TempTable VALUES(78,2,'4/30/06',17,67.868655,0.10910128185966,53.5208952097108,0)

INSERT TempTable VALUES(78,2,'5/7/06',18,71.202864,0.10910128185966,55.4500206705338,0)

INSERT TempTable VALUES(78,2,'5/14/06',19,45.519561,0.10910128185966,54.3665947910229,0)

INSERT TempTable VALUES(78,2,'5/21/06',20,30.992261,0.10910128185966,51.8164250118067,0)

INSERT TempTable VALUES(78,2,'5/28/06',21,49.278814,0.10910128185966,51.5395683975574,0)

INSERT TempTable VALUES(78,2,'6/4/06',22,25.3638119999999,0.10910128185966,48.6837598209377,0)

INSERT TempTable VALUES(78,2,'6/11/06',23,36.086944,0.10910128185966,47.3094310675233,0)

INSERT TempTable VALUES(78,2,'6/18/06',24,23.4862329999999,0.10910128185966,44.7102896203599,0)

INSERT TempTable VALUES(78,2,'6/25/06',25,9.85597899999999,0.10910128185966,40.9076396533439,0)

INSERT TempTable VALUES(78,2,'7/2/06',26,22.31809,0.10910128185966,38.8794959569702,0)

INSERT TempTable VALUES(78,2,'7/9/06',27,12.753827,0.10910128185966,36.0291519843236,0)

INSERT TempTable VALUES(78,2,'7/16/06',28,23.3121489999999,0.10910128185966,34.6417106573208,0)

INSERT TempTable VALUES(78,2,'7/23/06',29,3.7610909090909,0.10910128185966,31.2725954581682,0)

INSERT TempTable VALUES(78,2,'7/30/06',30,20.1015720122574,0.10910128185966,30.053822480535,0)

INSERT TempTable VALUES(78,2,'8/6/06',31,0,0.10910128185966,26.7749119231259,0)

INSERT TempTable VALUES(78,2,'8/13/06',32,28.1876547497446,0.10910128185966,26.929043976448,0)

INSERT TempTable VALUES(78,2,'8/20/06',33,5.12483554647599,0.10910128185966,24.5501768868029,0)

INSERT TempTable VALUES(78,2,'8/27/06',34,24.9191961184882,0.10910128185966,24.5904373580106,0)

INSERT TempTable VALUES(78,2,'9/3/06',35,0,0.10910128185966,21.907589120762,0)

INSERT TempTable VALUES(78,2,'9/10/06',36,9.71677630234933,0.10910128185966,20.5775558153619,0)

INSERT TempTable VALUES(78,2,'9/17/06',37,2.89831971399387,0.10910128185966,18.6487284944031,0)

INSERT TempTable VALUES(78,2,'9/24/06',38,15.002744637385,0.10910128185966,18.2509469819628,0)

INSERT TempTable VALUES(78,2,'10/1/06',39,3.07238508682328,0.10910128185966,16.5949464224169,0)

INSERT TempTable VALUES(78,2,'10/8/06',40,0,0.10910128185966,14.7844164953388,0)

INSERT TempTable VALUES(78,2,'10/15/06',41,9.43047906026557,0.10910128185966,14.2002950581759,0)

INSERT TempTable VALUES(78,2,'10/22/06',42,10.322,0.10910128185966,13.7771680958989,0)

INSERT TempTable VALUES(78,2,'10/29/06',43,17.257,0.10910128185966,14.1568222172925,0)

INSERT TempTable VALUES(78,2,'11/5/06',44,0,0.10910128185966,12.6122947663265,0)

INSERT TempTable VALUES(78,2,'11/12/06',45,9.34999999999999,0.10910128185966,12.2563742255162,0)

INSERT TempTable VALUES(78,2,'11/19/06',46,10.7470561797752,0.10910128185966,12.091705691992,0)

INSERT TempTable VALUES(78,2,'11/26/06',47,10.4919999999999,0.10910128185966,11.9171757503974,0)

INSERT TempTable VALUES(78,2,'12/3/06',48,0.121,0.10910128185966,10.6301978549872,0)

INSERT TempTable VALUES(78,2,'12/10/06',49,14.1859652706843,0.10910128185966,11.0181366380346,0)

INSERT TempTable VALUES(78,2,'12/17/06',50,11.1389999999999,0.10910128185966,11.0313229857549,0)

INSERT TempTable VALUES(78,2,'12/24/06',51,16.1051215526047,0.10910128185966,11.5848809132959,0)

INSERT TempTable VALUES(78,2,'12/31/06',52,0,0.10910128185966,10.3209555554638,0)

INSERT TempTable VALUES(78,3,'1/8/06',1,1.719,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'1/15/06',2,1.193,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'1/22/06',3,2.63399999999999,0.129449436703875,0.644053045973217,0)

INSERT TempTable VALUES(78,3,'1/29/06',4,2.91599999999999,0.129449436703875,0.93815529939307,0)

INSERT TempTable VALUES(78,3,'2/5/06',5,0.966999999999999,0.129449436703875,0.941889229638529,0)

INSERT TempTable VALUES(78,3,'2/12/06',6,1.95,0.129449436703875,1.07238860099693,0)

INSERT TempTable VALUES(78,3,'2/19/06',7,1.377,0.129449436703875,1.11182037501145,0)

INSERT TempTable VALUES(78,3,'2/26/06',8,1.16599999999999,0.129449436703875,1.11883389694705,0)

INSERT TempTable VALUES(78,3,'3/5/06',9,0.854999999999999,0.129449436703875,1.08468074760386,0)

INSERT TempTable VALUES(78,3,'3/12/06',10,1.257,0.129449436703875,1.10698737775978,0)

INSERT TempTable VALUES(78,3,'3/19/06',11,2.189,0.129449436703875,1.24705330221526,0)

INSERT TempTable VALUES(78,3,'3/26/06',12,1.602,0.129449436703875,1.29300095230339,0)

INSERT TempTable VALUES(78,3,'4/2/06',13,2,0.129449436703875,1.38452158077789,0)

INSERT TempTable VALUES(78,3,'4/9/06',14,2.647,0.129449436703875,1.54794870099699,0)

INSERT TempTable VALUES(78,3,'4/16/06',15,1.849,0.129449436703875,1.5869196220719,0)

INSERT TempTable VALUES(78,3,'4/23/06',16,1.88399999999999,0.129449436703875,1.62537650965047,0)

INSERT TempTable VALUES(78,3,'4/30/06',17,2.42099999999999,0.129449436703875,1.72836952230459,0)

INSERT TempTable VALUES(78,3,'5/7/06',18,3.117,0.129449436703875,1.90812695543209,0)

INSERT TempTable VALUES(78,3,'5/14/06',19,0.302,0.129449436703875,1.7002147257765,0)

INSERT TempTable VALUES(78,3,'5/21/06',20,0,0.129449436703875,1.48012288724909,0)

INSERT TempTable VALUES(78,3,'5/28/06',21,3.75599999999999,0.129449436703875,1.77473389750194,0)

INSERT TempTable VALUES(78,3,'6/4/06',22,0.556999999999999,0.129449436703875,1.6170989304151,0)

INSERT TempTable VALUES(78,3,'6/11/06',23,0.308,0.129449436703875,1.44763681128322,0)

INSERT TempTable VALUES(78,3,'6/18/06',24,0,0.129449436703875,1.26024104151081,0)

INSERT TempTable VALUES(78,3,'6/25/06',25,0,0.129449436703875,1.09710354857613,0)

INSERT TempTable VALUES(78,3,'7/2/06',26,0.148999999999999,0.129449436703875,0.974372078276006,0)

INSERT TempTable VALUES(78,3,'7/9/06',27,0.337,0.129449436703875,0.891864621772398,0)

INSERT TempTable VALUES(78,3,'7/16/06',28,0,0.129449436703875,0.776413248867846,0)

INSERT TempTable VALUES(78,3,'7/23/06',29,0.183,0.129449436703875,0.699596238069286,0)

INSERT TempTable VALUES(78,3,'7/30/06',30,0,0.129449436703875,0.609033899131067,0)

INSERT TempTable VALUES(78,3,'8/6/06',31,0.068,0.129449436703875,0.538997365650848,0)

INSERT TempTable VALUES(78,3,'8/13/06',32,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'8/20/06',33,0.231999999999999,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'8/27/06',34,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'9/3/06',35,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'9/10/06',36,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'9/17/06',37,0.078,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'9/24/06',38,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'10/1/06',39,0.209,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'10/8/06',40,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'10/15/06',41,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'10/22/06',42,0.0919999999999999,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'10/29/06',43,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'11/5/06',44,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'11/12/06',45,0.0539999999999999,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'11/19/06',46,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'11/26/06',47,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'12/3/06',48,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'12/10/06',49,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'12/17/06',50,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'12/24/06',51,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,3,'12/31/06',52,0,0.129449436703875,0,0)

INSERT TempTable VALUES(78,4,'1/8/06',1,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'1/15/06',2,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'1/22/06',3,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'1/29/06',4,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'2/5/06',5,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'2/12/06',6,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'2/19/06',7,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'2/26/06',8,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'3/5/06',9,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'3/12/06',10,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'3/19/06',11,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'3/26/06',12,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'4/2/06',13,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'4/9/06',14,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'4/16/06',15,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'4/23/06',16,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'4/30/06',17,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'5/7/06',18,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'5/14/06',19,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'5/21/06',20,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'5/28/06',21,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'6/4/06',22,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'6/11/06',23,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'6/18/06',24,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'6/25/06',25,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'7/2/06',26,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'7/9/06',27,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'7/16/06',28,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'7/23/06',29,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'7/30/06',30,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'8/6/06',31,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'8/13/06',32,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'8/20/06',33,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'8/27/06',34,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'9/3/06',35,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'9/10/06',36,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'9/17/06',37,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'9/24/06',38,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'10/1/06',39,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'10/8/06',40,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'10/15/06',41,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'10/22/06',42,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'10/29/06',43,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'11/5/06',44,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'11/12/06',45,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'11/19/06',46,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'11/26/06',47,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'12/3/06',48,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'12/10/06',49,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'12/17/06',50,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'12/24/06',51,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,4,'12/31/06',52,0,0.2062994740159,0,0)

INSERT TempTable VALUES(78,5,'1/8/06',1,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'1/15/06',2,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'1/22/06',3,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'1/29/06',4,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'2/5/06',5,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'2/12/06',6,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'2/19/06',7,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'2/26/06',8,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'3/5/06',9,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'3/12/06',10,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'3/19/06',11,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'3/26/06',12,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'4/2/06',13,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'4/9/06',14,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'4/16/06',15,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'4/23/06',16,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'4/30/06',17,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'5/7/06',18,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'5/14/06',19,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'5/21/06',20,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'5/28/06',21,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'6/4/06',22,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'6/11/06',23,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'6/18/06',24,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'6/25/06',25,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'7/2/06',26,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'7/9/06',27,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'7/16/06',28,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'7/23/06',29,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'7/30/06',30,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'8/6/06',31,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'8/13/06',32,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'8/20/06',33,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'8/27/06',34,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'9/3/06',35,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'9/10/06',36,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'9/17/06',37,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'9/24/06',38,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'10/1/06',39,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'10/8/06',40,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'10/15/06',41,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'10/22/06',42,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'10/29/06',43,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'11/5/06',44,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'11/12/06',45,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'11/19/06',46,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'11/26/06',47,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'12/3/06',48,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'12/10/06',49,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'12/17/06',50,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'12/24/06',51,0,0.292893218813452,0,0)

INSERT TempTable VALUES(78,5,'12/31/06',52,0,0.292893218813452,0,0)

RyanRandall SSChampion Points: 13623 More actions · Answer 3

Scott,

Your problem is very difficult to get to grips with - not because it's that tricky, but because of the way you've expressed it.

You're much more likely to get valuable input if you can capture the essence of the problem and provide simple, short examples.

That said, here is my suggestion below (as a start for you to work with). I've not used your data because it doesn't seem to be normalized, so is a little trickier (for me) to work with. I could have added more tables and different decay rates (for different Ids etc), but then you really would have to start paying me

Good luck!

--Create amount table

CREATE TABLE #Amount (Id INT IDENTITY(1, 1), Amount FLOAT)

INSERT INTO #Amount

SELECT 0

UNION ALL SELECT 0

UNION ALL SELECT 16.404253

UNION ALL SELECT 30.8047551287026

UNION ALL SELECT 58.7821879999998

UNION ALL SELECT 84.6029029999998

UNION ALL SELECT 86.22413

UNION ALL SELECT 33.2709479999998

UNION ALL SELECT 59.314047

UNION ALL SELECT 73.1534039999999

UNION ALL SELECT 46.1834920173646

UNION ALL SELECT 60.7420854065372

UNION ALL SELECT 59.6350256925434

UNION ALL SELECT 44.6962553626148

UNION ALL SELECT 3.116

UNION ALL SELECT 51.810039

UNION ALL SELECT 58.8992289999999

UNION ALL SELECT 49.5894899999998

UNION ALL SELECT 1.77599999999999

UNION ALL SELECT 34.8840629999999

UNION ALL SELECT 56.3441239999999

UNION ALL SELECT 29.7267979999999

UNION ALL SELECT 48.6844019999998

UNION ALL SELECT 1.476

UNION ALL SELECT 28.8740689999999

UNION ALL SELECT 42.7063359999999

UNION ALL SELECT 47.607978

UNION ALL SELECT 7.84102145045964

UNION ALL SELECT 41.069989

UNION ALL SELECT 33.6987297297296

UNION ALL SELECT 43.2552032686414

UNION ALL SELECT 4.87099999999998

UNION ALL SELECT 35.237639

UNION ALL SELECT 59.3337519999998

UNION ALL SELECT 63.854357

UNION ALL SELECT 2.98099999999999

--Create decay table

CREATE TABLE #Decay (Id INT, Decay FLOAT)

DECLARE @Decay FLOAT

DECLARE @Id INT

SET @Decay = 0.20629947401589999

SET @Id = 0

WHILE @Id < 52

BEGIN

INSERT INTO #Decay SELECT @Id, @Decay * POWER(1-@Decay, @Id)

SET @Id = @Id + 1

END

--Join decay and amount tables together and group to get result

SELECT d.Id + a.Id, SUM(Amount * Decay)

FROM #Decay d CROSS JOIN #Amount a

GROUP BY d.Id + a.Id

ORDER BY d.Id + a.Id

--Tidy up

DROP TABLE #Amount

DROP TABLE #Decay

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall SSChampion Points: 13623 More actions · Answer 4

Well, okay, I've done it now (I couldn't resist! ). You can pay me next time

--Create run table

CREATE TABLE #Run (Id INT, Decay FLOAT)

INSERT INTO #Run

SELECT 1, 0.20629947401589999

UNION SELECT 2, 0.10910128185966

UNION SELECT 3, 0.12944943670387499

UNION SELECT 4, 0.20629947401589999

UNION SELECT 5, 0.29289321881345198

--Create amount table

CREATE TABLE #Amount (RunId INT, WeekId INT, Amount FLOAT)

INSERT INTO #Amount

SELECT 1, 1, 0

UNION SELECT 1, 2, 0

UNION SELECT 1, 3, 0

UNION SELECT 1, 4, 0

UNION SELECT 1, 5, 0

UNION SELECT 1, 6, 0

UNION SELECT 1, 7, 0

UNION SELECT 1, 8, 0

UNION SELECT 1, 9, 0

UNION SELECT 1, 10, 0

UNION SELECT 1, 11, 0

UNION SELECT 1, 12, 0

UNION SELECT 1, 13, 0

UNION SELECT 1, 14, 0

UNION SELECT 1, 15, 0

UNION SELECT 1, 16, 0

UNION SELECT 1, 17, 0

UNION SELECT 1, 18, 0

UNION SELECT 1, 19, 16.404253

UNION SELECT 1, 20, 30.8047551287026

UNION SELECT 1, 21, 58.7821879999998

UNION SELECT 1, 22, 84.6029029999998

UNION SELECT 1, 23, 86.22413

UNION SELECT 1, 24, 33.2709479999998

UNION SELECT 1, 25, 59.314047

UNION SELECT 1, 26, 73.1534039999999

UNION SELECT 1, 27, 46.1834920173646

UNION SELECT 1, 28, 60.7420854065372

UNION SELECT 1, 29, 59.6350256925434

UNION SELECT 1, 30, 44.6962553626148

UNION SELECT 1, 31, 3.116

UNION SELECT 1, 32, 51.810039

UNION SELECT 1, 33, 58.8992289999999

UNION SELECT 1, 34, 49.5894899999998

UNION SELECT 1, 35, 1.77599999999999

UNION SELECT 1, 36, 34.8840629999999

UNION SELECT 1, 37, 56.3441239999999

UNION SELECT 1, 38, 29.7267979999999

UNION SELECT 1, 39, 48.6844019999998

UNION SELECT 1, 40, 1.476

UNION SELECT 1, 41, 28.8740689999999

UNION SELECT 1, 42, 42.7063359999999

UNION SELECT 1, 43, 47.607978

UNION SELECT 1, 44, 7.84102145045964

UNION SELECT 1, 45, 41.069989

UNION SELECT 1, 46, 33.6987297297296

UNION SELECT 1, 47, 43.2552032686414

UNION SELECT 1, 48, 4.87099999999998

UNION SELECT 1, 49, 35.237639

UNION SELECT 1, 50, 59.3337519999998

UNION SELECT 1, 51, 63.854357

UNION SELECT 1, 52, 2.98099999999999

UNION SELECT 2, 1, 64.9329539999999

UNION SELECT 2, 2, 50.406813

UNION SELECT 2, 3, 65.8291499999998

UNION SELECT 2, 4, 55.0250999999999

UNION SELECT 2, 5, 54.025348

UNION SELECT 2, 6, 49.932537

UNION SELECT 2, 7, 47.358223

UNION SELECT 2, 8, 70.3570519999998

UNION SELECT 2, 9, 53.0393669999998

UNION SELECT 2, 10, 48.517076

UNION SELECT 2, 11, 45.7321539999999

UNION SELECT 2, 12, 45.1575629999998

UNION SELECT 2, 13, 69.376356

UNION SELECT 2, 14, 76.154227

UNION SELECT 2, 15, 70.1678629999998

UNION SELECT 2, 16, 74.993817

UNION SELECT 2, 17, 67.868655

UNION SELECT 2, 18, 71.202864

UNION SELECT 2, 19, 45.519561

UNION SELECT 2, 20, 30.9922609999999

UNION SELECT 2, 21, 49.2788139999999

UNION SELECT 2, 22, 25.3638119999999

UNION SELECT 2, 23, 36.086944

UNION SELECT 2, 24, 23.4862329999998

UNION SELECT 2, 25, 9.85597899999999

UNION SELECT 2, 26, 22.31809

UNION SELECT 2, 27, 12.7538269999999

UNION SELECT 2, 28, 23.3121489999998

UNION SELECT 2, 29, 3.7610909090909

UNION SELECT 2, 30, 20.1015720122574

UNION SELECT 2, 31, 0

UNION SELECT 2, 32, 28.1876547497445

UNION SELECT 2, 33, 5.12483554647598

UNION SELECT 2, 34, 24.9191961184881

UNION SELECT 2, 35, 0

UNION SELECT 2, 36, 9.71677630234933

UNION SELECT 2, 37, 2.89831971399387

UNION SELECT 2, 38, 15.002744637385

UNION SELECT 2, 39, 3.07238508682327

UNION SELECT 2, 40, 0

UNION SELECT 2, 41, 9.43047906026557

UNION SELECT 2, 42, 10.3219999999999

UNION SELECT 2, 43, 17.257

UNION SELECT 2, 44, 0

UNION SELECT 2, 45, 9.34999999999999

UNION SELECT 2, 46, 10.7470561797752

UNION SELECT 2, 47, 10.4919999999999

UNION SELECT 2, 48, 0.121

UNION SELECT 2, 49, 14.1859652706842

UNION SELECT 2, 50, 11.1389999999999

UNION SELECT 2, 51, 16.1051215526047

UNION SELECT 2, 52, 0

UNION SELECT 3, 1, 1.719

UNION SELECT 3, 2, 1.193

UNION SELECT 3, 3, 2.63399999999999

UNION SELECT 3, 4, 2.91599999999999

UNION SELECT 3, 5, 0.966999999999998

UNION SELECT 3, 6, 1.95

UNION SELECT 3, 7, 1.377

UNION SELECT 3, 8, 1.16599999999998

UNION SELECT 3, 9, 0.854999999999998

UNION SELECT 3, 10, 1.25699999999999

UNION SELECT 3, 11, 2.189

UNION SELECT 3, 12, 1.602

UNION SELECT 3, 13, 2

UNION SELECT 3, 14, 2.64699999999999

UNION SELECT 3, 15, 1.849

UNION SELECT 3, 16, 1.88399999999998

UNION SELECT 3, 17, 2.42099999999999

UNION SELECT 3, 18, 3.117

UNION SELECT 3, 19, 0.301999999999999

UNION SELECT 3, 20, 0

UNION SELECT 3, 21, 3.75599999999999

UNION SELECT 3, 22, 0.556999999999999

UNION SELECT 3, 23, 0.308

UNION SELECT 3, 24, 0

UNION SELECT 3, 25, 0

UNION SELECT 3, 26, 0.148999999999998

UNION SELECT 3, 27, 0.337

UNION SELECT 3, 28, 0

UNION SELECT 3, 29, 0.183

UNION SELECT 3, 30, 0

UNION SELECT 3, 31, 0.068

UNION SELECT 3, 32, 0

UNION SELECT 3, 33, 0.231999999999999

UNION SELECT 3, 34, 0

UNION SELECT 3, 35, 0

UNION SELECT 3, 36, 0

UNION SELECT 3, 37, 0.078

UNION SELECT 3, 38, 0

UNION SELECT 3, 39, 0.208999999999999

UNION SELECT 3, 40, 0

UNION SELECT 3, 41, 0

UNION SELECT 3, 42, 0.0919999999999999

UNION SELECT 3, 43, 0

UNION SELECT 3, 44, 0

UNION SELECT 3, 45, 0.0539999999999999

UNION SELECT 3, 46, 0

UNION SELECT 3, 47, 0

UNION SELECT 3, 48, 0

UNION SELECT 3, 49, 0

UNION SELECT 3, 50, 0

UNION SELECT 3, 51, 0

UNION SELECT 3, 52, 0

UNION SELECT 4, 1, 0

UNION SELECT 4, 2, 0

UNION SELECT 4, 3, 0

UNION SELECT 4, 4, 0

UNION SELECT 4, 5, 0

UNION SELECT 4, 6, 0

UNION SELECT 4, 7, 0

UNION SELECT 4, 8, 0

UNION SELECT 4, 9, 0

UNION SELECT 4, 10, 0

UNION SELECT 4, 11, 0

UNION SELECT 4, 12, 0

UNION SELECT 4, 13, 0

UNION SELECT 4, 14, 0

UNION SELECT 4, 15, 0

UNION SELECT 4, 16, 0

UNION SELECT 4, 17, 0

UNION SELECT 4, 18, 0

UNION SELECT 4, 19, 0

UNION SELECT 4, 20, 0

UNION SELECT 4, 21, 0

UNION SELECT 4, 22, 0

UNION SELECT 4, 23, 0

UNION SELECT 4, 24, 0

UNION SELECT 4, 25, 0

UNION SELECT 4, 26, 0

UNION SELECT 4, 27, 0

UNION SELECT 4, 28, 0

UNION SELECT 4, 29, 0

UNION SELECT 4, 30, 0

UNION SELECT 4, 31, 0

UNION SELECT 4, 32, 0

UNION SELECT 4, 33, 0

UNION SELECT 4, 34, 0

UNION SELECT 4, 35, 0

UNION SELECT 4, 36, 0

UNION SELECT 4, 37, 0

UNION SELECT 4, 38, 0

UNION SELECT 4, 39, 0

UNION SELECT 4, 40, 0

UNION SELECT 4, 41, 0

UNION SELECT 4, 42, 0

UNION SELECT 4, 43, 0

UNION SELECT 4, 44, 0

UNION SELECT 4, 45, 0

UNION SELECT 4, 46, 0

UNION SELECT 4, 47, 0

UNION SELECT 4, 48, 0

UNION SELECT 4, 49, 0

UNION SELECT 4, 50, 0

UNION SELECT 4, 51, 0

UNION SELECT 4, 52, 0

UNION SELECT 5, 1, 0

UNION SELECT 5, 2, 0

UNION SELECT 5, 3, 0

UNION SELECT 5, 4, 0

UNION SELECT 5, 5, 0

UNION SELECT 5, 6, 0

UNION SELECT 5, 7, 0

UNION SELECT 5, 8, 0

UNION SELECT 5, 9, 0

UNION SELECT 5, 10, 0

UNION SELECT 5, 11, 0

UNION SELECT 5, 12, 0

UNION SELECT 5, 13, 0

UNION SELECT 5, 14, 0

UNION SELECT 5, 15, 0

UNION SELECT 5, 16, 0

UNION SELECT 5, 17, 0

UNION SELECT 5, 18, 0

UNION SELECT 5, 19, 0

UNION SELECT 5, 20, 0

UNION SELECT 5, 21, 0

UNION SELECT 5, 22, 0

UNION SELECT 5, 23, 0

UNION SELECT 5, 24, 0

UNION SELECT 5, 25, 0

UNION SELECT 5, 26, 0

UNION SELECT 5, 27, 0

UNION SELECT 5, 28, 0

UNION SELECT 5, 29, 0

UNION SELECT 5, 30, 0

UNION SELECT 5, 31, 0

UNION SELECT 5, 32, 0

UNION SELECT 5, 33, 0

UNION SELECT 5, 34, 0

UNION SELECT 5, 35, 0

UNION SELECT 5, 36, 0

UNION SELECT 5, 37, 0

UNION SELECT 5, 38, 0

UNION SELECT 5, 39, 0

UNION SELECT 5, 40, 0

UNION SELECT 5, 41, 0

UNION SELECT 5, 42, 0

UNION SELECT 5, 43, 0

UNION SELECT 5, 44, 0

UNION SELECT 5, 45, 0

UNION SELECT 5, 46, 0

UNION SELECT 5, 47, 0

UNION SELECT 5, 48, 0

UNION SELECT 5, 49, 0

UNION SELECT 5, 50, 0

UNION SELECT 5, 51, 0

UNION SELECT 5, 52, 0

--Create run decay table (using a simple numbers table)

CREATE TABLE #RunDecay (RunId INT, WeekId INT, Decay FLOAT)

DECLARE @Numbers_0_to_51 TABLE (i TINYINT identity(0,1), j BIT)

INSERT INTO @Numbers_0_to_51 SELECT TOP 52 NULL FROM master.dbo.syscolumns

INSERT INTO #RunDecay

SELECT Id, i, Decay * POWER(1-Decay, i) FROM #Run CROSS JOIN @Numbers_0_to_51 ORDER BY Id, i

--Join run decay and amount tables together and group to get result

SELECT a.RunId, d.WeekId + a.WeekId AS WeekId, SUM(Amount * Decay) AS Result

FROM #RunDecay d CROSS JOIN #Amount a

WHERE a.RunId = d.RunId

GROUP BY a.RunId, d.WeekId + a.WeekId

ORDER BY a.RunId, d.WeekId + a.WeekId

--Tidy up

DROP TABLE #Run

DROP TABLE #Amount

DROP TABLE #RunDecay

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.