You Asked
Hi Team,
Hope you are doing well..
I have table like below
ACC_NO DATE_OPENED PRODUCT_TYPE
===================================
UN1 02-SEP-16 A
UN2 02-OCT-16 B
UN3 12-DEC-16 C
UN4 22-NOV-16 D
UN5 02-DEC-16 B
UN6 17-NOV-16 C
UN7 02-DEC-16 D
UN8 02-NOV-16 C
UN9 10-DEC-16 B
UN10 02-NOV-16 C
UN11 02-DEC-16 C
UN12 22-DEC-16 D
UN13 02-NOV-16 A
UN14 19-NOV-16 B
UN15 20-NOV-16 B
UN16 15-MAY-16 C
UN17 02-NOV-16 A
UN18 18-DEC-16 A
UN19 02-NOV-16 B
I want output counts BY YEAR,CURRENT MONTH,PREVIUOS MONTHS GROUP BY PRODUCT TYPE as below
PRODUCT YTD CURRENT_MONTH PREVIOUS_MONTH
=========================================================
A 3 1 2
B 7 2 3
C 5 2 3
D 4 2 1
Kindly help me to get this.
CODE TO CREATE THE TABLE AND FOR INSERTS
CREATE TABLE T11(ACCNO VARCHAR2(20),DATE_OPENED DATE, PRODUCT_TYPE VARCHAR2(5));
INSERT INTO T11 VALUES ('UN1',TO_DATE('02-SEP-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN2',TO_DATE('02-OCT-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN3',TO_DATE('12-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN4',TO_DATE('22-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN5',TO_DATE('02-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN6',TO_DATE('17-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN7',TO_DATE('02-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN8',TO_DATE('02-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN9',TO_DATE('10-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN10',TO_DATE('02-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN11',TO_DATE('02-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN12',TO_DATE('22-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN13',TO_DATE('02-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN14',TO_DATE('19-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN15',TO_DATE('20-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN16',TO_DATE('15-MAY-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN17',TO_DATE('02-NOV-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN18',TO_DATE('18-DEC-16','DD-MON-YYYY'),'A');
INSERT INTO T11 VALUES ('UN19',TO_DATE('02-NOV-16','DD-MON-YYYY'),'A');
and Connor said...
Well...your test case
a) has "A" for every row, and
b) doesnt work (because the format mask does not match the data)
but other than that... well....at least you gave us one (eventually) :-)
SQL> CREATE TABLE T11(ACCNO VARCHAR2(20),DATE_OPENED DATE, PRODUCT_TYPE VARCHAR2(5));
Table created.
SQL>
SQL>
SQL> INSERT INTO T11 VALUES ('UN1',TO_DATE('02-SEP-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN2',TO_DATE('02-OCT-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN3',TO_DATE('12-DEC-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN4',TO_DATE('22-NOV-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN5',TO_DATE('02-DEC-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN6',TO_DATE('17-NOV-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN7',TO_DATE('02-DEC-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN8',TO_DATE('02-NOV-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN9',TO_DATE('10-DEC-2016','DD-MON-YYYY'),'B');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN10',TO_DATE('02-NOV-2016','DD-MON-YYYY'),'C');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN11',TO_DATE('02-DEC-2016','DD-MON-YYYY'),'C');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN12',TO_DATE('22-DEC-2016','DD-MON-YYYY'),'C');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN13',TO_DATE('02-NOV-2016','DD-MON-YYYY'),'C');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN14',TO_DATE('19-NOV-2016','DD-MON-YYYY'),'C');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN15',TO_DATE('20-NOV-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN16',TO_DATE('15-MAY-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN17',TO_DATE('02-NOV-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN18',TO_DATE('18-DEC-2016','DD-MON-YYYY'),'A');
1 row created.
SQL> INSERT INTO T11 VALUES ('UN19',TO_DATE('02-NOV-2016','DD-MON-YYYY'),'A');
1 row created.
SQL>
SQL>
SQL> with t as
2 (
3 select
4 product_type,
5 date_opened,
6 trunc(date_opened,'MM') mth,
7 trunc(date_opened,'YYYY') yr
8 from t11
9 )
10 select
11 product_type,
12 count(case when yr = trunc(sysdate,'YYYY') then 1 end ) ytd,
13 count(case when mth = trunc(sysdate,'MM') then 1 end) mth,
14 count(case when mth = add_months(trunc(sysdate,'MM'),-1) then 1 end) prev_mth
15 from t
16 group by product_type;
PRODU YTD MTH PREV_MTH
----- ---------- ---------- ----------
A 8 2 3
B 6 3 3
C 5 2 3
3 rows selected.
SQL>
SQL>