How to update columns data based on previous rows of a result set
devesh-ahuja
Indent
You don't need group by clause
On from clause, you can use 2 auto join to find next and previous rows using index.
On the select clause you need just adapt case when statement to implement your specific rules like this (not tested, but the idea is here) :
create table RHS as
select
Curr.policy,
case
when prev.EventDate is null then Curr.policyIssueDate
else EventDate
end as StartDate,
case
when next.EventDate is null then CURDATE()
else EventDate
end as EndDate
from
LHS Curr
left join LHS next on
Curr.INDEX+1= next.INDEX and
Curr.policy = next.policy
left join LHS prev on
Curr.INDEX-1= prev.INDEX and
Curr.policy = prev.policy
For today date :
- Mysql : CURDATE()
- SQLServer : getdate()
Note : you can also use lead and lag window function.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
How to update SQL column based on previous search result of the same table
- 2
Inserting extra rows into a result set based on the data length of a text column
- 3
How to Group a table and get results for a row based on the previous rows' data
- 4
How to copy rows of specific columns based on a criteria (and then continuously update)?
- 5
Counting previous rows in a data table based on date
- 6
Update or Insert records in database based on result set
- 7
Update or Insert records in database based on result set
- 8
Finding Rows based on a set of same columns in ORACLE
- 9
How To Update The Date Based On Previous Record Value
- 10
How To Update The Date Based On Previous Record Value
- 11
How to update a MySQL row based on previous row?
- 12
SQL: how to update a row based on a previous row
- 13
PostgreSQL Simple Crosstable (Rows to Columns and Columns to Rows) in a big data set
- 14
PostgreSQL Simple Crosstable (Rows to Columns and Columns to Rows) in a big data set
- 15
Sum rows in data frame based on two columns
- 16
Filtering time series data based on previous result row values in PostgreSQL
- 17
MySQL select rows based on a result between two date columns
- 18
Filter data based on result set of group and count
- 19
Update rest request data based on the previous step response data
- 20
How do we update column data in rows of similar columns with the data found after merge operation?
- 21
How do I remove duplicate rows based on two Columns? (E.g. A Pair Value Set)
- 22
Insert rows from select result set except some columns
- 23
Add rows to a data frame based on date in previous row
- 24
Segmenting a data frame by row based on previous rows values
- 25
How to merge rows in select query result set
- 26
Pandas data frame: adding columns based on previous time periods
- 27
How to remove duplicate rows based on some columns
- 28
How to Select rows based on two columns
- 29
How to match rows based on certain columns in pandas?
Comments