SQL, Looking back several months data for a specific row data 1

[proxy00]

Hello,

Could anyone help me on writing a SQL statement to get the following data?

Originally, I have a data set:
Key | Period | rowValue
1 | 2003/01 | 5
2 | 2003/02 | 8
3 | 2003/03 | 20
4 | 2003/04 | 10
5 | 2003/05 | 7
...

And want to have a desired dataset:
Key | Period | rowValue | previous_3_mth_sum
3 | 2003/03 | 20 | 33 (which is 5+8+20)
4 | 2003/04 | 10 | 38 (which is 8+20+10)
5 | 2003/05 | 7 | 37 (which is 7+10+20)
...


Im showing rows for all different periods, but these fields need to be looking back 3 months respective to the specific row, and will therefore not be the same for all rows.
Many Thanks!

 

[PHV]

The basic idea is something like this:
SELECT A.Key, A.Period, A.rowValue,
(SELECT SUM(rowValue) FROM yourTable AS B
WHERE B.Period BETWEEN A.Period - 2 months And A.Period
) AS previous_3_mth_sum
FROM yourTable AS A
WHERE A.Key>=3
The syntax of the following expression A.Period - 2 months depends on your RDBMS.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

this is a theta join, tricky but it can be done

is Period a char or varchar?

rudy

http://r937.com/

SQL Consulting

 

[proxy00]

r937, Period is in Date format.
Thanks!

 

[r937]

PHV, WHERE A.Key>=3 is cheating



rudy

http://r937.com/

SQL Consulting

 

[r937]

hope you saw the smiley ;-)

theta join:

select t1.key
     , t1.period
     , t1.rowValue
     , sum(t2.rowValue) as previous_3_mth_sum
  from yourtable t1
inner
  join yourtable t2
 where year(t1.period)*12+month(t1.period) 
     between 
       year(t2.period)*12+month(t2.period) 
     and 
       year(t2.period)*12+month(t2.period) +2
group
    by t1.key
     , t1.period
     , t1.rowValue

rudy

http://r937.com/

SQL Consulting