(elegantly) Querying for number of matching days in JPQL

In one of my projects I have the following situation:

I have "Availability Objects" that have a starting date and an end date. Users query for these by defining a date range of their own.

The c开发者_如何学编程ustomer would basically like to output all Availabilities and sort them by the number of days that are matching in the date range of the availability and the users date range.

For example, if I have two Availabilities, Availability 1 from January 1st 2012 till January 31th 2012 and Availability 2 from January 1st 2012 till January 15th 2012 and the user queries for "Availabilities between Jan 1st 2012 and Jan 31th 2012" the result should be:

Availability   | numberOfDaysMatching
Availability 1 | 31
Availability 2 | 15

I cannot really wrap my head around how I would query for this without denormalizing the data. As the Persistence API I am using JPA, provided by Hibernate (3.6ish I think).

Right now my solution is to denormalize the Days an Availability is in, by calculating them on every store / update and basically storing each day in a seperate table. This way I can query for the wanted result by basically computing the days within the users query, looking them up in my days table and then querying for Availabilities where one of their Days is IN(:mycalculateddays).

Is there some non-monstrous way to query this with plain JQPL that does not denormalize the data like I did?

---

Querying overlapping range itself is of course easy (assuming that ranges are valid(start <= end for both):

SELECT r
FROM Range r 
WHERE  r.startDate <= :endDate and :startDate <= r.endDate

But you cannot construct a query that computes difference between two Dates, because JPQL does not support arithmetic operations on Date. Also there is no built in functions that would help.

---

Would this work?

select *
from availabilities a
where
-- This is really least(a.end_date, :range_end) - greatest(a.start_date, :range_start)
-- I don't know if JPQL defines those functions so I implement it with CASE
(case when a.end_date <= :range_end then a.end_date else :range_end end) -
(case when a.start_date >= :range_start then a.start_date else :range_start end) > 0
order by 
(case when a.end_date <= :range_end then a.end_date else :range_end end) -
(case when a.start_date >= :range_start then a.start_date else :range_start end) desc