I have data that looks like this:
ID FROM TO START_CODE END_CODE TYPE
A 01/01/2012 02/02/2012 P E 1
A 12/03/2011 01/01/2012 P X 1
A 01/01/2011 12/03/2011 S X 2
A 01/01/2010 02/02/2010 P E 2
A 12/03/2010 01/01/2010 P X 4
A 01/01/2009 12/03/2009 S X 1
Basically, this is information for one client. The end_code 'e' denotes the end of a period of care, the start_code 's' denotes a start of a period of care. A start code of 'p' denotes the dates are a continuation, as does an end code of 'x'. What I would like to be able to return is data that looks like the following:
ID START END Types
A 01/01/2011 02/02/2012 2,1,1
A 01/01/2009 02/02/2010 1,4,2
The query for the first table looks similar to this (example only), however, I would be interested in returning for a number of clients, and not just 'A'.
SELECT
A.ID,
A.FROM,
A.TO,
A.START_CODE,
A.END_CODE,
A.Type
WHERE
A.ID = 'A'
I've used xmlagg in the past to achieve similar things (i.e. to list information in one cell), but it's actually identifying seperate start and end dates for each client, and then returning the sequence of types that I'm struggling with. For reference, my oracle version is 10g, 10.2.0.5.0.
Thanks for your time, any pointers or assistance is appreciated.
Edited to include suggestion by A.B.Cade below:
SELECT
t3.MOV_PER_GRO_ID,
t3.f,
t3.MOV_END_DATE,
t3.types,
LENGTH(REGEXP_REPLACE(t3.types,'[^,]')) as "Count"
FROM(
SELECT
sys_connect_by_path(t2.MOV_2000_PLACEMENT_TYPE,',') types,
connect_by_root(t2.MOV_START_DATE) f,
t2.MOV_START_DATE,
t2.MOV_END_DATE,
connect_by_isleaf is_leaf,
t2.MOV_PER_GRO_ID
FROM (SELECT t.*,
lag(t.MOV_START_DATE) over (ORDER BY t.MOV_PER_GRO_ID, t.MOV_START_DATE) nfrom
FROM O_MOVEMENTS t
WHERE t.MOV_PER_GRO_ID IN ('A','B'))t2
START
WITH
t2.MOV_2000_START_REASON = 'S'
CONNECT BY
PRIOR t2.MOV_START_DATE = t2.nfrom
AND PRIOR t2.MOV_PER_GRO_ID = t2.MOV_PER_GRO_ID
AND t2.MOV_2000_START_REASON IN ('P'))t3
where t3.is_leaf=1
Updated based on ABCade's solution. After a few tweaks (again thanks to ABCade) it appears to be working.