Monday, February 28, 2011

Oracle: Join Example

I have a table TEST in oracle which stores data for some users on monthly basis, I need to fetch the data in such a way that "all data for current month with the entry of their last month record for a particular column." Here is the table details and query:

CREATE TABLE DATETEST (ID NUMBER(4) NOT NULL, NAME VARCHAR2(10), BUDGET NUMBER(7), TDATE DATE);

INSERT INTO DATETEST VALUES (1, 'test1', 100, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 200, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (5, 'test5', 300, TO_DATE('01-FEB-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (1, 'test1', 200, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 100, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 400, TO_DATE('01-JAN-2011', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (2, 'test2', 200, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (3, 'test3', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));
INSERT INTO DATETEST VALUES (4, 'test4', 100, TO_DATE('01-DEC-2010', 'DD-MON-YYYY'));

select * from DATETEST;

# This Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0);

# Last Month's entry
select * from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1);

# Common users for current and last month and their budgets for current and last months
select A.ID, NAME, A.BUDGET, B.BUDGET from DATETEST A,
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
where A.ID=B.ID and TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) ;


# Query which displays data for all users with current month entry and thier last month record
select A.ID, NAME, A.BUDGET, NVL (B.BUDGET, 0)
from DATETEST A left join
(select ID, BUDGET from DATETEST where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),-1)) B
on A.ID=B.ID where TDATE = ADD_MONTHS(trunc(sysdate,'MM'),0) order by id asc;

No comments: