Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL query for all the days of a month

Tags:

sql

oracle

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.
like image 726
Lucian Tarna Avatar asked Mar 18 '23 06:03

Lucian Tarna


1 Answers

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') 
like image 196
Jafar Kofahi Avatar answered Mar 21 '23 13:03

Jafar Kofahi