Geek Answers Handbook

SQL query for all days of the month

I have the following RENTAL table (book_date, copy_id, member_id, title_id, act_ret_date, exp_ret_date). Where book_date shows the day the book was booked. I need to write a request that for each day of the month (from 1-30 or 1-29, or from 1-31 depending on the month) shows me the number of books booked.
I currently know how to show the number of books rented on the days that are in the table

select count(book_date), to_char(book_date,'DD')
from rental
group by to_char(book_date,'DD');

my questions:

  • How do I show the rest of the days (for example, for some reason, in my database I do not have books rented 20 or 19 or several days) and enter the number 0 there?
  • How to show the number of days of the current month only (28,29,30,31 all 4 of these are possible depending on the month or year) ... I'm lost. This should be done using only the no pl / SQL query or other material.
+4
source share
4 answers

The following query will give you all the days in the current month, in your case you can replace SYSDATEthe dates with your column and join this query to find out how much for this month

SELECT DT
FROM(
SELECT TRUNC (last_day(SYSDATE) - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 32
  )
  where DT >= trunc(sysdate,'mm')
+3
source

The answer is to create a table as follows:

table yearsmonthsdays (year varchar (4), month varchar (2), day varchar (2));

, , . java Calendar.getInstance(). getActualMaximum (Calendar.DAY_OF_MONTH), , , , 1 .

- :

insert into yearsmonthsdays ('1995','02','01');
insert into yearsmonthsdays ('1995','02','02');
...
insert into yearsmonthsdays ('1995','02','28'); /* non-leap year */
...
insert into yearsmonthsdays ('1996','02','01');
insert into yearsmonthsdays ('1996','02','02');
...
insert into yearsmonthsdays ('1996','02','28'); 
insert into yearsmonthsdays ('1996','02','29'); /* leap year */
...

..

, . , , , , , . .

+1

oracle, dual conncect by level - - . , ( 2014 ) :

SELECT    all_date, COALESCE (cnt, 0)
FROM      (SELECT to_date('01/01/2014', 'dd/mm/yyyy') + rownum - 1 AS all_date
           FROM   dual
           CONNECT BY LEVEL <= 365) d
LEFT JOIN (SELECT   TRUNC(book_date), COUNT(book_date) AS cnt
           FROM     rental
           GROUP BY book_date) r ON d.all_date = TRUNC(r.book_date)
+1

No need to attract ROWNUM... you can just use LEVELin CONNECT BY:

WITH d1 AS (
     SELECT TRUNC(SYSDATE, 'MONTH') - 1 + LEVEL AS book_date
       FROM dual
    CONNECT BY TRUNC(SYSDATE, 'MONTH') - 1 + LEVEL <= LAST_DAY(SYSDATE)
)
SELECT TRUNC(d1.book_date), COUNT(r.book_date)
  FROM d1 LEFT JOIN rental r
    ON TRUNC(d1.book_date) = TRUNC(r.book_date)
 GROUP BY TRUNC(d1.book_date);

Just replace SYSDATEwith the date of the month you are targeting the results.

0
source

More articles:


All Articles