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
|
|
|
|
Current Time: Tue Apr 23 23:56:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
