lundi 29 juin 2015

How to get calculations from two rows

I started learning SQL recently and would like to know if it is possible to do the calculations as below.

Basically my table looks like this:

id    Date           Fill_nbr     
 1   01/01/2015       30      
 1   02/05/2015       30      
 1   03/02/2015       30      
 1   07/01/2015       30      
 1   07/26/2015       30      
 2   03/01/2015       30      
 ....

And I'd like to create a table like this:

 id  Date             Fill_nbr     Date_last      Gap    First
 1   01/01/2015       30           01/30/2015      0         1
 1   02/05/2015       30           03/04/2015      5         0
 1   03/02/2015       30           03/31/2015      0         0
 1   07/01/2015       30           07/30/2015      91        1
 1   07/26/2015       30           08/24/2015      0         0
 2   03/01/2015       30           03/30/2015      0         1
 ....

The rule for column 'Date_last' is Date_last = Date + fill_nbr which is easy to get.

The difficult part for me is the 'Gap' part. The rules are: * Gap='Date' - last record of "Date_last'. For example, gap for the second row is calculated as Gap=02/05/2015- 01/30/2015; * Gap=0 for everyone's first record or when the calculated gap<0;

The rule for column 'First': * First=1 for everyone's first record OR when gap>60. * Otherwise, First=0;

Any suggestions would be greatly appreciated! Thanks!

Aucun commentaire:

Enregistrer un commentaire