Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query - 2nd Request Pls Help

RE: Query - 2nd Request Pls Help

From: Sdjaroud <sdjaroud_at_lth.sonatrach.dz>
Date: Wed, 21 Jun 2000 11:19:09 -0000
Message-Id: <10535.109950@fatcity.com>


Try this:

select identifier , purc_date, T1.purc_date FROM_DATE,T2.purc_date TO_DATE from CT T1, CT T2
where T1.identifier=T2.identifier
  and T1.date<=T2.date
  and not exist (select 'x' from CT T3
  where (T1.identifier=T3.identifier and (T3.purc_date=T2.purc_date+1 or T3.purc_date=T1.purc_date-1))   or (T1.identifier<>T3.identifier and (T3.date<T2.purc_date or T3.date>T1.purc_date);


De : 	salu Ullah[SMTP:salu_ullah_at_hotmail.com]
Repondre a : 	ORACLE-L_at_fatcity.com
Date :	mardi 20 juin 2000 21:06
A :	Multiple recipients of list ORACLE-L
Objet :	RE: Query - 2nd Request Pls Help

Hello,

yes, thats how i want them to show in my report. Unable to make a query that could generate the following output. Min & max function cant show the dates in between them for that particular status. Would appreciate any help.

Thanks

Salman

>From: "Guidry, Chris" <chris.guidry_at_atcoelectric.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Query - 2nd Request Pls Help
>Date: Tue, 20 Jun 2000 09:33:14 -0800
>
>Hi,
>Yes but shouldn't your sample output look more like this?
>
>cust_id item_code status from_date to_date
>0001 AAAA A 01-JAN-00 03-JAN-00
>0001 AAAA B 04-JAN-00 05-JAN-00
>0002 BBBB A 05-JAN-00 05-JAN-00
>0001 AAAA A 06-JAN-00 06-JAN-00
>0002 BBBB A 06-JAN-00 06-JAN-00
>0001 AAAA A 07-JAN-00 29-JAN-00
>0001 AAAA B 30-JAN-00 31-JAN-00
>
>You are overlapping dates that only have one entry.
>Is this what you want?
>
>-
>Chris J. Guidry P.Eng.
>ATCO Electric, Metering Services
>Phone: (780) 420-4142
>Fax: (780) 420-3854
>Email: chris.guidry_at_atcoelectric.com
>
> > -----Original Message-----
> > From: salu Ullah [SMTP:salu_ullah_at_hotmail.com]
> > Sent: Monday, June 19, 2000 3:04 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Query - 2nd Request Pls Help
> >
> > Hello David,
> >
> > I have to create a report that should show cust_id, item_code, its
>status
> > with from_date & to_date based on purc_date column. If u look at the
> > result
> > its showing the cust_id (0001), item_code (AAAA), from_date (01-JAN-00),
> > to_date (03-JAN-00) for status (A) & then with same cust_id, item_code,
> > but
> > then from_date & to_date is (04-JAN-00 05-JAN-00) because on these 2
> > days
> > the status was B.
> > I hope i answer ur question. I want to group them so that each cust_id
> > with
> > its item_code & status should show from & to date.
> >
> > Any help is appreciated.
> >
> > Salu
> >
> >
> >
> > >From: "Shockey, David" <DShockey_at_jwrinc.com>
> > >Reply-To: ORACLE-L_at_fatcity.com
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >Subject: RE: Query - 2nd Request Pls Help
> > >Date: Mon, 19 Jun 2000 09:31:30 -0800
> > >
> > >You'll have to give us the logic behind your desired results. As it
>is,
> > >you
> > >have lines that have the same cust_id, item_code, and status in the
> > >results.
> > >So, a simple grouping on these fields is not possible. What other
> > grouping
> > >criteria are you using?
> > >
> > >-----Original Message-----
> > >Sent: Monday, June 19, 2000 10:48 AM
> > >To: Multiple recipients of list ORACLE-L
> > >
> > >
> > >Hello
> > >
> > >I have a table with columns & data as follows
> > >
> > >cust_id item_code purc_date status
> > >
> > >0001 AAAA 01-JAN-00 A
> > >0001 AAAA 02-JAN-00 A
> > >0001 AAAA 03-JAN-00 A
> > >0001 AAAA 04-JAN-00 B
> > >0001 AAAA 05-JAN-00 B
> > >0002 BBBB 05-JAN-00 A
> > >0001 AAAA 06-JAN-00 A
> > >0002 BBBB 06-JAN-00 A
> > >0001 AAAA 07-JAN-00 A
> > >. . . .
> > >. . . .
> > >0001 AAAA 29-JAN-00 A
> > >0001 AAAA 30-JAN-00 B
> > >0001 AAAA 31-JAN-00 B
> > >
> > >I'am looking for a query that will give the folllowing result:
> > >
> > >
> > >cust_id item_code status from_date to_date
> > >0001 AAAA A 01-JAN-00 03-JAN-00
> > >0001 AAAA B 04-JAN-00 05-JAN-00
> > >0002 BBBB A 05-JAN-00 06-JAN-00
> > >0001 AAAA A 06-JAN-00 29-JAN-00
> > >0001 AAAA B 30-JAN-00 31-JAN-00
> > >
> > >
> > >I tried using min(purc_date) & max(purc_date) & grouping them with the
> > >remaining columns,
> > >but was not getting the desired output.
> > >
> > >Any input will be appreciated. Thanks in advance
> > >
> > >Salu
> > >
> >
> >________________________________________________________________________
> > >Get Your Private, Free E-mail from MSN Hotmail at
>http://www.hotmail.com
> > >
> > >--
> > >Author: salu Ullah
> > > INET: salu_ullah_at_hotmail.com
> > >
> > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > >San Diego, California -- Public Internet access / Mailing Lists
> > >--------------------------------------------------------------------
> > >To REMOVE yourself from this mailing list, send an E-Mail message
> > >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > >the message BODY, include a line containing: UNSUB ORACLE-L
> > >(or the name of mailing list you want to be removed from). You may
> > >also send the HELP command for other information (like subscribing).
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: salu Ullah
> > INET: salu_ullah_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>--
>Author: Guidry, Chris
> INET: chris.guidry_at_atcoelectric.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
-- 
Author: salu Ullah
  INET: salu_ullah_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Wed Jun 21 2000 - 06:19:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US