Getting Consecutive Dates SQL in Vertica

[msalam65]

Hi,

Table Data
ID Date Flag Step_Name
1 20101230 1 DATA
1 20101231 0 NODATA
1 20110101 0 NODATA
1 20110102 1 NODATA
1 20110103 1 NODATA
1 20110104 1 NODATA
1 20110105 1 DATA

I need to write a sql that will check if the Step_name column is 'NODATA' and flag is 0 for the 1st date. The 0 flag should then be copied over to next consecutive dates until it reaches a record where the Step_Name = 'DATA'. So the output of the above table should be as follow :

ID Date Flag Step_Name
1 20101230 1 DATA
1 20101231 0 NODATA
1 20110101 0 NODATA
1 20110102 0 NODATA
1 20110103 0 NODATA
1 20110104 0 NODATA
1 20110105 1 DATA

Please advise on this sql.

Thanks....

[JimKnicely]

Hi,

Code: Select all

dbadmin=> select id, date, flag, step_name
dbadmin->   from m
dbadmin->  order by id, date;
 id |   date   | flag | step_name
----+----------+------+-----------
  1 | 20101230 |    1 | DATA
  1 | 20101231 |    0 | NODATA
  1 | 20110101 |    0 | NODATA
  1 | 20110102 |    1 | NODATA
  1 | 20110103 |    1 | NODATA
  1 | 20110104 |    1 | NODATA
  1 | 20110105 |    1 | DATA
(7 rows)

Code: Select all

dbadmin=> select id, date, decode(step_name, 'DATA', 1, 0) flag, step_name
dbadmin->   from m
dbadmin->  order by id, date;
 id |   date   | flag | step_name
----+----------+------+-----------
  1 | 20101230 |    1 | DATA
  1 | 20101231 |    0 | NODATA
  1 | 20110101 |    0 | NODATA
  1 | 20110102 |    0 | NODATA
  1 | 20110103 |    0 | NODATA
  1 | 20110104 |    0 | NODATA
  1 | 20110105 |    1 | DATA
(7 rows)

But that seems to easy. What do you mean by first date?