SQL Conditional sum and grouping -

i have query that's trying sum patient's length of stay @ hospital. here example of data

| patient | admission_id | admission_event_id | admission_event_type  | start date | end date   | duration | linked_admission | | p0001   | adm0001      | ae1                | (formal) separation   | 2012-12-18 | 2012-12-18 | 0        | adm0002          | | p0001   | adm0001      | ae2                | statistical admission | 2012-12-17 | 2012-12-18 | 1        | adm0002          | | p0001   | adm0002      | ae3                | statistical separation| 2012-12-17 | 2012-12-17 | 0        | null             | | p0001   | adm0002      | ae4                | (formal) admission    | 2012-11-30 | 2012-12-17 | 17       | null             | | p0002   | adm0003      | ae5                | (formal) admission    | 2012-11-30 | 2012-12-25 | 25       | null             | . . .  

edit: forgot mention, there column links admission id (only used when patient statistically separated , admitted)

---

by definition, length of stay calculated each patient start of admission until separation (statistical separations , admission carry on admission, they're given new admission id

a report run find out average length of stay (alos) hospital , it's unit, user selects 2 dates report between. i've used cte (lets call ctesep) patient's have been formally separated between reporting period. use cte (called cteadmissions) admissions of patients within ctesep. stuck.

i need sum durations of patient total length of stay admission (which combination of adm0001 , adm0002) total los 18, rather 17 , 1. idea

    order patient     , end_date desc     , adm_id     , case when              admission_event_type  = '(formal) separation   ' 1            when admission_event_type  = 'statistical admission ' 2            when admission_event_type  = 'statistical separation' 3            when admission_event_type  = '(formal) admission    ' 4        end asc 

then sum duration on based on condition. condition rule 'start summing duration of each patient's admission formal separation formal admission'. i'm not sure how do. i've tried:

select sum(duration) over(partition patient) 'sum' 

but give me total los patient across admissions (if have more 1 separation within reporting period) i've tried

 select sum(duration) over(partition patient, admission_id) 'sum' 

but of course gives me los of patient between formal admission , statistical separation (and not los actual definition).

anyone got different way of tackling problem? way, using sybase

how this:

select patientid,        admissionid,        datediff(day,                    max(case when admission_event_type = '(formal) separation   ' startdate end),                   max(case when admission_event_type = '(formal) admission    ' enddate end)                ) total_length data group patientid, admissionid