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 Go to next message
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 #608337 is a reply to message #608336] Tue, 18 February 2014 17:26 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Join the table to itself, matching all the columns you want to be the same.
Re: get the data between the dates [message #608338 is a reply to message #608336] Tue, 18 February 2014 17:28 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
INSERT statement are NOT valid syntax.
You should ALWAYS test SQL before posting it here.
multiple mis-matched parenthesis
Re: get the data between the dates [message #608339 is a reply to message #608338] Tue, 18 February 2014 17:43 Go to previous messageGo to next message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior Member
sorry for the inconvenience.
Please can you make the reply. I am confused how to get the desired result.

From the next time I'll check all the queries before posting.
Re: get the data between the dates [message #608340 is a reply to message #608337] Tue, 18 February 2014 17:45 Go to previous messageGo to next message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior Member
thank you cookiemonster .

Please can you elaborate your reply more. I am confused with the reply.

Re: get the data between the dates [message #608341 is a reply to message #608339] Tue, 18 February 2014 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>From the next time I'll check all the queries before posting.
next time You might get specific SQL
Re: get the data between the dates [message #608342 is a reply to message #608341] Tue, 18 February 2014 17:54 Go to previous messageGo to next message
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 #608343 is a reply to message #608342] Tue, 18 February 2014 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
SQL> SELECT reserved_date rd, 
       delivery_date dd, 
       userid, 
       Count(userid) 
FROM   temp 
HAVING Count(userid) > 1 
GROUP  BY reserved_date, 
          delivery_date, 
          userid   2    3    4    5    6    7    8    9  
 10  /

RD        DD        USERID     COUNT(USERID)
--------- --------- ---------- -------------
20-JAN-14 02-FEB-14 ANG156                 2


Re: get the data between the dates [message #608361 is a reply to message #608343] Wed, 19 February 2014 02:34 Go to previous message
cookiemonster
Messages: 11285
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

Previous Topic: find latest ddl statement happened in table
Next Topic: Exception while creating table using EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Thu Dec 18 16:09:16 CST 2014

Total time taken to generate the page: 0.12068 seconds