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