how to create column based on other column in sql?

Question

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

Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using. Why should I tag my DBMS — user330315
– user330315, Commented Mar 17, 2020 at 12:04
@Mohekar . . . How is 2018 marked as "P" for id = 2? That does not follow your rules. — Gordon Linoff
– Gordon Linoff, Commented Mar 17, 2020 at 12:06

Suresh Gajera · Accepted Answer · 2020-03-17 12:33:32Z

2

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

answered Mar 17, 2020 at 12:33

Suresh Gajera

3621 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-03-17 12:06:38Z

Hmmm . . . I'm thinking to generate flags for each year and then apply the logic. Based on the rules you describe:

select t.*,
       (case when year in (2017, 2018) and
                  flag_2017 > 0 and flag_2018 > 0
             then 'P'
             when year in (2017) and
                  flag_2018 > 0 and flag_2019 > 0
             then 'P'
             else 'N'
        end) as mark
from (select t.*,
             sum(case when year = 2017 then 1 else 0 end) over (partition by id) as flag_2017,
             sum(case when year = 2018 then 1 else 0 end) over (partition by id) as flag_2018,
             sum(case when year = 2019 then 1 else 0 end) over (partition by id) as flag_2019
      from t
     ) t;

Your sample results don't seem to follow your rules, but some simple variation on this appears to be what you want.

wildplasser · Accepted Answer · 2020-03-17 13:25:17Z

You don't need a physical column. What you want can be expressed as a query, using exists() or lead()

the LEAD() version:

\i tmp.sql

CREATE TABLE years(id integer, zyear integer);

INSERT INTO years (id , zyear ) VALUES
 (1, 2017) , (1, 2018) , (1, 2019)
, (2, 2018) , (2, 2019) , (3, 2017)
, (3, 2019) , (8, 2017)
, (4, 2018) , (4, 2019)
        ;


SELECT id, zyear
        , CASE when yy.nxt=yy.zyear+1 THEN 'P' ELSE 'N' END AS flagged
        FROM (
        SELECT id, zyear
        , lead(zyear) OVER (partition by id ORDER BY zyear) AS nxt
        FROM years
        ) yy
        ;

or, the EXISTS()-version:

SELECT id, zyear
        , CASE when yy.xx THEN 'P' ELSE 'N' END AS flagged
        FROM (
        SELECT id, zyear
        , EXISTS ( select * FROM years x where x.id=y.id and x.zyear = y.zyear+1) AS xx
        FROM years y
        ) yy
        ;

Result: (the same for both versions)

psql:tmp.sql:2: NOTICE:  drop cascades to table tmp.years
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 10
 id | zyear | flagged 
----+-------+---------
  1 |  2017 | P
  1 |  2018 | P
  1 |  2019 | N
  2 |  2018 | P
  2 |  2019 | N
  3 |  2017 | N
  3 |  2019 | N
  4 |  2018 | P
  4 |  2019 | N
  8 |  2017 | N
(10 rows)

Collectives™ on Stack Overflow

how to create column based on other column in sql?

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related