Idea Transcript
Extending Dimensional Modeling through the abstraction of > public void Accumulate(SqlString value) { if (value.IsNull) { return; } this.valuelist.Add(value); } /// /// /// ///
219
Merge the partially computed aggregate with this aggregate.
public void Merge(SortConcatenate group) { this.valuelist.AddRange(group.valuelist); } /// /// Called at the end of aggregation, to return the results of the aggregation. /// /// public SqlString Terminate() { string output = string.Empty; //delete the trailing comma, if any this.valuelist.Sort(); if (this.valuelist.Count > 0) { output = Convert.ToString ( this.valuelist[0] ); for (int i = 1; i < valuelist.Count; i++) output = output + "," + Convert.ToString (this.valuelist[i]); } return new SqlString(output); } public void Read(BinaryReader r) { valuelist = new ArrayList(); string[] tmpList = r.ReadString().Split('|'); foreach (string entry in tmpList) { valuelist.Add(entry); } } public void Write(BinaryWriter w) { string[] tmpList = new string[valuelist.Count]; for (int i = 0; i < valuelist.Count; i++) { tmpList[i] = Convert.ToString( valuelist[i] ); } w.Write(String.Join("|", tmpList)); } }
220
Appendix 7: Seniors Advocate Study SQL Constellation Rules The SQL statements below were provided by the Vancouver Island Health Authority and the Province of British Columbia. They were adapted to the data structures created as part of this thesis. They were utilized in the analysis study of appropriate placement of seniors in residential care.
1)
Light Care patients in CCRS select dw_seq_id from (SELECT f.dw_seq_id ,case when d2.CPS in (0,1) and d3.ADL_HIERARCHY in (0,1) and d4.CHESS in (0,1,2) and d5.E4AA_WANDERING_FREQ=0 then 'Light Care Needs' else Null end as value FROM star.dbo.F_CCRS_ASSESSMENT AS f INNER JOIN star.dbo.D_CCRS_ASSESSMENT_FLAGS AS d1 ON f.CRS_ASSESSMENT_FLAGS_Dim_Key = d1.CRS_ASSESSMENT_FLAGS_Dim_Key INNER JOIN star.dbo.D_Scales_Cognitive_Depression_Social_CCRS AS d2 ON f.Scales_Cognitive_Depression_Social_Dim_Key = d2.Scales_Cognitive_Depression_Social_Dim_Key INNER JOIN star.dbo.D_Scales_ADL AS d3 on f.Scores_ADL_Dim_Key = d3.Scores_ADL_Dim_Key INNER JOIN star.dbo.D_H1a_To_H3b_CCRS as d6 on d6.H1a_To_H3b_Dim_Key=f.H1a_To_H3b_Dim_Key inner join star.dbo.D_P1aa_P1bfa_CCRS as d8 on d8.P1aa_P1bfa_Dim_Key=f.P1aa_P1bfa_Dim_Key inner join star.dbo.D_Scales_Chess_Pain_PURS_ABS_CCRS AS d4 ON f.Scales_Chess_Pain_PURS_ABS_Dim_Key = d4.Scales_Chess_Pain_PURS_ABS_Dim_Key inner join star.dbo.D_E4ca_To_E5_CCRS as d7 on d7.E4ca_To_E5_Dim_Key=f.E4ca_To_E5_Dim_Key inner join star.dbo.D_E2_To_E4bb_CCRS as d5 on d5.E2_To_E4bb_Dim_Key=f.E2_To_E4bb_Dim_Key left outer join (select * from (select Disease_Group_Dim_Key,ccrs_observation_value,CCRS_OBSERVATION_FIELD from star.dbo.B_DISEASE_DIAGNOSIS_BRIDGE as BDIS left outer join star.dbo.D_Disease_Diagnosis_CCRS as ddis on ddis.DISEASE_DIAGNOSIS_DIM_KEY=BDIS.DISEASE_DIAGNOSIS_DIM_KEY) as source pivot (max(ccrs_observation_value) for CCRS_OBSERVATION_FIELD in ([i1a],[i1b],[i1c],[i1d],[i1e],[i1f],[i1g],[i1h] ,[i1i] ,[i1j] ,[i1k],[i1l] ,[i1m],[i1n] ,[i1o] ,[i1p] ,[i1q] ,[i1r] ,[i1s] ,[i1t] , [i1u] ,[i1v] ,[i1w] ,[i1x] ,[i1y] ,[i1z],[i1aa],[i1bb],[i1cc],[i1dd],[i1ee],[i1ff],[i1gg],[i1hh],[i1ii],[i1jj],[i1kk],[i1ll],[i1 mm],[i1nn],[i1oo],[i1pp],[i1qq],[i1rr],[i1ss],[i1tt],[i1uu])) as pivottable) as ddis on ddis.Disease_Group_Dim_Key=f.Disease_Group_Dim_Key where d1.AA8_ASSESSMENT_TYPE in (1,2,5) ) as a where a.value is not null
2) Assisted Living Plus patients in CCRS select dw_seq_id from (SELECT f.dw_seq_id ,case when d2.CPS in (0,1) and d3.ADL_LONG_FORM in (0,1,2,3,4,5,6)
221
and ddis.i1ff is null and ddis.i1gg is null and ddis.i1hh is null and ddis.i1ii is null and E4AA_WANDERING_FREQ=0 and E4EA_RESISTS_CARE_FREQ=0 and E4DA_DISRUPTIVE_FREQ=0 and E4CA_PHYSICAL_ABUSE_FREQ=0 and E4BA_VERBAL_ABUSE_FREQ=0 and P1AG_OXYGEN_THERAPY=0 then 'Assisted Living Plus' else null end as Value FROM star.dbo.F_CCRS_ASSESSMENT AS f INNER JOIN star.dbo.D_CCRS_ASSESSMENT_FLAGS AS d1 ON f.CRS_ASSESSMENT_FLAGS_Dim_Key = d1.CRS_ASSESSMENT_FLAGS_Dim_Key INNER JOIN star.dbo.D_Scales_Cognitive_Depression_Social_CCRS AS d2 ON f.Scales_Cognitive_Depression_Social_Dim_Key = d2.Scales_Cognitive_Depression_Social_Dim_Key INNER JOIN star.dbo.D_Scales_ADL AS d3 ON f.Scores_ADL_Dim_Key = d3.Scores_ADL_Dim_Key INNER JOIN star.dbo.D_H1a_To_H3b_CCRS as d6 on d6.H1a_To_H3b_Dim_Key=f.H1a_To_H3b_Dim_Key inner join star.dbo.D_P1aa_P1bfa_CCRS as d8 on d8.P1aa_P1bfa_Dim_Key=f.P1aa_P1bfa_Dim_Key inner join star.dbo.D_Scales_Chess_Pain_PURS_ABS_CCRS AS d4 ON f.Scales_Chess_Pain_PURS_ABS_Dim_Key = d4.Scales_Chess_Pain_PURS_ABS_Dim_Key inner join star.dbo.D_E4ca_To_E5_CCRS as d7 on d7.E4ca_To_E5_Dim_Key=f.E4ca_To_E5_Dim_Key inner join star.dbo.D_E2_To_E4bb_CCRS as d5 on d5.E2_To_E4bb_Dim_Key=f.E2_To_E4bb_Dim_Key left outer join (select * from (select Disease_Group_Dim_Key,ccrs_observation_value,CCRS_OBSERVATION_FIELD from star.dbo.B_DISEASE_DIAGNOSIS_BRIDGE as BDIS left outer join star.dbo.D_Disease_Diagnosis_CCRS as ddis on ddis.DISEASE_DIAGNOSIS_DIM_KEY=BDIS.DISEASE_DIAGNOSIS_DIM_KEY) as source pivot (max(ccrs_observation_value) for CCRS_OBSERVATION_FIELD in ([i1a],[i1b],[i1c],[i1d],[i1e],[i1f],[i1g],[i1h] ,[i1i] ,[i1j] ,[i1k],[i1l] ,[i1m],[i1n] ,[i1o] ,[i1p] ,[i1q] ,[i1r] ,[i1s] ,[i1t] ,[ i1u] ,[i1v] ,[i1w] ,[i1x] ,[i1y] ,[i1z],[i1aa],[i1bb],[i1cc],[i1dd],[i1ee],[i1ff],[i1gg],[i1hh],[i1ii],[i1jj],[i1kk],[i1ll],[i1 mm],[i1nn],[i1oo],[i1pp],[i1qq],[i1rr],[i1ss],[i1tt],[i1uu])) as pivottable) as ddis on ddis.Disease_Group_Dim_Key=f.Disease_Group_Dim_Key where d1.AA8_ASSESSMENT_TYPE in (1,2,5)) as a where a.value is not null
3) Dementia Care Needs patients in CCRS select dw_seq_id from (SELECT f.dw_seq_id ,case when d2.CPS in (0,1,2,3) and d3.ADL_LONG_FORM in (0,1,2,3,4) and d6.H1B_BLADDER_CONTINENCE_SELF in (0,1,2,3) and (ddis.i1r=1 or ddis.i1v=1) and ddis.i1ff is null and ddis.i1gg is null and ddis.i1hh is null and ddis.i1ii is null and E4EA_RESISTS_CARE_FREQ=0 and E4DA_DISRUPTIVE_FREQ=0 and E4CA_PHYSICAL_ABUSE_FREQ=0 and E4BA_VERBAL_ABUSE_FREQ=0 and P1AG_OXYGEN_THERAPY=0 then 'Dementia Care Needs' else null end as Value FROM star.dbo.F_CCRS_ASSESSMENT AS f INNER JOIN star.dbo.D_CCRS_ASSESSMENT_FLAGS AS d1 ON f.CRS_ASSESSMENT_FLAGS_Dim_Key = d1.CRS_ASSESSMENT_FLAGS_Dim_Key INNER JOIN star.dbo.D_Scales_Cognitive_Depression_Social_CCRS AS d2 ON f.Scales_Cognitive_Depression_Social_Dim_Key = d2.Scales_Cognitive_Depression_Social_Dim_Key INNER JOIN star.dbo.D_Scales_ADL AS d3 ON f.Scores_ADL_Dim_Key = d3.Scores_ADL_Dim_Key INNER JOIN star.dbo.D_H1a_To_H3b_CCRS as d6 on d6.H1a_To_H3b_Dim_Key=f.H1a_To_H3b_Dim_Key inner join star.dbo.D_P1aa_P1bfa_CCRS as d8 on d8.P1aa_P1bfa_Dim_Key=f.P1aa_P1bfa_Dim_Key inner join star.dbo.D_Scales_Chess_Pain_PURS_ABS_CCRS AS d4 ON f.Scales_Chess_Pain_PURS_ABS_Dim_Key = d4.Scales_Chess_Pain_PURS_ABS_Dim_Key inner join star.dbo.D_E4ca_To_E5_CCRS as d7 on
222
d7.E4ca_To_E5_Dim_Key=f.E4ca_To_E5_Dim_Key inner join star.dbo.D_E2_To_E4bb_CCRS as d5 on d5.E2_To_E4bb_Dim_Key=f.E2_To_E4bb_Dim_Key left outer join (select * from (select Disease_Group_Dim_Key,ccrs_observation_value,CCRS_OBSERVATION_FIELD from star.dbo.B_DISEASE_DIAGNOSIS_BRIDGE as BDIS left outer join star.dbo.D_Disease_Diagnosis_CCRS as ddis on ddis.DISEASE_DIAGNOSIS_DIM_KEY=BDIS.DISEASE_DIAGNOSIS_DIM_KEY) as source pivot (max(ccrs_observation_value) for CCRS_OBSERVATION_FIELD in ([i1a],[i1b],[i1c],[i1d],[i1e],[i1f],[i1g],[i1h] ,[i1i] ,[i1j] ,[i1k],[i1l] ,[i1m],[i1n] ,[i1o] ,[i1p] ,[i1q] ,[i1r] ,[i1s] ,[i1t] , [i1u] ,[i1v] ,[i1w] ,[i1x] ,[i1y] ,[i1z],[i1aa],[i1bb],[i1cc],[i1dd],[i1ee],[i1ff],[i1gg],[i1hh],[i1ii],[i1jj],[i1k k],[i1ll], [i1mm],[i1nn],[i1oo],[i1pp],[i1qq],[i1rr],[i1ss],[i1tt],[i1uu])) as pivottable) as ddis on ddis.Disease_Group_Dim_Key=f.Disease_Group_Dim_Key where d1.AA8_ASSESSMENT_TYPE in (1,2,5)) as a where a.value is not null
4) Prior Home Care Assessment before Continuing Care Assessment select distinct dw_seq_id as child_dw_seq_id, isnull((select top 1 dw_seq_id from star.dbo.F_HCRS_ASSESSMENT as fd where fd.patient_dim_key=fca.Patient_DIM_KEY and fd.Assessment_Reference_Date_Dim_Key