claim_occurrence_wide_view
Materialized: false
Definition
WITH occurrences AS (
SELECT ct.claim_id,
ct.occurrence_code1,
ct.occurrence_code2,
ct.occurrence_code3,
ct.occurrence_code4,
ct.occurrence_code5,
ct.occurrence_code6,
ct.occurrence_code7,
ct.occurrence_code8,
ct.occurrence_code9,
ct.occurrence_code10,
ct.occurrence_code11,
ct.occurrence_code12,
ct.occurrence_code13,
ct.occurrence_code14,
ct.occurrence_code15,
ct.occurrence_code16,
ct.occurrence_code17,
ct.occurrence_code18,
ct.occurrence_code19,
ct.occurrence_code20,
ct.occurrence_code21,
ct.occurrence_code22,
ct.occurrence_code23,
ct.occurrence_code24,
ct.occurrence_code25,
ct.occurrence_code26,
ct.occurrence_code27,
ct.occurrence_code28,
ct.occurrence_code29,
ct.occurrence_code30
FROM public.crosstab('SELECT claim_id, claim_occurrence_index, occurrence_code
FROM claims.claim_occurrence
ORDER BY claim_id, claim_occurrence_index, occurrence_code'::text) ct(claim_id bigint, occurrence_code1 text, occurrence_code2 text, occurrence_code3 text, occurrence_code4 text, occurrence_code5 text, occurrence_code6 text, occurrence_code7 text, occurrence_code8 text, occurrence_code9 text, occurrence_code10 text, occurrence_code11 text, occurrence_code12 text, occurrence_code13 text, occurrence_code14 text, occurrence_code15 text, occurrence_code16 text, occurrence_code17 text, occurrence_code18 text, occurrence_code19 text, occurrence_code20 text, occurrence_code21 text, occurrence_code22 text, occurrence_code23 text, occurrence_code24 text, occurrence_code25 text, occurrence_code26 text, occurrence_code27 text, occurrence_code28 text, occurrence_code29 text, occurrence_code30 text)
), start_dates AS (
SELECT ct.claim_id,
ct.occurrence_start_date1,
ct.occurrence_start_date2,
ct.occurrence_start_date3,
ct.occurrence_start_date4,
ct.occurrence_start_date5,
ct.occurrence_start_date6,
ct.occurrence_start_date7,
ct.occurrence_start_date8,
ct.occurrence_start_date9,
ct.occurrence_start_date10,
ct.occurrence_start_date11,
ct.occurrence_start_date12,
ct.occurrence_start_date13,
ct.occurrence_start_date14,
ct.occurrence_start_date15,
ct.occurrence_start_date16,
ct.occurrence_start_date17,
ct.occurrence_start_date18,
ct.occurrence_start_date19,
ct.occurrence_start_date20,
ct.occurrence_start_date21,
ct.occurrence_start_date22,
ct.occurrence_start_date23,
ct.occurrence_start_date24,
ct.occurrence_start_date25,
ct.occurrence_start_date26,
ct.occurrence_start_date27,
ct.occurrence_start_date28,
ct.occurrence_start_date29,
ct.occurrence_start_date30
FROM public.crosstab('SELECT claim_id, claim_occurrence_index, start_date
FROM claims.claim_occurrence
ORDER BY claim_id, claim_occurrence_index, start_date'::text) ct(claim_id bigint, occurrence_start_date1 date, occurrence_start_date2 date, occurrence_start_date3 date, occurrence_start_date4 date, occurrence_start_date5 date, occurrence_start_date6 date, occurrence_start_date7 date, occurrence_start_date8 date, occurrence_start_date9 date, occurrence_start_date10 date, occurrence_start_date11 date, occurrence_start_date12 date, occurrence_start_date13 date, occurrence_start_date14 date, occurrence_start_date15 date, occurrence_start_date16 date, occurrence_start_date17 date, occurrence_start_date18 date, occurrence_start_date19 date, occurrence_start_date20 date, occurrence_start_date21 date, occurrence_start_date22 date, occurrence_start_date23 date, occurrence_start_date24 date, occurrence_start_date25 date, occurrence_start_date26 date, occurrence_start_date27 date, occurrence_start_date28 date, occurrence_start_date29 date, occurrence_start_date30 date)
), end_dates AS (
SELECT ct.claim_id,
ct.occurrence_end_date1,
ct.occurrence_end_date2,
ct.occurrence_end_date3,
ct.occurrence_end_date4,
ct.occurrence_end_date5,
ct.occurrence_end_date6,
ct.occurrence_end_date7,
ct.occurrence_end_date8,
ct.occurrence_end_date9,
ct.occurrence_end_date10,
ct.occurrence_end_date11,
ct.occurrence_end_date12,
ct.occurrence_end_date13,
ct.occurrence_end_date14,
ct.occurrence_end_date15,
ct.occurrence_end_date16,
ct.occurrence_end_date17,
ct.occurrence_end_date18,
ct.occurrence_end_date19,
ct.occurrence_end_date20,
ct.occurrence_end_date21,
ct.occurrence_end_date22,
ct.occurrence_end_date23,
ct.occurrence_end_date24,
ct.occurrence_end_date25,
ct.occurrence_end_date26,
ct.occurrence_end_date27,
ct.occurrence_end_date28,
ct.occurrence_end_date29,
ct.occurrence_end_date30
FROM public.crosstab('SELECT claim_id, claim_occurrence_index, end_date
FROM claims.claim_occurrence
ORDER BY claim_id, claim_occurrence_index, end_date'::text) ct(claim_id bigint, occurrence_end_date1 date, occurrence_end_date2 date, occurrence_end_date3 date, occurrence_end_date4 date, occurrence_end_date5 date, occurrence_end_date6 date, occurrence_end_date7 date, occurrence_end_date8 date, occurrence_end_date9 date, occurrence_end_date10 date, occurrence_end_date11 date, occurrence_end_date12 date, occurrence_end_date13 date, occurrence_end_date14 date, occurrence_end_date15 date, occurrence_end_date16 date, occurrence_end_date17 date, occurrence_end_date18 date, occurrence_end_date19 date, occurrence_end_date20 date, occurrence_end_date21 date, occurrence_end_date22 date, occurrence_end_date23 date, occurrence_end_date24 date, occurrence_end_date25 date, occurrence_end_date26 date, occurrence_end_date27 date, occurrence_end_date28 date, occurrence_end_date29 date, occurrence_end_date30 date)
), descriptions AS (
SELECT ct.claim_id,
ct.occurrence_description1,
ct.occurrence_description2,
ct.occurrence_description3,
ct.occurrence_description4,
ct.occurrence_description5,
ct.occurrence_description6,
ct.occurrence_description7,
ct.occurrence_description8,
ct.occurrence_description9,
ct.occurrence_description10,
ct.occurrence_description11,
ct.occurrence_description12,
ct.occurrence_description13,
ct.occurrence_description14,
ct.occurrence_description15,
ct.occurrence_description16,
ct.occurrence_description17,
ct.occurrence_description18,
ct.occurrence_description19,
ct.occurrence_description20,
ct.occurrence_description21,
ct.occurrence_description22,
ct.occurrence_description23,
ct.occurrence_description24,
ct.occurrence_description25,
ct.occurrence_description26,
ct.occurrence_description27,
ct.occurrence_description28,
ct.occurrence_description29,
ct.occurrence_description30
FROM public.crosstab('SELECT claim_id, claim_occurrence_index, title
FROM claims.claim_occurrence o
LEFT OUTER JOIN claims.nubc_occurrence_lookup n ON n.occurrence_code = o.occurrence_code
ORDER BY claim_id, claim_occurrence_index, title'::text) ct(claim_id bigint, occurrence_description1 text, occurrence_description2 text, occurrence_description3 text, occurrence_description4 text, occurrence_description5 text, occurrence_description6 text, occurrence_description7 text, occurrence_description8 text, occurrence_description9 text, occurrence_description10 text, occurrence_description11 text, occurrence_description12 text, occurrence_description13 text, occurrence_description14 text, occurrence_description15 text, occurrence_description16 text, occurrence_description17 text, occurrence_description18 text, occurrence_description19 text, occurrence_description20 text, occurrence_description21 text, occurrence_description22 text, occurrence_description23 text, occurrence_description24 text, occurrence_description25 text, occurrence_description26 text, occurrence_description27 text, occurrence_description28 text, occurrence_description29 text, occurrence_description30 text)
)
SELECT occurrences.claim_id,
occurrences.occurrence_code1,
occurrences.occurrence_code2,
occurrences.occurrence_code3,
occurrences.occurrence_code4,
occurrences.occurrence_code5,
occurrences.occurrence_code6,
occurrences.occurrence_code7,
occurrences.occurrence_code8,
occurrences.occurrence_code9,
occurrences.occurrence_code10,
occurrences.occurrence_code11,
occurrences.occurrence_code12,
occurrences.occurrence_code13,
occurrences.occurrence_code14,
occurrences.occurrence_code15,
occurrences.occurrence_code16,
occurrences.occurrence_code17,
occurrences.occurrence_code18,
occurrences.occurrence_code19,
occurrences.occurrence_code20,
occurrences.occurrence_code21,
occurrences.occurrence_code22,
occurrences.occurrence_code23,
occurrences.occurrence_code24,
occurrences.occurrence_code25,
occurrences.occurrence_code26,
occurrences.occurrence_code27,
occurrences.occurrence_code28,
occurrences.occurrence_code29,
occurrences.occurrence_code30,
start_dates.occurrence_start_date1,
start_dates.occurrence_start_date2,
start_dates.occurrence_start_date3,
start_dates.occurrence_start_date4,
start_dates.occurrence_start_date5,
start_dates.occurrence_start_date6,
start_dates.occurrence_start_date7,
start_dates.occurrence_start_date8,
start_dates.occurrence_start_date9,
start_dates.occurrence_start_date10,
start_dates.occurrence_start_date11,
start_dates.occurrence_start_date12,
start_dates.occurrence_start_date13,
start_dates.occurrence_start_date14,
start_dates.occurrence_start_date15,
start_dates.occurrence_start_date16,
start_dates.occurrence_start_date17,
start_dates.occurrence_start_date18,
start_dates.occurrence_start_date19,
start_dates.occurrence_start_date20,
start_dates.occurrence_start_date21,
start_dates.occurrence_start_date22,
start_dates.occurrence_start_date23,
start_dates.occurrence_start_date24,
start_dates.occurrence_start_date25,
start_dates.occurrence_start_date26,
start_dates.occurrence_start_date27,
start_dates.occurrence_start_date28,
start_dates.occurrence_start_date29,
start_dates.occurrence_start_date30,
end_dates.occurrence_end_date1,
end_dates.occurrence_end_date2,
end_dates.occurrence_end_date3,
end_dates.occurrence_end_date4,
end_dates.occurrence_end_date5,
end_dates.occurrence_end_date6,
end_dates.occurrence_end_date7,
end_dates.occurrence_end_date8,
end_dates.occurrence_end_date9,
end_dates.occurrence_end_date10,
end_dates.occurrence_end_date11,
end_dates.occurrence_end_date12,
end_dates.occurrence_end_date13,
end_dates.occurrence_end_date14,
end_dates.occurrence_end_date15,
end_dates.occurrence_end_date16,
end_dates.occurrence_end_date17,
end_dates.occurrence_end_date18,
end_dates.occurrence_end_date19,
end_dates.occurrence_end_date20,
end_dates.occurrence_end_date21,
end_dates.occurrence_end_date22,
end_dates.occurrence_end_date23,
end_dates.occurrence_end_date24,
end_dates.occurrence_end_date25,
end_dates.occurrence_end_date26,
end_dates.occurrence_end_date27,
end_dates.occurrence_end_date28,
end_dates.occurrence_end_date29,
end_dates.occurrence_end_date30,
descriptions.occurrence_description1,
descriptions.occurrence_description2,
descriptions.occurrence_description3,
descriptions.occurrence_description4,
descriptions.occurrence_description5,
descriptions.occurrence_description6,
descriptions.occurrence_description7,
descriptions.occurrence_description8,
descriptions.occurrence_description9,
descriptions.occurrence_description10,
descriptions.occurrence_description11,
descriptions.occurrence_description12,
descriptions.occurrence_description13,
descriptions.occurrence_description14,
descriptions.occurrence_description15,
descriptions.occurrence_description16,
descriptions.occurrence_description17,
descriptions.occurrence_description18,
descriptions.occurrence_description19,
descriptions.occurrence_description20,
descriptions.occurrence_description21,
descriptions.occurrence_description22,
descriptions.occurrence_description23,
descriptions.occurrence_description24,
descriptions.occurrence_description25,
descriptions.occurrence_description26,
descriptions.occurrence_description27,
descriptions.occurrence_description28,
descriptions.occurrence_description29,
descriptions.occurrence_description30
FROM occurrences
LEFT JOIN start_dates ON start_dates.claim_id = occurrences.claim_id
LEFT JOIN end_dates ON end_dates.claim_id = occurrences.claim_id
LEFT JOIN descriptions ON descriptions.claim_id = occurrences.claim_id;
| Column | Data Type | Indexed |
|---|---|---|
claim_id | bigint | No |
occurrence_code1 | text | No |
occurrence_code2 | text | No |
occurrence_code3 | text | No |
occurrence_code4 | text | No |
occurrence_code5 | text | No |
occurrence_code6 | text | No |
occurrence_code7 | text | No |
occurrence_code8 | text | No |
occurrence_code9 | text | No |
occurrence_code10 | text | No |
occurrence_code11 | text | No |
occurrence_code12 | text | No |
occurrence_code13 | text | No |
occurrence_code14 | text | No |
occurrence_code15 | text | No |
occurrence_code16 | text | No |
occurrence_code17 | text | No |
occurrence_code18 | text | No |
occurrence_code19 | text | No |
occurrence_code20 | text | No |
occurrence_code21 | text | No |
occurrence_code22 | text | No |
occurrence_code23 | text | No |
occurrence_code24 | text | No |
occurrence_code25 | text | No |
occurrence_code26 | text | No |
occurrence_code27 | text | No |
occurrence_code28 | text | No |
occurrence_code29 | text | No |
occurrence_code30 | text | No |
occurrence_start_date1 | date | No |
occurrence_start_date2 | date | No |
occurrence_start_date3 | date | No |
occurrence_start_date4 | date | No |
occurrence_start_date5 | date | No |
occurrence_start_date6 | date | No |
occurrence_start_date7 | date | No |
occurrence_start_date8 | date | No |
occurrence_start_date9 | date | No |
occurrence_start_date10 | date | No |
occurrence_start_date11 | date | No |
occurrence_start_date12 | date | No |
occurrence_start_date13 | date | No |
occurrence_start_date14 | date | No |
occurrence_start_date15 | date | No |
occurrence_start_date16 | date | No |
occurrence_start_date17 | date | No |
occurrence_start_date18 | date | No |
occurrence_start_date19 | date | No |
occurrence_start_date20 | date | No |
occurrence_start_date21 | date | No |
occurrence_start_date22 | date | No |
occurrence_start_date23 | date | No |
occurrence_start_date24 | date | No |
occurrence_start_date25 | date | No |
occurrence_start_date26 | date | No |
occurrence_start_date27 | date | No |
occurrence_start_date28 | date | No |
occurrence_start_date29 | date | No |
occurrence_start_date30 | date | No |
occurrence_end_date1 | date | No |
occurrence_end_date2 | date | No |
occurrence_end_date3 | date | No |
occurrence_end_date4 | date | No |
occurrence_end_date5 | date | No |
occurrence_end_date6 | date | No |
occurrence_end_date7 | date | No |
occurrence_end_date8 | date | No |
occurrence_end_date9 | date | No |
occurrence_end_date10 | date | No |
occurrence_end_date11 | date | No |
occurrence_end_date12 | date | No |
occurrence_end_date13 | date | No |
occurrence_end_date14 | date | No |
occurrence_end_date15 | date | No |
occurrence_end_date16 | date | No |
occurrence_end_date17 | date | No |
occurrence_end_date18 | date | No |
occurrence_end_date19 | date | No |
occurrence_end_date20 | date | No |
occurrence_end_date21 | date | No |
occurrence_end_date22 | date | No |
occurrence_end_date23 | date | No |
occurrence_end_date24 | date | No |
occurrence_end_date25 | date | No |
occurrence_end_date26 | date | No |
occurrence_end_date27 | date | No |
occurrence_end_date28 | date | No |
occurrence_end_date29 | date | No |
occurrence_end_date30 | date | No |
occurrence_description1 | text | No |
occurrence_description2 | text | No |
occurrence_description3 | text | No |
occurrence_description4 | text | No |
occurrence_description5 | text | No |
occurrence_description6 | text | No |
occurrence_description7 | text | No |
occurrence_description8 | text | No |
occurrence_description9 | text | No |
occurrence_description10 | text | No |
occurrence_description11 | text | No |
occurrence_description12 | text | No |
occurrence_description13 | text | No |
occurrence_description14 | text | No |
occurrence_description15 | text | No |
occurrence_description16 | text | No |
occurrence_description17 | text | No |
occurrence_description18 | text | No |
occurrence_description19 | text | No |
occurrence_description20 | text | No |
occurrence_description21 | text | No |
occurrence_description22 | text | No |
occurrence_description23 | text | No |
occurrence_description24 | text | No |
occurrence_description25 | text | No |
occurrence_description26 | text | No |
occurrence_description27 | text | No |
occurrence_description28 | text | No |
occurrence_description29 | text | No |
occurrence_description30 | text | No |