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....
Getting Consecutive Dates SQL in Vertica
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Getting Consecutive Dates SQL in Vertica
Hi,
But that seems to easy. What do you mean by first date?
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.