counting same dates between 2 dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

counting same dates between 2 dates

Author

Topic

rodvalencia
Starting Member

3 Posts

Posted - 2009-01-27 : 18:31:03

I have a table called RENTAL below

customer_id/movie_no/copy_no/rental_date/return_date
1/6/1/24-Aug-08/27-Aug-08
1/1/1/7-Sep-08/14-Sep-08
2/5/2/23-Aug-08/4-Sep-08
2/5/1/29-Sep-08/06-Oct-08
2/2/1/23-Sep-08/30-Sep-08
3/4/1/23-Aug-08/11-Sep-08
4/5/1/23-Sep-08/30-Sep-08
8/3/2/23-Sep-08/30-Sep-08

*columns are seperated by /

I want to diplay all rows where movie_no, copy_no has been rented even before it was returned. This actually happens because some customers book a copy that is currently on loan.

I want to see all the copys and movie_no that has been rented on the same day.

In this table, movie_no = 5, copy_no =1 has been rented in the same date by 2 different customer_id 2 and customer_id 5. How can I query this information?

I have created
Select * FROM RENTAL WHERE movie_no IN(SELECT movie_no FROM RENTAL GROUP BY movie_no HAVING COUNT(movie_no)>1) AND copy_no IN(SELECT copy_no FROM RENTAL GROUP BY movie_no, copy_no HAVING COUNT(movie_no)>1);

but it doesnt satisfy the query that I need. I need to get the dates in between each row and compare it to the dates in between of the other rows to know what dates occured twice.

I just dont know how to count all the dates that has the same dates in between. I need to have the code to get all the dates that occurs twice in the table.

I hope someone can help me.

Thanks.

rodvalencia

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 19:01:38

So what will be your expected output from above sample?

rodvalencia
Starting Member

3 Posts

Posted - 2009-01-27 : 19:24:11

it should show

customer_id/movie_no/copy_no/rental_date/return_date
2/5/1/29-Sep-08/06-Oct-08
4/5/1/23-Sep-08/30-Sep-08

because as you can see, they rented the same movie at the same date.

In an actual database, this shouldnt be happening, having the same movie rented at the same time, but this problem occurs because mad a booking for a particular movie that is currently on loan.

I just need a code where I can get all the same dates that exists on the dates between rental_date and return_date.

I tried SELECT COUNT(SELECT rental_date from RENTAL WHERE rental_date BETWEEN 01/01/08 AND NOW())>1;

and it didnt work..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 19:58:59

How did they rent at same date?

2/5/1/29-Sep-08/06-Oct-08
4/5/1/23-Sep-08/30-Sep-08

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 02:11:12

[code]declare @test table
(
customer_id int,
movie_no int,
copy_no int,
rental_date datetime,
return_date datetime
)
insert into @Test
select 1,6,1,'24 Aug 08','27 Aug 08' union all
select 1,1,1,'7 Sep 08','14 Sep 08' union all
select 2,5,2,'23 Aug 08','4 Sep 08' union all
select 2,5,1,'29 Sep 08','06 Oct 08' union all
select 2,2,1,'23 Sep 08','30 Sep 08' union all
select 3,4,1,'23 Aug 08','11 Sep 08' union all
select 4,5,1,'23 Sep 08','30 Sep 08' union all
select 8,3,2,'23 Sep 08','30 Sep 08'

select t.*
from @test t
cross apply (select count(customer_id) as custcount
from @test
where movie_no=t.movie_no and copy_no=t.copy_no
and ((return_date>t.rental_date and return_date<t.return_date)
or (rental_date>t.rental_date and rental_date<t.return_date))
)tmp
where tmp.custcount>0

output
----------------------------------------
customer_id movie_no copy_no rental_date return_date
2 5 1 2008-09-29 00:00:00.000 2008-10-06 00:00:00.000
4 5 1 2008-09-23 00:00:00.000 2008-09-30 00:00:00.000[/code]

rodvalencia
Starting Member

3 Posts

Posted - 2009-01-28 : 07:36:33

THANKS A LOT MATE!!much appreciated!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:40:17

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources