Home » SQL & PL/SQL » SQL & PL/SQL » Last 7 days report (oracle 11g)
Last 7 days report [message #671081] |
Wed, 15 August 2018 04:08 |
akash123
Messages: 46 Registered: May 2008 Location: india
|
Member |
|
|
Hi I am trying to write a query to see the count of records in last seven days only. If there is no data for one particular date then it should be displayed as 0. Please help.
My query is
SELECT TO_CHAR(TD_DATE,'DD-MON-RRRR'),SUM(REC_CNT)
FROM TEST_DATE WHERE TD_DATE >= TRUNC(SYSDATE)-6
GROUP BY TO_CHAR(TD_DATE,'DD-MON-RRRR')
ORDER BY TO_DATE(TO_CHAR(TD_DATE,'DD-MON-RRRR')) DESC
CREATE TABLE TEST_DATE
(
TD_DATE DATE,
REC_CNT NUMBER
)
SET DEFINE OFF;
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/15/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/14/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/08/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/07/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 23);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/15/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);
COMMIT;
Expected output is
15-Aug-18 12
14-Aug-18 4
13-Aug-18 0
12-Aug-18 9
11-Aug-18 0
10-Aug-18 8
9-Aug-18 0
|
|
|
Last 7 days report [message #671082 is a reply to message #671081] |
Wed, 15 August 2018 04:09 |
akash123
Messages: 46 Registered: May 2008 Location: india
|
Member |
|
|
Hi I am trying to write a query to see the count of records in last seven days only. If there is no data for one particular date then it should be displayed as 0. Please help.
My query is
SELECT TO_CHAR(TD_DATE,'DD-MON-RRRR'),SUM(REC_CNT)
FROM TEST_DATE WHERE TD_DATE >= TRUNC(SYSDATE)-6
GROUP BY TO_CHAR(TD_DATE,'DD-MON-RRRR')
ORDER BY TO_DATE(TO_CHAR(TD_DATE,'DD-MON-RRRR')) DESC
CREATE TABLE TEST_DATE
(
TD_DATE DATE,
REC_CNT NUMBER
)
SET DEFINE OFF;
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/15/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/14/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/08/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 9);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/07/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 23);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into TEST_DATE
(TD_DATE, REC_CNT)
Values
(TO_DATE('08/15/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 10);
COMMIT;
Expected output is
15-Aug-18 12
14-Aug-18 4
13-Aug-18 0
12-Aug-18 9
11-Aug-18 0
10-Aug-18 8
9-Aug-18 0
|
|
|
Re: Last 7 days report [message #671086 is a reply to message #671082] |
Wed, 15 August 2018 05:19 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Create an inline view (or reference a calendar table if you have one) that lists all dates required and outer join to yoiur resultset:
select dt.dte
, sum(nvl(rec_cnt, 0))
from
(select trunc(sysdate) + 1 - level dte
from dual
connect by level <= 7
) dt
left join test_date
on dt.dte = test_date.td_date
where dt.dte >= trunc(sysdate) - 6
group by dt.dte
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 16 08:01:46 CDT 2024
|