Home » SQL & PL/SQL » SQL & PL/SQL » get the data between the dates (win 7, oracle 11G)
get the data between the dates [message #608336] |
Tue, 18 February 2014 17:15 |
|
nischalinn
Messages: 118 Registered: May 2012 Location: nepal
|
Senior Member |
|
|
I've a test table,
create table temp
(
userid varchar2(10),
cartid varchar2(10),
reserved_date date,
delivery_date date,
destiny varchar2(20)
);
values in the table are:
INSERT INTO TEMP VALUES ('UTO77','DIAM0999',TO_DATE('01-01-2014', 'MM-DD-YYYY',TO_DATE('02-08-2014', 'MM-DD-YYYY','FRANCE');
INSERT INTO TEMP VALUES ('ANG156','CUST835',TO_DATE('01-20-2014', 'MM-DD-YYYY',TO_DATE('02-02-2014', 'MM-DD-YYYY','USA');
INSERT INTO TEMP VALUES ('BEN672','BOOK100',TO_DATE('12-29-2013', 'MM-DD-YYYY',TO_DATE('03-18-2014', 'MM-DD-YYYY','CHINA');
INSERT INTO TEMP VALUES ('NEL156','TOY0999',TO_DATE('01-01-2014', 'MM-DD-YYYY',TO_DATE('02-08-2014', 'MM-DD-YYYY','ITALY');
INSERT INTO TEMP VALUES ('ANG156','UTE599',TO_DATE('01-20-2014', 'MM-DD-YYYY',TO_DATE('02-02-2014', 'MM-DD-YYYY','CANADA');
INSERT INTO TEMP VALUES ('UTO77','GOLD111',TO_DATE('01-01-2014', 'MM-DD-YYYY',TO_DATE('04-18-2014', 'MM-DD-YYYY','RUSSIA');
How can I find the users with their corresponding CARTID having multiple CARTID between the same RESERVED_DATE and DELIVERY_DATE. In this case the user ANG156 is having multiple CARTID b/n the same RESERVED_DATE and DELIVERY_DATE.
[Updated on: Tue, 18 February 2014 17:17] Report message to a moderator
|
|
|
|
|
|
|
|
Re: get the data between the dates [message #608342 is a reply to message #608341] |
Tue, 18 February 2014 17:54 |
|
nischalinn
Messages: 118 Registered: May 2012 Location: nepal
|
Senior Member |
|
|
@ BlackSwan, I've corrected the statements. Sorry for the inconvenience.
INSERT INTO TEMP VALUES ('UTO77','DIAM0999',TO_DATE('01-01-2014', 'MM-DD-YYYY'),TO_DATE('02-08-2014', 'MM-DD-YYYY'),'FRANCE');
INSERT INTO TEMP VALUES ('ANG156','CUST835',TO_DATE('01-20-2014', 'MM-DD-YYYY'),TO_DATE('02-02-2014', 'MM-DD-YYYY'),'USA');
INSERT INTO TEMP VALUES ('BEN672','BOOK100',TO_DATE('12-29-2013', 'MM-DD-YYYY'),TO_DATE('03-18-2014', 'MM-DD-YYYY'),'CHINA');
INSERT INTO TEMP VALUES ('NEL156','TOY0999',TO_DATE('01-01-2014', 'MM-DD-YYYY'),TO_DATE('02-08-2014', 'MM-DD-YYYY'),'ITALY');
INSERT INTO TEMP VALUES ('ANG156','UTE599',TO_DATE('01-20-2014', 'MM-DD-YYYY'),TO_DATE('02-02-2014', 'MM-DD-YYYY'),'CANADA');
INSERT INTO TEMP VALUES ('UTO77','GOLD111',TO_DATE('01-01-2014', 'MM-DD-YYYY'),TO_DATE('04-18-2014', 'MM-DD-YYYY'),'RUSSIA');
Thank you for the suggestion.
|
|
|
|
Re: get the data between the dates [message #608361 is a reply to message #608343] |
Wed, 19 February 2014 02:34 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Or
SELECT * from temp a
WHERE EXISTS (SELECT null
FROM temp b
WHERE a.cartid != b.cartid
AND a.userid = b.userid
AND a.reserved_date = b.reserved_date
AND a.delivery_date = b.delivery_date
|
|
|
Goto Forum:
Current Time: Fri Mar 29 09:16:32 CDT 2024
|