pt_table

encounter_id medical_record_num admit_date discharge_date {.....}

10001 7890123 01/05/2013 01/10/2013

10002 8901234 05/20/2013 05/23/2013

10003 8901234 06/20/2013 06/21/2013

10004 7890123 02/20/2013 02/23/2013

10005 7890123 02/25/2013 02/27/2013

10006 5678901 12/24/2013 12/24/2013

10007 5678901 12/24/2013 01/02/2014

10008 4567890 05/20/2013 06/01/2013

10009 4567890 07/02/2013 07/02/2013

10010 7890123 02/09/2013 02/18/2013

Desired output:

index_table

encounter_id medical_record_num Index_admit_date Index_discharge_date {.....}

10001 7890123 01/05/2013 01/10/2013

10002 8901234 05/20/2013 05/23/2013

10004 7890123 02/20/2013 02/23/2013

10006 5678901 12/24/2013 12/24/2013

10008 4567890 05/20/2013 06/01/2013

10009 4567890 07/02/2013 07/02/2013

readmission_table

encounter_id medical_record_num Rdm_admit_date Rdm_discharge_date {.....}

10003 8901234 06/20/2013 06/21/2013

10005 7890123 02/25/2013 02/27/2013

10007 5678901 12/24/2013 01/02/2014

10010 7890123 02/09/2013 03/15/2013

DDL:

encounter_id: key

medical_record_num: unique to patient

Index visit is any admission without an admission 30 days prior.

A readmission is a return to the hospital within 30 days and can be multiple based on Index.

All patient visits are either an Index admission or a readmission.

I'm attempting to build OLAP cubes with these tables too, so I'm not sure how to do that with views, yet.

Thanks.