CODEWITHSUNDEEP
sqloracle-database
Lucian Tarna
Lucian Tarna

Reputation: 1827

SQL query for all the days of a month

i have the following table RENTAL(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 query that for every day of the month(so from 1-30 or from 1-29 or from 1-31 depending on month) it shows me the number of books booked.
i currently know how to show the number of books rented in 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 are:

  1. How do i show the rest of the days(if let's say for some reason in my database i have no books rented on 20th or 19th or multiple days) and put the number 0 there?
  2. How do i show the number of days only of the current month so(28,29,30,31 all these 4 are possible depending on month or year)... i am lost . This must be done using only SQL query no pl/SQL or other stuff.

Upvotes: 4

Views: 14851

Answers (7)

Dmytro A
Dmytro A

Reputation: 1

In Oracle SQL the query must look like this to not miss the last day of month:

SELECT DT
FROM(
SELECT trunc(add_months(sysdate, 1),'MM')- ROWNUM dt
FROM DUAL CONNECT BY ROWNUM < 32
)
where DT >= trunc(sysdate,'mm')

Upvotes: 0

Javier La Chira
Javier La Chira

Reputation: 1

It did works to me:

SELECT DT
  FROM (SELECT TRUNC(LAST_DAY(SYSDATE) - (CASE WHEN ROWNUM=1 THEN 0 ELSE ROWNUM-1 END)) DT
           FROM DUAL
         CONNECT BY ROWNUM <= 32)
 WHERE DT >= TRUNC(SYSDATE, 'MM')

Upvotes: 0

bergee
bergee

Reputation: 121

All days of the month based on current date

select trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1)+level-1 x from dual connect by level <= TO_CHAR(LAST_DAY(sysdate),'DD')

Upvotes: 0

David Faber
David Faber

Reputation: 12486

There's no need to get ROWNUM involved ... you can just use LEVEL in the 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);

Simply replace SYSDATE with a date in the month you're targeting for results.

Upvotes: 1

Jafar Kofahi
Jafar Kofahi

Reputation: 763

The following query would give you all days in the current month, in your case you can replace SYSDATE with your date column and join with this query to know how many for a given month

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

Upvotes: 6

Mureinik
Mureinik

Reputation: 311143

In , you can query from dual and use the conncect by level syntax to generate a series of rows - in your case, dates. From there on, it's just a matter of deciding what dates you want to display (in my example I used all the dates from 2014) and joining on your table:

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)

Upvotes: 1

Luis Matos
Luis Matos

Reputation: 365

The answer is to create a table like this:

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

use any language you wish, e.g. iterate in java with Calendar.getInstance().getActualMaximum(Calendar.DAY_OF_MONTH) to get the last day of the month for as many years and months as you like, and fill that table with the year, month and days from 1 to last day of month of your result.

you'd get something like:

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 */
...

and so on.

Once you have this table done, your work is almost finished. Make an outer left join between your table and this table, joining year, month and day together, and when no lines appear, the count will be zero as you wish. Without using programming, this is your best bet.

Upvotes: 1

Related Questions