CODEWITHSUNDEEP
sqloracle-database
Arcadiaen
Arcadiaen

Reputation: 33

SQL In Oracle - How to search through occurrences in an interval?

I've gotten myself stuck working in Oracle with SQL for the first time. In my library example, I need to make a query on my tables for a library member who has borrowed more than 5 books in some week during the past year. Here's my attempt: 

SELECT 
PN.F_NAME,
PN.L_NAME,
M.ENROLL_DATE,
COUNT(*) AS BORROWED_COUNT,
(SELECT 
    (BD.DATE_BORROWED + INTERVAL '7' DAY)
    FROM DUAL, BORROW_DETAILS BD 
    GROUP BY BD.DATE_BORROWED + INTERVAL '7' DAY
    HAVING COUNT(*) > 5
) AS VALID_INTERVALS
FROM PERSON_NAME PN, BORROW_DETAILS BD, HAS H, MEMBER M
WHERE 
    PN.PID = M.PID AND
    M.PID = BD.PID AND
    BD.BORROWID = H.BORROWID

GROUP BY PN.F_NAME, PN.L_NAME, M.ENROLL_DATE, DATEDIFF(DAY, BD.DATE_RETURNED, VALID_INTERVALS)
ORDER BY BORROWED_COUNT DESC;

As I'm sure you can tell, Im really struggling with the Dates in oracle. For some reason DATEDIFF wont work at all for me, and I cant find any way to evaluate the VALID_INTERVAL which should be another date...

Also apologies for the all caps.

Upvotes: 0

Views: 70

Answers (1)

MT0
MT0

Reputation: 167982

DATEDIFF is not a valid function in Oracle; if you want the difference then subtract one date from another and you'll get a number representing the number of days (or fraction thereof) between the values.

If you want to count it for a week starting from Midnight Monday then you can TRUNCate the date to the start of the ISO week (which will be Midnight of the Monday of that week) and then group and count:

SELECT MAX( PN.F_NAME ) AS F_NAME,
       MAX( PN.L_NAME ) AS L_NAME,
       MAX( M.ENROLL_DATE ) AS ENROLL_DATE,
       TRUNC( BD.DATE_BORROWED, 'IW' ) AS monday_of_iso_week,
       COUNT(*) AS BORROWED_COUNT
FROM   PERSON_NAME PN
       INNER JOIN MEMBER M
       ON ( PN.PID = M.PID )
       INNER JOIN BORROW_DETAILS BD
       ON ( M.PID = BD.PID )
GROUP BY
       PN.PID,
       TRUNC( BD.DATE_BORROWED, 'IW' )
HAVING COUNT(*) > 5
ORDER BY BORROWED_COUNT DESC;

db<>fiddle

You haven't given your table structures or any sample data so its difficult to test; but you don't appear to need to include the HAS table and I'm assuming there is a 1:1 relationship between person and member.

You also don't want to GROUP BY names as there could be two people with the same first and last name (who happened to enrol on the same date) and should use something that uniquely identifies the person (which I assume is PID).

Upvotes: 2

Related Questions