| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query - 2nd Request Pls Help
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFDAC2.66950FBE
Content-Type: text/plain;
charset="iso-8859-1"
The following is the result that you posted. The only difference that I see between lines 1 vs 4 and lines 2 vs 5 is the from_date and to_date; cust_id, item_code, and status are identical between these lines.
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
But using your instructions, I think this will do it.
select cust_id, item_code, status, min(purchase_date) mnpd,
max(purchase_date) mxpd
from purchases
group by cust_id, item_code, status
-----Original Message-----
From: salu Ullah [mailto:salu_ullah_at_hotmail.com]
Sent: Monday, June 19, 2000 4: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).
-- 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). ------_=_NextPart_001_01BFDAC2.66950FBE Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>RE: Query - 2nd Request Pls Help</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>The following is the result that you posted. = The only difference that I see between lines 1 vs 4 and lines 2 vs 5 is = the from_date and to_date; cust_id, item_code, and status are identical = between these lines.</FONT></P> <P><FONT SIZE=3D2>cust_id item_code status = from_date to_date</FONT> <BR><FONT SIZE=3D2>0001 = AAAA = A 01-JAN-00 03-JAN-00</FONT> <BR><FONT SIZE=3D2>0001 = AAAA = B 04-JAN-00 05-JAN-00</FONT> <BR><FONT SIZE=3D2>0002 = BBBB = A 05-JAN-00 06-JAN-00</FONT> <BR><FONT SIZE=3D2>0001 = AAAA = A 06-JAN-00 29-JAN-00</FONT> <BR><FONT SIZE=3D2>0001 = AAAA = B 30-JAN-00 31-JAN-00</FONT> </P> <P><FONT SIZE=3D2>But using your instructions, I think this will do = it.</FONT> </P> <P><FONT SIZE=3D2>select cust_id, item_code, status, min(purchase_date) = mnpd, max(purchase_date) mxpd</FONT> <BR><FONT SIZE=3D2>from purchases</FONT> <BR><FONT SIZE=3D2>group by cust_id, item_code, status</FONT> </P> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: salu Ullah [<A = HREF=3D"mailto:salu_ullah_at_hotmail.com">mailto:salu_ullah_at_hotmail.com</A>= ]</FONT> <BR><FONT SIZE=3D2>Sent: Monday, June 19, 2000 4:04 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: RE: Query - 2nd Request Pls Help</FONT> </P> <BR> <P><FONT SIZE=3D2>Hello David,</FONT> </P> <P><FONT SIZE=3D2>I have to create a report that should show cust_id, = item_code, its status </FONT> <BR><FONT SIZE=3D2>with from_date & to_date based on purc_date = column. If u look at the result </FONT> <BR><FONT SIZE=3D2>its showing the cust_id (0001), item_code (AAAA), = from_date (01-JAN-00), </FONT> <BR><FONT SIZE=3D2>to_date (03-JAN-00) for status (A) & then with = same cust_id, item_code, but </FONT> <BR><FONT SIZE=3D2>then from_date & to_date is = (04-JAN-00 05-JAN-00) because on these 2 days </FONT> <BR><FONT SIZE=3D2>the status was B.</FONT> <BR><FONT SIZE=3D2>I hope i answer ur question. I want to group them so = that each cust_id with </FONT> <BR><FONT SIZE=3D2>its item_code & status should show from & to = date.</FONT> </P> <P><FONT SIZE=3D2>Any help is appreciated.</FONT> </P> <P><FONT SIZE=3D2>Salu</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>>From: "Shockey, David" = <DShockey_at_jwrinc.com></FONT> <BR><FONT SIZE=3D2>>Reply-To: ORACLE-L_at_fatcity.com</FONT> <BR><FONT SIZE=3D2>>To: Multiple recipients of list ORACLE-L = <ORACLE-L_at_fatcity.com></FONT> <BR><FONT SIZE=3D2>>Subject: RE: Query - 2nd Request Pls Help</FONT> <BR><FONT SIZE=3D2>>Date: Mon, 19 Jun 2000 09:31:30 -0800</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>You'll have to give us the logic behind your = desired results. As it is, </FONT> <BR><FONT SIZE=3D2>>you</FONT> <BR><FONT SIZE=3D2>>have lines that have the same cust_id, = item_code, and status in the </FONT> <BR><FONT SIZE=3D2>>results.</FONT> <BR><FONT SIZE=3D2>>So, a simple grouping on these fields is not = possible. What other grouping</FONT> <BR><FONT SIZE=3D2>>criteria are you using?</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>>Sent: Monday, June 19, 2000 10:48 AM</FONT> <BR><FONT SIZE=3D2>>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Hello</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>I have a table with columns & data as = follows</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>cust_id item_code purc_date = status</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 01-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 02-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 03-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 04-JAN-00 = B</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 05-JAN-00 = B</FONT> <BR><FONT SIZE=3D2>>0002 = BBBB 05-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 06-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0002 = BBBB 06-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 07-JAN-00 = A</FONT> <BR><FONT = SIZE=3D2>>. = . = . .</FONT> <BR><FONT = SIZE=3D2>>. = . = . .</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 29-JAN-00 = A</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 30-JAN-00 = B</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA 31-JAN-00 = B</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>I'am looking for a query that will give the = folllowing result:</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>cust_id item_code status = from_date to_date</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA = A 01-JAN-00 03-JAN-00</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA = B 04-JAN-00 05-JAN-00</FONT> <BR><FONT SIZE=3D2>>0002 = BBBB = A 05-JAN-00 06-JAN-00</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA = A 06-JAN-00 29-JAN-00</FONT> <BR><FONT SIZE=3D2>>0001 = AAAA = B 30-JAN-00 31-JAN-00</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>I tried using min(purc_date) & = max(purc_date) & grouping them with the</FONT> <BR><FONT SIZE=3D2>>remaining columns,</FONT> <BR><FONT SIZE=3D2>>but was not getting the desired output.</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Any input will be appreciated. Thanks in = advance</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Salu</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT = SIZE=3D2>>___________________________________________________________= _____________</FONT> <BR><FONT SIZE=3D2>>Get Your Private, Free E-mail from MSN Hotmail = at <A HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A></FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>--</FONT> <BR><FONT SIZE=3D2>>Author: salu Ullah</FONT> <BR><FONT SIZE=3D2>> INET: salu_ullah_at_hotmail.com</FONT> <BR><FONT SIZE=3D2>></FONT> <BR><FONT SIZE=3D2>>Fat City Network Services -- = (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>>-----------------------------------------------------------= ---------</FONT> <BR><FONT SIZE=3D2>>To REMOVE yourself from this mailing list, send = an E-Mail message</FONT> <BR><FONT SIZE=3D2>>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>>the message BODY, include a line containing: = UNSUB ORACLE-L</FONT> <BR><FONT SIZE=3D2>>(or the name of mailing list you want to be = removed from). You may</FONT> <BR><FONT SIZE=3D2>>also send the HELP command for other information = (like subscribing).</FONT> </P> <P><FONT = SIZE=3D2>_______________________________________________________________= _________</FONT> <BR><FONT SIZE=3D2>Get Your Private, Free E-mail from MSN Hotmail at <A = HREF=3D"http://www.hotmail.com" = TARGET=3D"_blank">http://www.hotmail.com</A></FONT> </P> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: salu Ullah</FONT> <BR><FONT SIZE=3D2> INET: salu_ullah_at_hotmail.com</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =Received on Tue Jun 20 2000 - 09:06:35 CDT
 |
 |