0

I have a simple query:

Select
fiscalweek, 4171
From table

And I get results in order of fiscal week:

FiscalWeek  4171
1           691
2           746
3           650
4           625
5           788
6           581
7           597
8           772
9           777

I want in a third column, the sum of the previous 6 fiscal weeks, or for week 2 just the sum of weeks 1 & 2,

so the out put would be:

FiscalWeek  4171    Sum
1           691     691
2           746     1437
3           650     2087
4           625     2712
5           788     7671
6           581     4081
7           597     3393
8           772     4013
9           777     4140

I have tried using rows unbounded preceeding but this was not available on 2008, any suggestions ?

PeterH
  • 975
  • 2
  • 14
  • 36

1 Answers1

2

The easy way, for this simple case, is a self table non equijoin. Lets supose t is your table and a is FiscalWeek:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table t ( a int, t int);
insert into t values 
(1,10),
(2,11),
(3,12),
(4,13),
(5,14),
(6,15),
(7,16),
(8,17),
(9,18),
(10,19),
(11,20),
(12,10);

Query 1:

select t1.a, t1.t, sum(t2.t)
from t t1
inner join t t2 
  on t2.a between t1.a-6 and t1.a --self non equijoin taking 6 prev.
group by t1.a, t1.t
order by t1.a

Results:

|  a |  t |     |
|----|----|-----|
|  1 | 10 |  10 |
|  2 | 11 |  21 |
|  3 | 12 |  33 |
|  4 | 13 |  46 |
|  5 | 14 |  60 |
|  6 | 15 |  75 |
|  7 | 16 |  91 |
|  8 | 17 |  98 |
|  9 | 18 | 105 |
| 10 | 19 | 112 |
| 11 | 20 | 119 |
| 12 | 10 | 115 |

The hard approach, may be more sql XXI century friendly or not, is to deal with CTE, partitions and ranks. Search about it if you are interesting.

dani herrera
  • 48,760
  • 8
  • 117
  • 177
  • Never saw a join with a between in the on clause "on t2.a between t1.a-6 and t1.a ", thats awesome & works a treat Thank You – PeterH Dec 18 '17 at 10:40
  • I've seen things you people wouldn't believe. Attack ships on fire off the shoulder of Orion. I watched C-beams glitter in the dark near the Tannhäuser Gate. Subrogate subqueries. Fake equijoins. Non first normal form databases. All those moments will be lost in time, like tears in rain. Time to die. – dani herrera Dec 19 '17 at 13:07