SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to create a rolling total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
545 Posts

Posted - 05/24/2013 :  03:47:35  Show Profile  Reply with Quote
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 column

so when a new year starts the rolling column starts again.

Year Days count Value Rolling
2005 -30 111 779
2005 -29 11 68
2005 -28 67 488
2005 -27 60 414
2005 -26 34 216
2005 -25 59 444
2005 -24 57 444
2005 -23 259 702
2005 -22 5 40
2005 -21 43 346
2005 -20 6 44
2005 -19 6 46
2005 -18 21 168
2005 -17 44 350
2005 -16 62 390
2005 -15 137 872
2005 -14 69 476
2005 -13 328 2348
2005 -12 165 658
2005 -11 414 2589
2005 -10 720 2681
2005 -9 286 1814
2005 -8 1044 6105
2005 -7 462 2812
2005 -6 444 2673
2005 -5 372 2434
2005 -4 1684 14289
2005 -3 615 3382
2005 -2 2054 18442
2005 -1 801 4953
2005 0 677 3504
2006 -30 137 520
2006 -29 52 460
2006 -28 83 750
2006 -27 65 180
2006 -26 32 295
2006 -25 132 865
2006 -24 66 600
2006 -23 82 620
2006 -22 130 1035
2006 -21 374 2466
2006 -20 179 319
2006 -19 160 1535
2006 -18 152 1400
2006 -17 108 565
2006 -16 278 1240
2006 -15 112 1000
2006 -14 377 2560
2006 -13 385 3653
2006 -12 205 1290
2006 -11 122 755
2006 -10 107 785
2006 -9 174 1560
2006 -8 282 2230
2006 -7 335 2455
2006 -6 238 1639
2006 -5 1112 11527
2006 -4 562 4603
2006 -3 742 6140
2006 -2 444 3415
2006 -1 973 6498
2006 0 539 4151
2007 -30 77 1387
2007 -29 174 1629
2007 -28 188 1825
2007 -27 65 635
2007 -26 125 1299
2007 -25 61 540
2007 -24 109 835
2007 -23 137 1435
2007 -22 206 1933
2007 -21 261 1391
2007 -20 377 15286
2007 -19 463 2623
2007 -18 175 1609
2007 -17 330 4487
2007 -16 234 1912
2007 -15 690 27486
2007 -14 383 5960
2007 -13 172 2119
2007 -12 228 2169
2007 -11 304 2433
2007 -10 140 1133
2007 -9 301 4433
2007 -8 706 7474
2007 -7 1524 15589
2007 -6 944 10951
2007 -5 904 7456
2007 -4 688 6713
2007 -3 571 5554
2007 -2 1311 15856
2007 -1 930 12249
2007 0 1247 16779
2008 -30 64 634
2008 -29 84 801
2008 -28 217 1391
2008 -27 94 969
2008 -26 72 595
2008 -25 146 1742
2008 -24 129 1290
2008 -23 200 2020
2008 -22 286 1225
2008 -21 179 1832
2008 -20 135 899
2008 -19 294 1736
2008 -18 214 1845
2008 -17 239 2250
2008 -16 375 2635
2008 -15 359 2413
2008 -14 500 4794
2008 -13 184 1244
2008 -12 201 1565
2008 -11 334 3158
2008 -10 271 3019
2008 -9 635 3243
2008 -8 532 3756
2008 -7 377 3943
2008 -6 405 2939
2008 -5 281 2507
2008 -4 639 6008
2008 -3 496 4357
2008 -2 742 6425
2008 -1 351 3391
2008 0 304 1817
2009 -30 95 1018
2009 -29 156 1702
2009 -28 222 1234
2009 -27 143 1009
2009 -26 315 1307
2009 -25 266 2293
2009 -24 130 1394
2009 -23 255 1463
2009 -22 156 1589
2009 -21 721 8826
2009 -20 78 933
2009 -19 69 921.5
2009 -18 495 7298
2009 -17 433 6037
2009 -16 257 1379
2009 -15 79 955
2009 -14 266 2437
2009 -13 567 8644
2009 -12 150 1358
2009 -11 313 2094
2009 -10 473 3078
2009 -9 177 2025
2009 -8 416 3891
2009 -7 241 2237
2009 -6 850 3635
2009 -5 347 2689
2009 -4 653 5279
2009 -3 530 5001
2009 -2 623 5927
2009 -1 194 1949
2009 0 315 2724
2010 -30 429 1668.54000043869
2010 -29 116 1126.90000009537
2010 -28 206 2822.82999944687
2010 -27 89 1071.78999900818
2010 -26 120 1168.82999992371
2010 -25 156 1446.46999931335
2010 -24 289 1560.88999986649
2010 -23 120 1500.29999923706
2010 -22 204 2101.47999954224
2010 -21 135 1719.65999984741
2010 -20 228 1047.86999940872
2010 -19 108 842
2010 -18 195 1834.14999961853
2010 -17 329 1826.30999946594
2010 -16 251 1455.8799996376
2010 -15 261 1275.48999977112
2010 -14 209 2206.30999946594
2010 -13 88 1174.64999961853
2010 -12 169 1542.63999938965
2010 -11 202 2484
2010 -10 523 6373.38999986649
2010 -9 290 3601.63999938965
2010 -8 508 4970.55999994278
2010 -7 651 7346
2010 -6 464 4929.48999977112
2010 -5 228 2071.64999961853
2010 -4 904 5171.48999977112
2010 -3 1091 13956
2010 -2 1741 20936.4899997711
2010 -1 1763 29221.8199996948
2010 0 628 3152.82999992371
2011 -30 341 3725
2011 -29 232 4234
2011 -28 159 2601
2011 -27 93 1629
2011 -26 349 1601
2011 -25 330 3100
2011 -24 444 2178
2011 -23 303 3389
2011 -22 244 2708
2011 -21 112 1415
2011 -20 126 965
2011 -19 179 1622
2011 -18 322 4456
2011 -17 223 1805
2011 -16 230 2531
2011 -15 289 1990
2011 -14 414 2901
2011 -13 225 2112
2011 -12 281 1864
2011 -11 358 2945
2011 -10 345 2987
2011 -9 515 4371
2011 -8 961 14516
2011 -7 737 9706
2011 -6 358 3773
2011 -5 745 8913.00000095367
2011 -4 1551 19192
2011 -3 1055 10989
2011 -2 1317 13348.0000009537
2011 -1 1044 10802
2011 0 513 5653
2012 -30 11 172
2012 -29 137 594
2012 -28 109 624
2012 -27 105 1420
2012 -26 253 3323
2012 -25 86 676
2012 -24 206 1198
2012 -23 77 966
2012 -22 91 585
2012 -21 594 4650
2012 -20 112 1032
2012 -19 116 284
2012 -18 48 379
2012 -17 208 2234
2012 -16 267 1699
2012 -15 325 1712
2012 -14 178 1467
2012 -13 310 1310
2012 -12 157 1374
2012 -11 268 1067
2012 -10 224 1050
2012 -9 212 1332
2012 -8 256 2115
2012 -7 233 2068
2012 -6 200 1592
2012 -5 116 1057
2012 -4 63 425
2012 -3 429 2072
2012 -2 285 1745
2012 -1 463 3597
2012 0 428 180

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 05/24/2013 :  05:56:38  Show Profile  Reply with Quote
What is the logic behind rolling column?
what should be the rolling column data? (may be running total based on year ??)

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 05/24/2013 :  06:11:42  Show Profile  Reply with Quote
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 all
SELECT 2005, 29 ,11 ,68 union all
SELECT 2005, 28 ,67 ,488 union all
SELECT 2005, 27 ,60 ,414 union all
SELECT 2005, 26, 34 ,216 union all
SELECT 2005, 25, 59 ,444 union all
SELECT 2005, 24, 57, 444 union all
SELECT 2005, 23, 259, 702 union all
SELECT 2005, 22, 5 ,40 union all
SELECT 2005, 21, 43, 346 union all
SELECT 2005, 20, 6 ,44 union all
SELECT 2005, 19, 6 ,46 union all
SELECT 2005, 18, 21, 168 union all
SELECT 2005, 17, 44, 350 union all
SELECT 2005, 16, 62, 390 union all
SELECT 2005, 15, 137, 872 union all
SELECT 2005, 14, 69 ,476 union all
SELECT 2005, 13, 328, 2348 union all
SELECT 2005, 12, 165, 658 union all
SELECT 2005, 11 ,414, 2589 union all
SELECT 2005, 10, 720, 2681 union all
SELECT 2005, 9, 286 ,1814 union all
SELECT 2005, 8, 1044, 6105 union all
SELECT 2005, 7, 462 ,2812 union all
SELECT 2005, 6, 444 ,2673 union all
SELECT 2005, 5, 372, 2434 union all
SELECT 2005, 4, 1684, 14289 union all
SELECT 2005, 3, 615 ,3382 union all
SELECT 2005, 2, 2054, 18442 union all
SELECT 2005, 1 ,801 ,4953 union all
SELECT 2005, 0, 677, 3504 union all
SELECT 2006, 30, 137, 520 union all
SELECT 2006, 29, 52 ,460 union all
SELECT 2006, 28, 83, 750 union all
SELECT 2006, 27, 65, 180 union all
SELECT 2006, 26, 32, 295

--SELECT * FROM @RollingTotal
SELECT
	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 o
ORDER BY Year, Days;


Refer this link for alternate approaches
http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000