how to create column based on other column in sql?

Mohekar Published at Dev

83

Mohekar

I need to create column based on id and year column:

if a id present in 2017 and 2018 (subsequent year) then mark 'P' against 2017.
if a id present in 2018 and 2019 then mark 'P' then mark 'P' against 2017.
if a id present in 2017 but not in subsequent year then mark 'N' against 2017
If there is no data of subsequent year then mark 'N' in the previous year (2019)

output :

id  year  mark
1   2017  P
1   2018  P
1   2019  N
2   2018  P
2   2019  N
3   2017  N
3   2019  N
8   2017  P
4   2018  P
4   2019  N

Suresh Gajera

You can try Lead() function. but please check output for Id = 8. Ideally it should be 'N'

SELECT *
    ,CASE WHEN LEAD(Year) OVER (PARTITION BY ID ORDER BY YEAR) - YEAR = 1 THEN 'P' ELSE 'N' END
FROM #Table

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-13

Comments

0 comments

Login to comment

Related

SQL how create a Boolean column based on the value of another column

Pandas - create total column based on other column

How to create a calculated column in DAX, based on one column values and on a specific date of other column?

pandas how to create a boolean column based on other boolean columns in the df

How to create rank column in Python based on other columns

How to get the incremented value in a column based on the other column in sql

SQL return column based on other column

How to create a new column based on other columns with if conditions in r

How to create a column based on two conditions from other data frame?

How to create new column in DataFrame based on other columns in Python Pandas?

R: How to create new variable based on name of other column

Create Column in sql based on condition?

Create column based on other column but with conditions

How to create column based on conditions on other rows - Pandas Dataframe?

create new column based on other column but stripping

SQL create new column based on two other columns

How to create new pandas column based on other column of data frame?

How to create a new column based on calculations made in other columns in PySpark

How to create a logic in pandas to create a new column based on existing values in other column?

How to create a column based on the value of an element of an array indicated by the other column?

Pandas, create 'order' column based on other column

How to create new column based on values after "/" in other column in Oracle?

How to create binary column based on starts character in other column in Oracle SQL Developer?

How create new column in Spark using Python, based on other column?

Oracle SQL create unique INDEX constraint based on status column and other 4 column

SQL: how to pick a value based on other column in an aggregated column

How to create a new column in a DataFrame based on values of two other columns

how to create and fill a new column based on conditions in two other columns?

How to create a column based on the value of the other columns

TOP Ranking

Article

HotTag

Archive