Click here to Skip to main content
15,031,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table Movies and another table reservationOfMovie.
ReservationsOfMovie has a column dateOfReservation (which is a DATE)

I want to show both the amount of movies which are currently reserved and the amount of those which are available.
What I'm trying to do is to select the count of records where the
dateOfReservation
is earlier or after the current date, but I can't do such operation.

What I have tried:

DECLARE @Today DATE = GETDATE();

SELECT COUNT (r.dateOfReservation< @Today ) AS 'Available',
       COUNT (r.dateOfReservation> @Today ) AS 'notAvailable'
FROM Movies m
INNER JOIN ReservationsOfMovie r
ON r.IDMovie = m.IDMovie
Posted
Updated 5-Nov-20 1:05am
v2

1 solution

SQL
DECLARE @Today date = GETDATE();

-- Currently reserved:
SELECT Count(DISTINCT IDMovie)
FROM ReservationsOfMovie
WHERE @Today Between StartDate And EndDate;

-- Currently available:
SELECT Count(*)
FROM Movies As m
WHERE Not Exists
(
    SELECT 1
    FROM ReservationsOfMovie As r
    WHERE r.IDMovie = m.IDMovie
    And @Today Between r.StartDate And r.EndDate
);
   
Comments
xhon 5-Nov-20 7:13am
   
Hi, thanks for your answer. How can I put both in one select?
Richard Deeming 5-Nov-20 7:14am
   
Use sub-queries:
SELECT
     (SELECT ...) As CurrentlyReserved,
     (SELECT ...) As CurrentlyAvailable
;
xhon 5-Nov-20 8:19am
   
what if each movie has N copis and I want to show the director, the total number of copies, the number of available ones and the number of copies which are currently reserved?
I'm trying to do this:

SELECT
Director
Number_of_copies, (
SELECT
Number_of_copies
FROM
Movies m
INNER JOIN
ReservationsOfMovies r
ON
r.IDMovie = m.IDMovie
WHERE
@Today BETWEEN StartDate AND EndDate

)
AS Currently_Reserved,

(
SELECT
Quantity
FROM
Movies m
INNER JOIN
ReservationsOfMovies r
ON
r.IDMovies = m.IDMovies
WHERE
@Current_Date > EndDate

)
AS Currently_Available

FROM Movies
GROUP BY Drector, Quantity
Richard Deeming 5-Nov-20 10:10am
   
Something like this:
SELECT
    Director,
    Sum(Number_of_copies) As TotalCopies,
    Sum(SELECT Count(1) FROM ReservationsOfMovies As r WHERE r.IDMovie = m.IDMovie And @Today Between r.StartDate And r.EndDate)) As TotalReserved,
    Sum(Number_of_copies) - Sum(SELECT Count(1) FROM ReservationsOfMovies As r WHERE r.IDMovie = m.IDMovie And @Today Between r.StartDate And r.EndDate)) As TotalAvailable
FROM
    Movies As m
GROUP BY
    Director
;
xhon 5-Nov-20 11:38am
   
Than you very much. In your opinion could I obtain the same result by using an outer join?
Richard Deeming 5-Nov-20 11:42am
   
Probably, but you'd need to join to a sub-query:
SELECT
    m.Director,
    Sum(m.Number_of_copies) As TotalCopies,
    Sum(r.TotalReserved) As TotalReserved,
    Sum(m.Number_of_copies) - Sum(r.TotalReserved) As TotalAvailable
FROM
    Movies As m
    OUTER JOIN
    (
        SELECT IDMovie, Count(1) As TotalReserved
        FROM ReservationsOfMovies
        WHERE @Today Between StartDate And EndDate
        GROUP BY IDMovie
    ) As r
    ON r.IDMovie = m.IDMovie
GROUP BY
    m.Director
;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900