Home » SQL & PL/SQL » SQL & PL/SQL » grouping query / Can someone help me writing sql (merged 4 threads)
grouping query / Can someone help me writing sql (merged 4 threads) [message #199332] Mon, 23 October 2006 11:17 Go to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
>>Hi gurus,
>>
>>I have records below in my source table Stage_1_IPC_CARE.
>>
>>
>>CLIENT_ID BEGIN_DT REVISION_DT END_DT RESIDENTIAL_TYPE_DESC
>>A 19-Aug-00 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 1-Sep-00 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 29-Mar-01 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 30-Mar-01 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-01 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-01 29-Jan-02 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-01 3-Jun-02 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-02 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 1-Nov-02 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 5-Feb-03 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 25-Jul-03 18-Aug-03 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 1-Sep-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 2-Sep-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 15-Mar-04 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 24-Jun-04 17-Aug-04 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 14-Oct-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 18-Nov-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>
>>
>>when residential_type_desc chages I want to have begin_dt and end_dt assigned to it in following manner
>>If begin_Dt chages when residential_type_desc chages then take begin_Dt only
>>but if begin_dt remains the same then take take revision_dt as begin_dt
>>
>>
>>and second thing
>>when residential_type_desc changes i want to have end_dt as (revision_dt - 1day) for the next record.
>>
>>and my output should look like as below:
>>
>>CLIENT_ID BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
>>A 19-Aug-00 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-02 24-Jul-03 RESIDENTIAL SUPPORT SERVICE
>>A 25-Jul-03 23-Jun-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 24-Jun-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>
>>Please help me I am stuck ATTACHMENT IS THE TABLE DATA FOR SOURCE AND TARGET TABLE.
>>
>>Thanks in advance
>>Jay
>>
>>
>
>

Re: Query Help [message #199340 is a reply to message #199332] Mon, 23 October 2006 12:18 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
http://www.orafaq.com/forum/fa/1579/0/ This is not the way to ask questions here; as it is your first post, perhaps just an advice or two: read the Sticky thread.
Don't post replied or forwarded e-mail (I can't decide which one is it). Use [CODE] tags to format your data (and queries you tried) properly so that other Forum members can easily read it.
Do not attach Excel files - not everyone can download them. Instead, include whole CREATE TABLE and INSERT INTO statements to provide sample data.

My eyes ache from reading such a post.
Re: Query Help [message #199347 is a reply to message #199332] Mon, 23 October 2006 13:03 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Alright I will obey from next time.

Can I post same question ?
Awaiting your suggestion.
Thanks
Jay
Regarding grouping query [message #199349 is a reply to message #199332] Mon, 23 October 2006 13:36 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
I have a table with the following data

>>CLIENT_ID BEGIN_DT REVISION_DT END_DT RESIDENTIAL_TYPE_DESC
>>A 19-Aug-00 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 1-Sep-00 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 29-Mar-01 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-00 30-Mar-01 18-Aug-01 SUPERVISED LIVING
>>A 19-Aug-01 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-01 29-Jan-02 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-01 3-Jun-02 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-02 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 1-Nov-02 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 5-Feb-03 18-Aug-03 RESIDENTIAL SUPPORT SERVICE
>>A 19-Aug-02 25-Jul-03 18-Aug-03 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 1-Sep-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 2-Sep-03 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 15-Mar-04 17-Aug-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 19-Aug-03 24-Jun-04 17-Aug-04 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 14-Oct-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE
>>A 18-Aug-04 18-Nov-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE



Now I need a query with data below using above table. I want to assign Begin_Dt as revision_dt when group of begin_Dt changes. I want to assign end_dt as next record's revision_Dt - 1day for every group.


>>CLIENT_ID BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
>>A 19-Aug-00 18-Aug-02 SUPERVISED LIVING
>>A 19-Aug-02 24-Jul-03 RESIDENTIAL SUPPORT SERVICE
>>A 25-Jul-03 23-Jun-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
>>A 24-Jun-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE



I am stuck.Any suggestion is more than welcome.
I have tried below query but didn't work somehow

select * FROM for_mv_residence_type
WHERE (
select FIRST(begin_dt) over (partition by RESIDENTIAL_TYPE_DESC,BEGIN_DT order
by BEGIN_DT asc) begin_dt,
laST(end_dt) over (partition by RESIDENTIAL_TYPE_DESC,BEGIN_DT order by
BEGIN_DT asc) end_dt,
RESIDENTIAL_TYPE_DESC FROM for_mv_residence_type)


I am stuck kindly help if anyone have some idea.
Thanks a ton in advance,
Jay
Re: Regarding grouping query [message #199351 is a reply to message #199349] Mon, 23 October 2006 14:07 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
CODE tags are still missing.
CREATE TABLE statements are still missing.
Sample data via INSERT statements are still missing.
Re: Regarding grouping query [message #199353 is a reply to message #199351] Mon, 23 October 2006 14:19 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
CREATE TABLE

CREATE TABLE FOR_MV_RESIDENCE_TYPE
   (	CLIENT_ID VARCHAR2(2 BYTE), 
	BEGIN_DT DATE, 
	REVISION_DT DATE, 
	END_DT DATE, 
	RESIDENTIAL_TYPE_DESC VARCHAR2(50 BYTE)
   ) ;

INSERT STATEMENT

Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('',to_date('','null'),to_date('','null'),to_date('','null'),'');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),null,to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('01-SEP-00','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('29-MAR-01','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('30-MAR-01','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),null,to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),to_date('29-JAN-02','null'),to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),to_date('03-JUN-02','null'),to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),null,to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('01-NOV-02','null'),to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('05-FEB-03','null'),to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('25-JUL-03','null'),to_date('18-AUG-03','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),null,to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('01-SEP-03','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('02-SEP-03','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('15-MAR-04','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('24-JUN-04','null'),to_date('17-AUG-04','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),null,to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),to_date('14-OCT-04','null'),to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),to_date('18-NOV-04','null'),to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');


above are the create table and Insert into table statements as requested.

Thanks
Jay

[Updated on: Mon, 23 October 2006 14:26] by Moderator

Report message to a moderator

Re: Query Help [message #199361 is a reply to message #199347] Mon, 23 October 2006 15:21 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not about obeying, but making other people lives less complicated. If you don't provide sample data, anyone who would like to help you has to build his own playground (and not everyone wants to do that).
Re: Query Help [message #199362 is a reply to message #199361] Mon, 23 October 2006 15:31 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Below are the create table and insert script

CREATE TABLE

CREATE TABLE FOR_MV_RESIDENCE_TYPE
( CLIENT_ID VARCHAR2(2 BYTE),
BEGIN_DT DATE,
REVISION_DT DATE,
END_DT DATE,
RESIDENTIAL_TYPE_DESC VARCHAR2(50 BYTE)
) ;

INSERT STATEMENT

Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('',to_date('','null'),to_date('','null'),to_date('','null'),'');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),null,to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('01-SEP-00','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('29-MAR-01','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-00','null'),to_date('30-MAR-01','null'),to_date('18-AUG-01','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),null,to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),to_date('29-JAN-02','null'),to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-01','null'),to_date('03-JUN-02','null'),to_date('18-AUG-02','null'),'SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),null,to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('01-NOV-02','null'),to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('05-FEB-03','null'),to_date('18-AUG-03','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-02','null'),to_date('25-JUL-03','null'),to_date('18-AUG-03','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),null,to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('01-SEP-03','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('02-SEP-03','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('15-MAR-04','null'),to_date('17-AUG-04','null'),'FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('19-AUG-03','null'),to_date('24-JUN-04','null'),to_date('17-AUG-04','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),null,to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),to_date('14-OCT-04','null'),to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE ("CLIENT_ID","BEGIN_DT","REVISION_DT","END_DT","RESIDENTIAL_TYPE_DESC") values ('A',to_date('18-AUG-04','null'),to_date('18-NOV-04','null'),to_date('17-AUG-05','null'),'RESIDENCIAL SUPPORT SERVICE');

let me know if anything else is missing.
Jay
Re: Query Help [message #199364 is a reply to message #199362] Mon, 23 October 2006 16:31 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does this work on your database?

select to_date('29-MAR-01','null') from dual;

Mine doesn't recognize it. In other words, I'm afraid that this is invalid statement. But never mind that, it isn't that difficult to fix it (but is annoying).

After I created the table and loaded data into it (by changing all TO_DATE('29-mar-01', 'null') to TO_DATE('29-MAR-01', 'DD-MON-YY'); I hope that was your intention too), I spent some time looking at it and couldn't find connection between data and desired output.

How do you know the moment of 'residential_type_desc' change? Ordered by 'begin_dt'? I don't think so - if we go by 'begin_dt', 'residential_type_desc' changes 7 times (while your example shows 4 of them).

Perhaps it is time of the day - it is almost midnight here - but I'm sorry, I can't reproduce your output. I just don't understand it. Perhaps tomorrow will be brighter than this evening, or - more likely - someone else will find the way.

Therefore, just an idea: you could try to use LAG or LEAD functions to fetch data from "previous" or "next" record (if you know which one is "next". I don't.).
Re: Query Help [message #199366 is a reply to message #199364] Mon, 23 October 2006 16:43 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I've just seen another topic which, as it seems, covers the same problem. Thomas gave a solution - check it and see whether it suits your needs.
Re: Query Help [message #199367 is a reply to message #199364] Mon, 23 October 2006 17:09 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
Thanks for the reply.
There are four groups for RESIDENTIAL_TYPE_DESC:

first group SUPERVISED LIVING Having 8 records
second group RESIDENTIAL SUPPORT SERVICE having 3 records
third group FOSTER/COMPANION CARE RESIDENTIAL Having 5 records
forth group RESIDENTIAL SUPPORT SERVICE having 4 records

Now I want to assign Begin_dt and end_dt values from REVISION_DT.

The first row in my out put is not a problem it should give record like
BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
19-AUG-00 18-AUG-02 SUPERVISE LIVING

But i want to assign REVISION_DT for next group which is RESIDENTIAL SUPPORT SERVICE

WHERE BEGIN_DT REMAINS AS IT IS TO (19-AUG-2002)
here I am doing next records RESIDENTIAL_DT - 1DAY (25-JUL-03 - 1DAY )= 24-JUL-03 = END_DT

BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
19-AUG-02 24-JUL-03 RESIDENTIAL SUPPORT SERVICE


Lastly, when the residential_type_desc changes and begin_dt remains same then I want same record's revision_dt as my begin_dt

so third row would look like as below:

BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
25-AUG-03 23-JUN-04 FOSTER/COMPANION CARE RESIDENTIAL

in above record end_dt is poppulated as we did for RESIDENTIAL SUPPORT SERVICE AND BEGIN_DT IS just assign what ever date is there in revision_Dt.

I DONT KNOW HOW TO EXPLAIN THE SITUATION. ANYHOW I DID MY BEST TO EXPLAIN IT.

I REALLY APPRECIATE YOUR HELP,
i did query as below but somehow didn't work ?

select client_id,begin_dt,max(revision_Dt),end_dt,residential_type_desc from (
select first_value(begin_dt) over (partition by RESIDENTIAL_TYPE_DESC,begin_dt order
by BEGIN_DT asc) begin_dt,
last_value (end_dt) over (partition by RESIDENTIAL_TYPE_DESC,begin_dt order by
BEGIN_DT asc) end_dt,
RESIDENTIAL_TYPE_DESC
from for_mv_residence_type group by client_id,begin_dt,end_dt,residential_type_Desc)


Any suggestion is appreciated.
Thanks
Jay


Re: Query Help [message #199459 is a reply to message #199366] Tue, 24 October 2006 08:46 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
The scenario which Thomas provided a solutions is slightly different from my scenario.

Below is the create table and insert statement which is working

--CREATE TABLE

CREATE TABLE FOR_MV_RESIDENCE_TYPE
( CLIENT_ID VARCHAR2(2 BYTE),
BEGIN_DT DATE,
REVISION_DT DATE,
END_DT DATE,
RESIDENTIAL_TYPE_DESC VARCHAR2(50 BYTE)
) ;

--INSERT INTO TABLE

Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2000','18-AUG-2001','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2000','01-SEP-2000','18-AUG-2001','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2000','29-MAR-2001','18-AUG-2001','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2000','30-MAR-2001','18-AUG-2001','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2001','18-AUG-2002','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2001','29-JAN-2002','18-AUG-2002','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2001','03-JUN-2002','18-AUG-2002','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2002','18-AUG-2003','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2002','01-NOV-2002','18-AUG-2003','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2002','05-FEB-2003','18-AUG-2003','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2002','25-JUL-2003','18-AUG-2003','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2003','17-AUG-2004','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2003','01-SEP-2003','17-AUG-2004','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2003','02-SEP-2003','17-AUG-2004','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2003','15-MAR-2004','17-AUG-2004','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-2003','24-JUN-2004','17-AUG-2004','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','18-AUG-2004','17-AUG-2005','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','18-AUG-2004','14-OCT-2004','17-AUG-2005','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','18-AUG-2004','18-NOV-2004','17-AUG-2005','RESIDENCIAL SUPPORT SERVICE');

I am looking for sql query for the desired output.
Thanks in advance
Jay
grouping query question continued [message #199467 is a reply to message #199332] Tue, 24 October 2006 10:16 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
hi
In regards to my previous grouping query question.
I have acheved below results.

select * from mv_residence_type;

CL BEGIN_DT REVISION_ END_DT RESIDENTIAL_TYPE_DESC
-- --------- --------- --------- --------------------------------------------------
A 19-AUG-00 30-MAR-01 18-AUG-01 SUPERVISED LIVING
A 19-AUG-01 03-JUN-02 18-AUG-02 SUPERVISED LIVING
A 19-AUG-02 25-JUL-03 18-AUG-03 FOSTER/COMPANION CARE RESIDENCIAL
A 19-AUG-03 24-JUN-04 17-AUG-04 RESIDENCIAL SUPPORT SERVICE
A 18-AUG-04 18-NOV-04 17-AUG-05 RESIDENCIAL SUPPORT SERVICE

Now I want to have output as below:
CLIENT_ID BEGIN_DT END_DT RESIDENTIAL_TYPE_DESC
A 19-Aug-00 18-Aug-02 SUPERVISED LIVING
A 19-Aug-02 24-Jul-03 RESIDENTIAL SUPPORT SERVICE
A 25-Jul-03 23-Jun-04 FOSTER/COMPANION CARE RESIDENTIAL COMPON
A 24-Jun-04 17-Aug-05 RESIDENTIAL SUPPORT SERVICE


--create table statement

CREATE TABLE FOR_MV_RESIDENCE_TYPE
( CLIENT_ID VARCHAR2(2 BYTE),
BEGIN_DT DATE,
REVISION_DT DATE,
END_DT DATE,
RESIDENTIAL_TYPE_DESC VARCHAR2(50 BYTE)
) ;


--Insert into table statement

Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-00','30-MAR-01','18-AUG-01','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-01','03-JUN-02','18-AUG-02','SUPERVISED LIVING');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-02','25-JUL-03','18-AUG-03','FOSTER/COMPANION CARE RESIDENCIAL');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','19-AUG-03','24-JUN-04','17-AUG-04','RESIDENCIAL SUPPORT SERVICE');
Insert into FOR_MV_RESIDENCE_TYPE values ('A','18-AUG-04','18-NOV-04','17-AUG-05','RESIDENCIAL SUPPORT SERVICE');


I want to have sql to achieve necessary output. Hope this helps understand scenario.
Thanks in advance
Jay

Re: grouping query question continued [message #199476 is a reply to message #199467] Tue, 24 October 2006 12:42 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You have had your quetions merged once already. Why are you starting a new thread once again?

http://www.orafaq.com/forum/t/71327/66800/
Re: grouping query question continued [message #199479 is a reply to message #199476] Tue, 24 October 2006 13:23 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member

I have a refined result set in my latest post.
I need to use LAG/LEAD function for next record set value and subtract one day from REVISION_DT column.

I would prefer to leave it seperate if you want to merge it that is not a problem.
icon9.gif  Re: grouping query (merged 3 threads) [message #199625 is a reply to message #199332] Wed, 25 October 2006 09:35 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
QUERY Is as below:

create materialized view mv_test_res1 as
SELECT client_id
, NVL(first_rev_dt, begin_dt) begin_dt
, NVL2(next_rev_dt, next_rev_dt-1, end_dt) end_dt
, res_type_desc
-- , r
-- , next_rev_dt
FROM (
SELECT client_id
, rt_begin_dt begin_dt
, end_dt
, FIRST_VALUE(revision_dt) OVER(PARTITION BY rt_begin_dt, res_type_desc ORDER BY lev) first_rev_dt
, res_type_desc
, r
, next_rev_dt
, RANK() OVER(PARTITION BY rt_begin_dt, res_type_desc ORDER BY lev DESC) rnk
FROM (
SELECT client_id
, begin_dt
, end_dt
, revision_dt
, res_type_desc
, r
, next_rev_dt
, level lev
, CONNECT_BY_ROOT begin_dt rt_begin_dt
FROM (
SELECT client_id
, begin_dt
, end_dt
, revision_dt
, residential_type_desc res_type_desc
, rownum r
, LAG(residential_type_desc) OVER(ORDER BY rownum) prev_res_type_desc
, LEAD(revision_dt) OVER(ORDER BY rownum) next_rev_dt
FROM STAGE_1_CARE_IPC)
CONNECT BY r = PRIOR r+1
AND res_type_desc = PRIOR res_type_desc
START WITH NVL(prev_res_type_desc, 'z') <> res_type_desc))
WHERE rnk = 1
ORDER BY r;

but that is for Sample table.
It has just 19 records in it now when I tried applied to the actual table (STAGE_1_CARE_IPC)
SQL> DESC STAGE_1_CARE_IPC;
Name Null? Type
----------------------------------------- -------- ------------------
CLIENT_ID VARCHAR2(9)
CARE_ID VARCHAR2(10)
CP_COMPONENT_CD VARCHAR2(4)
CDSA_COMPONENT_CD VARCHAR2(4)
CP_LOCAL_CASE_NUMBER VARCHAR2(10)
CDSA_LOCAL_CASE_NUMBER VARCHAR2(10)
SSN VARCHAR2(10)
CP_CONTRACT_NUMBER VARCHAR2(10)
CDSA_CONTRACT_NUMBER VARCHAR2(10)
BEGIN_DT DATE
REVISION_DT DATE
END_DT DATE
MHMR_SERVICE_COUNTY_CD VARCHAR2(3)
MHMR_SERVICE_GROUP_CD VARCHAR2(1)
MHMR_SERVICE_GROUP_DESC VARCHAR2(20)
LOCATION_CD VARCHAR2(5)
LOCATION_DESC VARCHAR2(30)
RESIDENTIAL_TYPE_CD VARCHAR2(1)
RESIDENTIAL_TYPE_DESC VARCHAR2(40)
IPC_ANNUAL_COST NUMBER(10,2)
IPC_AUTHORIZED_AMOUNT NUMBER(10,2)
SERVICES_BY_RELATIVE_YN VARCHAR2(1)
CASE_COORDINATOR VARCHAR2(50)


I have 181767 rows in it and I have group of clients here. Yesterday we did only for one client (19 records), now I have group of clients with groups of begin_dt and residential_type_Desc.

I want to have CLIENT_ID,BEGIN_DT,END_DT,RESIDENCE_TYPE_DESC,RESIDENCE_TYPE_CD columns as my target result.

RESIDENCE_TYPE_DESC Is the same groups as RESIDENCE_TYPE_CD so we don't have to partition it furthur on RESIDENCE_TYPE_CD


Can anyone help ?
Thanks in advance
Jay Parmar
Can some one help me writing sql [message #200663 is a reply to message #199332] Tue, 31 October 2006 16:58 Go to previous messageGo to next message
parmarjm
Messages: 25
Registered: October 2006
Junior Member
CREATE TABLE KTEST1
   (	BEGIN_DT DATE, 
	REVISION_DT DATE, 
	NEW_REVISION_DT DATE, 
	END_DT DATE, 
	MAX_IPC_COST NUMBER(10,2), 
	MAX_AUTHORIZED NUMBER(10,2), 
	SERVICE_CD VARCHAR2(5 BYTE), 
	DOLLARS NUMBER, 
	UNITS NUMBER, 
	SERVICE_UNITS_TYPE VARCHAR2(20 BYTE)
   ) ;
 







Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','CMM','0','12','MONS');
Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','DE','0','200','DOL');
Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','DI','0','2','HRS');
Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','NU','0','4','HRS');
Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','PS','0','3','HRS');
Insert into KTEST1  values ('07-JUL-04',null,'07-JUL-04','06-JUL-05','0','0','SHL','0','167','HRS');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','AA','320','320','DOL');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','CMM','13526.16','12','MONS');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','DE','200','200','DOL');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','DI','96.72','2','HRS');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','NU','343.63','6','HRS');
Insert into KTEST1  values ('07-JUL-04','19-NOV-04','19-NOV-04','06-JUL-05','17940.79','69050.7','SHL','3454.28','223','HRS');


select begin_dt,REVISION_DT,NEW_REVISION_DT, END_DT,service_cd
from ktest1:

BEGIN_DT  REVISION_ NEW_REVIS END_DT    SERVI
--------- --------- --------- --------- -----
07-JUL-04           07-JUL-04 06-JUL-05 CMM
07-JUL-04           07-JUL-04 06-JUL-05 DE
07-JUL-04           07-JUL-04 06-JUL-05 DI
07-JUL-04           07-JUL-04 06-JUL-05 NU
07-JUL-04           07-JUL-04 06-JUL-05 PS
07-JUL-04           07-JUL-04 06-JUL-05 SHL
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 AA
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 CMM
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 DE
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 DI
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 NU

BEGIN_DT  REVISION_ NEW_REVIS END_DT    SERVI
--------- --------- --------- --------- -----
07-JUL-04 19-NOV-04 19-NOV-04 06-JUL-05 SHL

12 rows selected.


I want the output as below:


BEGIN_DT    END_DT      SERVICE_CD
07-JUL-04   18-NOV-04    PS
19-NOV-04   06-JUL-05    AA
07-JUL-04   06-JUL-05    CMM
07-JUL-04   06-JUL-05    DE
07-JUL-04   06-JUL-05    NU
07-JUL-04   06-JUL-05    SHL
07-JUL-04   06-JUL-05    DI


code tags added by moderator to preserve formatting

[Updated on: Tue, 31 October 2006 18:34] by Moderator

Report message to a moderator

Re: Query Help [message #200738 is a reply to message #199364] Wed, 01 November 2006 02:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be fair to the OP, I suspect that the TO_DATE(date,'null') was caused by SQL Developer - I've had it do that to me when I created insert statements from it.
Previous Topic: problem on creation of table
Next Topic: Reg bind variable
Goto Forum:
  


Current Time: Mon Dec 05 15:19:52 CST 2016

Total time taken to generate the page: 0.08204 seconds