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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2881
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
Re: Last 7 days report [message #671087 is a reply to message #671086] Wed, 15 August 2018 06:12 Go to previous messageGo to next message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
Thank you Poblolee...
Re: Last 7 days report [message #671088 is a reply to message #671081] Wed, 15 August 2018 06:16 Go to previous messageGo to next message
EdStevens
Messages: 1139
Registered: September 2013
Senior Member
akash123 wrote on Wed, 15 August 2018 04:08
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


Not relevant to your question, but you need to quit using the 'RRRR' mask when dealing with dates. That was created as a temporary fix, to buy time in dealing with the Y2K issue. And that was 20 years ago!
Re: Last 7 days report [message #671089 is a reply to message #671088] Wed, 15 August 2018 06:24 Go to previous message
akash123
Messages: 46
Registered: May 2008
Location: india
Member
ok sure, I will do..
Previous Topic: XML Special Character
Next Topic: failing in Creation of PL/SQL function
Goto Forum:
  


Current Time: Sat Nov 16 15:56:02 CST 2019