CODEWITHSUNDEEP
sqlsql-serverssms
user10455210
user10455210

Reputation: 9

Returning values based on several conditions in SQL server

I have a simple table which contains 4 columns:

  1. order id which is unique and can't be null
  2. movie id which can't be null
  3. rent_date which can't be null
  4. return_date which can be null

I'm trying to return all movie ids which have at-least 1 of two conditions:

  1. the same movie was ordered twice on the same date (as long as the return date isn't null)
  2. the same movie was ordered on the same date as it was returned (2 different order ids - the rent date of the first movie id is the return date of the second movie id)

The table looks something like this: 

order_id    movie_id    rent_date   return_date
1           1           2014-07-17  NULL
2           1           2014-07-18  NULL
3           1           2014-07-19  2014-07-17
4           2           2014-07-17  2014-07-18
5           2           2014-07-17  2014-07-18
6           3           2014-07-17  NULL
7           3           2014-07-18  2014-07-19

I want to return the following movie IDs:

Any help would be appreciated.

Upvotes: 0

Views: 93

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can try like below

    select t1.rent_date,t1.movie_id  from t t1 join t t2 
     on t1.rent_date=t2.return_date and t1.movie_id=t2.movie_id and t1.order_id!=t2.order_id
     union
select t1.rent_date, t1.movie_id  from t t1
 where t1.return_date IS NOT NULL
  group by t1.rent_date,t1.movie_id
  having count(*)>1

Upvotes: 1

incomudro
incomudro

Reputation: 558

1) the same movie was ordered twice on the same date (as long as the return date isn't null)

This can be done by grouping on movie-id and rent-date and adding the having argument (HAVING COUNT(*) >= 1)

2) The same movie was ordered on the same date as it was returned (2 different order ids - the rent date of the first movie id is the return date of the second movie id)

This can be done by joining the table on itself and add two conditions to the join statement. tabele1.movie_id = table2.movie_id And tabele1.rent_date = tabele2.return_date. Also add that both order-ids should be different

This will result in two tables of which you can collect the movie_id and rent-date. If you combine the two tables with a UNION (not a UNION ALL), you get the unique results.

Out of my head, it will result into something like the code below. 

SELECT movie_id, rent_date 
FROM movies table1
JOIN movies table2 ON tabele1.movie_id = table2.movie_id 
                   AND tabele1.rent_date = tabele2.rent_date 
GROUP BY movie_id, rent_date    
HAVING COUNT(*) > 1

UNION

SELECT movie_id, rent_date 
FROM movies table1
JOIN movies table2 ON tabele1.movie_id = table2.movie_id 
                   AND tabele1.rent_date = tabele2.return_date
                   AND tabele1.order_id <> tabele2.order_id
GROUP BY movie_id, rent_date

To get the unique movie-ids, you can use this as a CTE or Inner-Query and add a GROUP BY on movie_id, rent_date.

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

Create the Simple Table

declare @simple table
(
    order_id    int,
    movie_id    int,
    rent_date   date,
    return_date date
)

And some simple data

insert into @simple values
(1, 1, '2014-07-17', NULL),
(2, 1, '2014-07-18', NULL),
(3, 1, '2014-07-19', '2014-07-17'),
(4, 2, '2014-07-17', '2014-07-18'),
(5, 2, '2014-07-17', '2014-07-18'),
(6, 3, '2014-07-17', NULL),
(7, 3, '2014-07-18', '2014-07-19')

the query

select  distinct movie_id
from    @simple t
where   exists  -- Rule 1
    (
        select  rent_date
        from    @simple x
        where   return_date is not null
        and movie_id    = t.movie_id
        group by rent_date
        having count(*) = 2
    ) 
or  exists  -- Rule 2
    (
        select  *
        from    @simple x
        where   x.movie_id  = t.movie_id
        and x.rent_date = t.return_date
    )

Upvotes: 0

Related Questions