select times,R.name,date,indate,outdate,money,predate
from (select count(*) as times,
name,
sum(datediff(day,indate,outdate))as date,
min(indate)as indate,
max(outdate) as outdate,
sum(datediff(day,indate,outdate)*money) as money
from hotel a
where state='O'
group by name) R
left join
(select name,max(predate) as predate from hotel b
where state='P'
group by name) S
on R.name=S.name
from (select count(*) as times,
name,
sum(datediff(day,indate,outdate))as date,
min(indate)as indate,
max(outdate) as outdate,
sum(datediff(day,indate,outdate)*money) as money
from hotel a
where state='O'
group by name) R
left join
(select name,max(predate) as predate from hotel b
where state='P'
group by name) S
on R.name=S.name