<commit_before>DROP MATERIALIZED VIEW IF EXISTS central_line_durations CASCADE;
CREATE MATERIALIZED VIEW central_line_durations as
with mv as
(
  select
    pe.icustay_id
  , pe.starttime, pe.endtime
    , case
        when (locationcategory <> 'Invasive Arterial' or locationcategory is null)
          then 1
        else 0
      end as central_line
  from procedureevents_mv pe
  where pe.itemid in
  (
      224263 -- Multi Lumen | None | 12 | Processes
    , 224264 -- PICC Line | None | 12 | Processes
    , 224267 -- Cordis/Introducer | None | 12 | Processes
    , 224268 -- Trauma line | None | 12 | Processes
    , 225199 -- Triple Introducer | None | 12 | Processes
    , 225202 -- Indwelling Port (PortaCath) | None | 12 | Processes
    , 225203 -- Pheresis Catheter | None | 12 | Processes
    , 225315 -- Tunneled (Hickman) Line | None | 12 | Processes
    , 225752 -- Arterial Line | None | 12 | Processes
    , 227719 -- AVA Line | None | 12 | Processes
    -- , 228286 -- Intraosseous Device | None | 12 | Processes
    , 224270 -- Dialysis Catheter
  )
)
, cv_grp as
(
  -- group type+site
  select ce.icustay_id, ce.charttime
    , max(case when itemid =  229  then value else null end) as INV1_Type
    , max(case when itemid =  8392 then value else null end) as INV1_Site
    , max(case when itemid =  235  then value else null end) as INV2_Type
    , max(case when itemid =  8393 then value else null end) as INV2_Site
    , max(case when itemid =  241  then value else null end) as INV3_Type
    , max(case when itemid =  8394 then value else null end) as INV3_Site
    , max(case when itemid =  247  then value else null end) as INV4_Type
    , max(case when itemid =  8395 then value else null end) as INV4_Site
    , max(case when itemid =  253  then value else null end) as INV5_Type
    , max(case when itemid =  8396 then value else null end) as INV5_Site
    , max(case when itemid =  259  then value else null end) as INV6_Type
    , max(case when itemid =  8397 then value else null end) as INV6_Site
    , max(case when itemid =  265  then value else null end) as INV7_Type
    , max(case when itemid =  8398 then value else null end) as INV7_Site
    , max(case when itemid =  271  then value else null end) as INV8_Type
    , max(case when itemid =  8399 then value else null end) as INV8_Site
  from chartevents ce
  where ce.itemid in
  (
      229 -- INV Line#1 [Type]
    , 235 -- INV Line#2 [Type]
    , 241 -- INV Line#3 [Type]
    , 247 -- INV Line#4 [Type]
    , 253 -- INV Line#5 [Type]
    , 259 -- INV Line#6 [Type]
    , 265 -- INV Line#7 [Type]
    , 271 -- INV Line#8 [Type]
    , 8392 -- INV Line#1 [Site]
    , 8393 -- INV Line#2 [Site]
    , 8394 -- INV Line#3 [Site]
    , 8395 -- INV Line#4 [Site]
    , 8396 -- INV Line#5 [Site]
    , 8397 -- INV Line#6 [Site]
    , 8398 -- INV Line#7 [Site]
    , 8399 -- INV Line#8 [Site]
  )
  and ce.value is not null
  group by ce.icustay_id, ce.charttime
)
-- types of invasive lines in carevue
--       value       | count
-- ------------------+--------
--  A-Line           | 460627
--  Multi-lumen      | 345858
--  PICC line        |  92285
--  PA line          |  65702
--  Dialysis Line    |  57579
--  Introducer       |  36027
--  CCO PA Line      |  24831
--                   |  22369
--  Trauma Line      |  15530
--  Portacath        |  12927
--  Ventriculostomy  |  10295
--  Pre-Sep Catheter |   9678
--  IABP             |   8819
--  Other/Remarks    |   8725
--  Midline          |   5067
--  Venous Access    |   4278
--  Hickman          |   3783
--  PacerIntroducer  |   2663
--  TripleIntroducer |   2262
--  RIC              |   1625
--  PermaCath        |   1066
--  <NAME>      |    913
--  Lumbar Drain     |    361
-- (23 rows)
, cv as
(
  select distinct icustay_id, charttime
  from cv_grp
  where (inv1_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv2_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv3_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv4_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv5_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv6_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv7_type in ('PICC line', 'Hickman', 'Portacath'))
     OR (inv8_type in ('PICC line', 'Hickman', 'Portacath'))
)
-- transform carevue data into durations
, cv0 as
(
  select
    icustay_id
    -- this carries over the previous charttime
    , LAG(CHARTTIME, 1) OVER (partition by icustay_id order by charttime) as charttime_lag
    , charttime
  from cv
)
, cv1 as
(
  select
    icustay_id
    , charttime
    , charttime_lag
    -- if the current observation indicates a line is present
    -- calculate the time since the last charted line
    , charttime - charttime_lag as central_line_duration
    -- now we determine if the current line is "new"
    -- new == no documentation for 16 hours
    , case
        when (charttime - charttime_lag) > interval '16' hour
          then 1
      else 0
      end as central_line_new
  FROM cv0<commit_msg>Changed logic for central line durations in carevue<commit_after>DROP MATERIALIZED VIEW IF EXISTS central_line_durations CASCADE;
CREATE MATERIALIZED VIEW central_line_durations as
with mv as
(
  select
    pe.icustay_id
  , pe.starttime, pe.endtime
    , case
        when (locationcategory <> 'Invasive Arterial' or locationcategory is null)
          then 1
        else 0
      end as central_line
  from procedureevents_mv pe
  where pe.itemid in
  (
      224263 -- Multi Lumen | None | 12 | Processes
    , 224264 -- PICC Line | None | 12 | Processes
    , 224267 -- Cordis/Introducer | None | 12 | Processes
    , 224268 -- Trauma line | None | 12 | Processes
    , 225199 -- Triple Introducer | None | 12 | Processes
    , 225202 -- Indwelling Port (PortaCath) | None | 12 | Processes
    , 225203 -- Pheresis Catheter | None | 12 | Processes
    , 225315 -- Tunneled (Hickman) Line | None | 12 | Processes
    , 225752 -- Arterial Line | None | 12 | Processes
    , 227719 -- AVA Line | None | 12 | Processes
    -- , 228286 -- Intraosseous Device | None | 12 | Processes
    , 224270 -- Dialysis Catheter
  )
)
, cv_grp as
(
  -- group type+site
  select ce.icustay_id, ce.charttime
    , max(case when itemid =  229  then value else null end) as INV1_Type
    , max(case when itemid =  8392 then value else null end) as INV1_Site
    , max(case when itemid =  235  then value else null end) as INV2_Type
    , max(case when itemid =  8393 then value else null end) as INV2_Site
    , max(case when itemid =  241  then value else null end) as INV3_Type
    , max(case when itemid =  8394 then value else null end) as INV3_Site
    , max(case when itemid =  247  then value else null end) as INV4_Type
    , max(case when itemid =  8395 then value else null end) as INV4_Site
    , max(case when itemid =  253  then value else null end) as INV5_Type
    , max(case when itemid =  8396 then value else null end) as INV5_Site
    , max(case when itemid =  259  then value else null end) as INV6_Type
    , max(case when itemid =  8397 then value else null end) as INV6_Site
    , max(case when itemid =  265  then value else null end) as INV7_Type
    , max(case when itemid =  8398 then value else null end) as INV7_Site
    , max(case when itemid =  271  then value else null end) as INV8_Type
    , max(case when itemid =  8399 then value else null end) as INV8_Site
  from chartevents ce
  where ce.itemid in
  (
      229 -- INV Line#1 [Type]
    , 235 -- INV Line#2 [Type]
    , 241 -- INV Line#3 [Type]
    , 247 -- INV Line#4 [Type]
    , 253 -- INV Line#5 [Type]
    , 259 -- INV Line#6 [Type]
    , 265 -- INV Line#7 [Type]
    , 271 -- INV Line#8 [Type]
    , 8392 -- INV Line#1 [Site]
    , 8393 -- INV Line#2 [Site]
    , 8394 -- INV Line#3 [Site]
    , 8395 -- INV Line#4 [Site]
    , 8396 -- INV Line#5 [Site]
    , 8397 -- INV Line#6 [Site]
    , 8398 -- INV Line#7 [Site]
    , 8399 -- INV Line#8 [Site]
  )
  and ce.value is not null
  group by ce.icustay_id, ce.charttime
)
-- types of invasive lines in carevue
--       value       | count
-- ------------------+--------
--  A-Line           | 460627
--  Multi-lumen      | 345858
--  PICC line        |  92285
--  PA line          |  65702
--  Dialysis Line    |  57579
--  Introducer       |  36027
--  CCO PA Line      |  24831
--                   |  22369
--  Trauma Line      |  15530
--  Portacath        |  12927
--  Ventriculostomy  |  10295
--  Pre-Sep Catheter |   9678
--  IABP             |   8819
--  Other/Remarks    |   8725
--  Midline          |   5067
--  Venous Access    |   4278
--  Hickman          |   3783
--  PacerIntroducer  |   2663
--  TripleIntroducer |   2262
--  RIC              |   1625
--  PermaCath        |   1066
--  <NAME>      |    913
--  Lumbar Drain     |    361
-- (23 rows)
, cv as
(
  select distinct icustay_id, charttime
  from cv_grp
  where (inv1_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv2_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv3_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv4_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv5_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv6_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv7_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
     OR (inv8_type in ('Multi-lumen', 'PICC line', 'Dialysis Line', 'Introducer','Trauma Line', 'Portacath', 'Venous Access', 'Hickman', 'PacerIntroducer', 'TripleIntroducer'))
)
-- transform carevue data into durations
, cv0 as
(
  select
    icustay_id
    -- this carries over the previous charttime
    , LAG(CHARTTIME, 1) OVER (partition by icustay_id order by charttime) as charttime_lag
    , charttime
  from cv
)
, cv1 as
(
  select
    icustay_id
    , charttime
    , charttime_lag
    -- if the current observation indicates a line is present
    -- calculate the time since the last charted line
    , charttime - charttime_lag as central_line_duration
    -- now we determine if the current line is "new"
    -- new == no documentation for 16 hours
    , case
        when (charttime - charttime_lag) > interval '16' hour
          then 1
      else 0
      end as central_line_new
  FROM cv0
{'fraction_non_alphanumeric': 0.11062828173094333, 'fraction_numerical': 0.06572514937533949, 'mean_word_length': 2.4032655576093656, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5386433', 'n_tokens_mistral': 4362, 'n_tokens_neox': 3828, 'n_words': 1254}