CODEWITHSUNDEEP
sql
Mohekar
Mohekar

Reputation: 185

how to create column based on other column in sql?

id  year
1   2017
1   2018
1   2019
2   2018
2   2019
3   2017
3   2019
8   2017   
4   2018
4   2019

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

  1. if a id present in 2017 and 2018 (subsequent year) then mark 'P' against 2017.
  2. if a id present in 2018 and 2019 then mark 'P' then mark 'P' against 2017.
  3. if a id present in 2017 but not in subsequent year then mark 'N' against 2017
  4. 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

Upvotes: 2

Views: 258

Answers (3)

wildplasser
wildplasser

Reputation: 44250

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)

Upvotes: 0

Suresh Gajera
Suresh Gajera

Reputation: 362

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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.

Upvotes: 0

Related Questions