Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2013-05-24 : 03:47:35
|
| Good morning I want to create a rolling total with the example below.I would like to create the rolling column based on the year columnso when a new year starts the rolling column starts again.Year Days count Value Rolling2005 -30 111 7792005 -29 11 682005 -28 67 4882005 -27 60 4142005 -26 34 2162005 -25 59 4442005 -24 57 4442005 -23 259 7022005 -22 5 402005 -21 43 3462005 -20 6 442005 -19 6 462005 -18 21 1682005 -17 44 3502005 -16 62 3902005 -15 137 8722005 -14 69 4762005 -13 328 23482005 -12 165 6582005 -11 414 25892005 -10 720 26812005 -9 286 18142005 -8 1044 61052005 -7 462 28122005 -6 444 26732005 -5 372 24342005 -4 1684 142892005 -3 615 33822005 -2 2054 184422005 -1 801 49532005 0 677 35042006 -30 137 5202006 -29 52 4602006 -28 83 7502006 -27 65 1802006 -26 32 2952006 -25 132 8652006 -24 66 6002006 -23 82 6202006 -22 130 10352006 -21 374 24662006 -20 179 3192006 -19 160 15352006 -18 152 14002006 -17 108 5652006 -16 278 12402006 -15 112 10002006 -14 377 25602006 -13 385 36532006 -12 205 12902006 -11 122 7552006 -10 107 7852006 -9 174 15602006 -8 282 22302006 -7 335 24552006 -6 238 16392006 -5 1112 115272006 -4 562 46032006 -3 742 61402006 -2 444 34152006 -1 973 64982006 0 539 41512007 -30 77 13872007 -29 174 16292007 -28 188 18252007 -27 65 6352007 -26 125 12992007 -25 61 5402007 -24 109 8352007 -23 137 14352007 -22 206 19332007 -21 261 13912007 -20 377 152862007 -19 463 26232007 -18 175 16092007 -17 330 44872007 -16 234 19122007 -15 690 274862007 -14 383 59602007 -13 172 21192007 -12 228 21692007 -11 304 24332007 -10 140 11332007 -9 301 44332007 -8 706 74742007 -7 1524 155892007 -6 944 109512007 -5 904 74562007 -4 688 67132007 -3 571 55542007 -2 1311 158562007 -1 930 122492007 0 1247 167792008 -30 64 6342008 -29 84 8012008 -28 217 13912008 -27 94 9692008 -26 72 5952008 -25 146 17422008 -24 129 12902008 -23 200 20202008 -22 286 12252008 -21 179 18322008 -20 135 8992008 -19 294 17362008 -18 214 18452008 -17 239 22502008 -16 375 26352008 -15 359 24132008 -14 500 47942008 -13 184 12442008 -12 201 15652008 -11 334 31582008 -10 271 30192008 -9 635 32432008 -8 532 37562008 -7 377 39432008 -6 405 29392008 -5 281 25072008 -4 639 60082008 -3 496 43572008 -2 742 64252008 -1 351 33912008 0 304 18172009 -30 95 10182009 -29 156 17022009 -28 222 12342009 -27 143 10092009 -26 315 13072009 -25 266 22932009 -24 130 13942009 -23 255 14632009 -22 156 15892009 -21 721 88262009 -20 78 9332009 -19 69 921.52009 -18 495 72982009 -17 433 60372009 -16 257 13792009 -15 79 9552009 -14 266 24372009 -13 567 86442009 -12 150 13582009 -11 313 20942009 -10 473 30782009 -9 177 20252009 -8 416 38912009 -7 241 22372009 -6 850 36352009 -5 347 26892009 -4 653 52792009 -3 530 50012009 -2 623 59272009 -1 194 19492009 0 315 27242010 -30 429 1668.540000438692010 -29 116 1126.900000095372010 -28 206 2822.829999446872010 -27 89 1071.789999008182010 -26 120 1168.829999923712010 -25 156 1446.469999313352010 -24 289 1560.889999866492010 -23 120 1500.299999237062010 -22 204 2101.479999542242010 -21 135 1719.659999847412010 -20 228 1047.869999408722010 -19 108 8422010 -18 195 1834.149999618532010 -17 329 1826.309999465942010 -16 251 1455.87999963762010 -15 261 1275.489999771122010 -14 209 2206.309999465942010 -13 88 1174.649999618532010 -12 169 1542.639999389652010 -11 202 24842010 -10 523 6373.389999866492010 -9 290 3601.639999389652010 -8 508 4970.559999942782010 -7 651 73462010 -6 464 4929.489999771122010 -5 228 2071.649999618532010 -4 904 5171.489999771122010 -3 1091 139562010 -2 1741 20936.48999977112010 -1 1763 29221.81999969482010 0 628 3152.829999923712011 -30 341 37252011 -29 232 42342011 -28 159 26012011 -27 93 16292011 -26 349 16012011 -25 330 31002011 -24 444 21782011 -23 303 33892011 -22 244 27082011 -21 112 14152011 -20 126 9652011 -19 179 16222011 -18 322 44562011 -17 223 18052011 -16 230 25312011 -15 289 19902011 -14 414 29012011 -13 225 21122011 -12 281 18642011 -11 358 29452011 -10 345 29872011 -9 515 43712011 -8 961 145162011 -7 737 97062011 -6 358 37732011 -5 745 8913.000000953672011 -4 1551 191922011 -3 1055 109892011 -2 1317 13348.00000095372011 -1 1044 108022011 0 513 56532012 -30 11 1722012 -29 137 5942012 -28 109 6242012 -27 105 14202012 -26 253 33232012 -25 86 6762012 -24 206 11982012 -23 77 9662012 -22 91 5852012 -21 594 46502012 -20 112 10322012 -19 116 2842012 -18 48 3792012 -17 208 22342012 -16 267 16992012 -15 325 17122012 -14 178 14672012 -13 310 13102012 -12 157 13742012 -11 268 10672012 -10 224 10502012 -9 212 13322012 -8 256 21152012 -7 233 20682012 -6 200 15922012 -5 116 10572012 -4 63 4252012 -3 429 20722012 -2 285 17452012 -1 463 35972012 0 428 180 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-24 : 05:56:38
|
| What is the logic behind rolling column? what should be the rolling column data? (may be running total based on year ??)--Chandu |
 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-05-24 : 06:11:42
|
| [code]If you want Year-wise Running Total see the below query...DECLARE @RollingTotal TABLE(Year INT, Days INT, cunt INT, Value DEC(15,4)) --, Rolling DEC(30,2))INSERT INTO @RollingTotal SELECT 2005, 30 ,111, 779 union allSELECT 2005, 29 ,11 ,68 union allSELECT 2005, 28 ,67 ,488 union allSELECT 2005, 27 ,60 ,414 union allSELECT 2005, 26, 34 ,216 union allSELECT 2005, 25, 59 ,444 union allSELECT 2005, 24, 57, 444 union allSELECT 2005, 23, 259, 702 union allSELECT 2005, 22, 5 ,40 union allSELECT 2005, 21, 43, 346 union allSELECT 2005, 20, 6 ,44 union allSELECT 2005, 19, 6 ,46 union allSELECT 2005, 18, 21, 168 union allSELECT 2005, 17, 44, 350 union allSELECT 2005, 16, 62, 390 union allSELECT 2005, 15, 137, 872 union allSELECT 2005, 14, 69 ,476 union allSELECT 2005, 13, 328, 2348 union allSELECT 2005, 12, 165, 658 union allSELECT 2005, 11 ,414, 2589 union allSELECT 2005, 10, 720, 2681 union allSELECT 2005, 9, 286 ,1814 union allSELECT 2005, 8, 1044, 6105 union allSELECT 2005, 7, 462 ,2812 union allSELECT 2005, 6, 444 ,2673 union allSELECT 2005, 5, 372, 2434 union allSELECT 2005, 4, 1684, 14289 union allSELECT 2005, 3, 615 ,3382 union allSELECT 2005, 2, 2054, 18442 union allSELECT 2005, 1 ,801 ,4953 union allSELECT 2005, 0, 677, 3504 union allSELECT 2006, 30, 137, 520 union allSELECT 2006, 29, 52 ,460 union allSELECT 2006, 28, 83, 750 union allSELECT 2006, 27, 65, 180 union allSELECT 2006, 26, 32, 295--SELECT * FROM @RollingTotalSELECT Year, Days, Value, RunningTotal = Value + COALESCE( ( SELECT SUM(Value) FROM @RollingTotal AS s WHERE s.Year = o.Year AND s.Days < o.Days), 0 )FROM @RollingTotal AS oORDER BY Year, Days;[/code]Refer this link for alternate approacheshttp://www.sqlperformance.com/2012/07/t-sql-queries/running-totals--Chandu |
|
|
|
|
|
|
|
|
How to create a rolling total