Comparing lines of detail

[miketbuck]

I'm looking at admissions, discharges, and programs of individuals within a hospital setting. An easy sort to look at individual occurences. I am grouping by client ID. Each line of information has admit date, discharge date, program, entry date, Episode number, and client ID. I am trying to create a report that combines episodes that have either an overlap of admissions or have an admission within 1 day of discharge. From this information I need to derive the initial start date / program / episode # of this defined chain of services, and the discharge date / program / episode # of the last program he was discharged from in this chain of services. (this would be null if the client is not yet disharged from the last episode) Note that a client could have multiple chains of services. I've created a formula to define the episode order by admission date. I was trying to complete this without creating a series of subreports but I might have to proceed that way. I thought I could create a subreport for each episode number and create a variable to push forward the needed information into one report. Or would it be better to create a sql command line combining info before sorting in a single report?

 

[miketbuck]

Forgot to include one important Item. The results need to to be listed on one line for each episode chain. Since I am creating this as a data dump for all clients within a fiscal year.

 

[lbass]

I think you should show some sample data and then show how you want it to display.

-LB

 

[miketbuck]

ok, here is an example:
episode program admit date discharge date PATID
1 CCS South 4/23/2007 11/16/2007 9050112
2 DWTC N/C 8/14/2007 2/6/2008 9050112
3 WIN SR 2/7/2008 6/12/2008 9050112
1 ATC North 5/18/2007 8/11/2009 9050236
2 WIN NR 8/9/2009 9050236

Preferred Display:
9050112
1 CCS South 4/23/2007 3 WIN SR 6/12/2008
9050236
1 ATC North 5/18/2007

Note that all chains will not start at episode 1.
Thanks for you help.

 

[lbass]

Why is there no discharge date for 9050236? It implies they are still there. If they are still there, wouldn't you want to show 2 WIN NR with no date?

-LB

 

[miketbuck]

Because the client is still within a program it cannot be considered as the last program of the chain. If I was to list that program as the last program and three weeks later the client was admitted to another program and was discharged from WIN NR, the report would be displaying inaccurate information. By leaving it blank, the report denotes that services are still being provided and full discharge has not yet occurred.