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)
), dates AS (
SELECT ct.claim_id,
ct.occurrence_date1,
ct.occurrence_date2,
ct.occurrence_date3,
ct.occurrence_date4,
ct.occurrence_date5,
ct.occurrence_date6,
ct.occurrence_date7,
ct.occurrence_date8,
ct.occurrence_date9,
ct.occurrence_date10,
ct.occurrence_date11,
ct.occurrence_date12,
ct.occurrence_date13,
ct.occurrence_date14,
ct.occurrence_date15,
ct.occurrence_date16,
ct.occurrence_date17,
ct.occurrence_date18,
ct.occurrence_date19,
ct.occurrence_date20,
ct.occurrence_date21,
ct.occurrence_date22,
ct.occurrence_date23,
ct.occurrence_date24,
ct.occurrence_date25,
ct.occurrence_date26,
ct.occurrence_date27,
ct.occurrence_date28,
ct.occurrence_date29,
ct.occurrence_date30
FROM public.crosstab('SELECT claim_id, claim_occurrence_index, date
FROM claims.claim_occurrence
ORDER BY claim_id, claim_occurrence_index, date'::text) ct(claim_id bigint, occurrence_date1 date, occurrence_date2 date, occurrence_date3 date, occurrence_date4 date, occurrence_date5 date, occurrence_date6 date, occurrence_date7 date, occurrence_date8 date, occurrence_date9 date, occurrence_date10 date, occurrence_date11 date, occurrence_date12 date, occurrence_date13 date, occurrence_date14 date, occurrence_date15 date, occurrence_date16 date, occurrence_date17 date, occurrence_date18 date, occurrence_date19 date, occurrence_date20 date, occurrence_date21 date, occurrence_date22 date, occurrence_date23 date, occurrence_date24 date, occurrence_date25 date, occurrence_date26 date, occurrence_date27 date, occurrence_date28 date, occurrence_date29 date, occurrence_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,
dates.occurrence_date1,
dates.occurrence_date2,
dates.occurrence_date3,
dates.occurrence_date4,
dates.occurrence_date5,
dates.occurrence_date6,
dates.occurrence_date7,
dates.occurrence_date8,
dates.occurrence_date9,
dates.occurrence_date10,
dates.occurrence_date11,
dates.occurrence_date12,
dates.occurrence_date13,
dates.occurrence_date14,
dates.occurrence_date15,
dates.occurrence_date16,
dates.occurrence_date17,
dates.occurrence_date18,
dates.occurrence_date19,
dates.occurrence_date20,
dates.occurrence_date21,
dates.occurrence_date22,
dates.occurrence_date23,
dates.occurrence_date24,
dates.occurrence_date25,
dates.occurrence_date26,
dates.occurrence_date27,
dates.occurrence_date28,
dates.occurrence_date29,
dates.occurrence_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 dates ON 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_date1 | date | No |
occurrence_date2 | date | No |
occurrence_date3 | date | No |
occurrence_date4 | date | No |
occurrence_date5 | date | No |
occurrence_date6 | date | No |
occurrence_date7 | date | No |
occurrence_date8 | date | No |
occurrence_date9 | date | No |
occurrence_date10 | date | No |
occurrence_date11 | date | No |
occurrence_date12 | date | No |
occurrence_date13 | date | No |
occurrence_date14 | date | No |
occurrence_date15 | date | No |
occurrence_date16 | date | No |
occurrence_date17 | date | No |
occurrence_date18 | date | No |
occurrence_date19 | date | No |
occurrence_date20 | date | No |
occurrence_date21 | date | No |
occurrence_date22 | date | No |
occurrence_date23 | date | No |
occurrence_date24 | date | No |
occurrence_date25 | date | No |
occurrence_date26 | date | No |
occurrence_date27 | date | No |
occurrence_date28 | date | No |
occurrence_date29 | date | No |
occurrence_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 |