CODEWITHSUNDEEP
sqlselectsubquery
Michal
Michal

Reputation: 573

How to subtract two selects in which the one select have an expression - SQL Server 2012

I have Query1 for count the number of places in hotel like:

SELECT number_of_places from DatabaseHotel WHERE ID_ROOM > 0

And Query2 for count number of places which are busy like:

SELECT (SELECT COUNT(2) FROM DatabaseHotel2 WHERE (STAY_FROM <= CONVERT(VARCHAR(8), GETDATE(), 112)) 
AND (PREDICT_STAY >= CONVERT(VARCHAR(8), GETDATE(), 112)
OR PREDICT_STAY <= 0) 
AND (STAY_ID_ROOM = DatabaseHotel.ID_ROOM) 
AND (STAY_TO <= 0)
 AND (STAY_RESERV = 0) or (STAY_RESERV = 1)) busy
FROM DatabaseHotel WHERE ID_ROOM > 0

Query2 must be counted by select like this, there is no another way.

Above queries are working ok and returning all of the numbers of places and numbers of busy places from all of the rooms in database. Problem is that I need to count the number of free places and I've tried to subtract these queries in format like:

Select (Query1) - (Query2) AS num_free_pl - and this is working ok but only for ID_ROOM= eg. 10 - so for just one value.

While ID_ROOM > 0 then SQL returning error like:

  Msg 512, Level 16, State 1, Line 1
  Subquery returned more than 1 value.
  This is not permitted when the `subquery` follows `=, !=, <, <= , >, >=` or when the `subquery` is used as an `expression`.

Don't have concept how to solve this problem. I think as simplies it may be explained by two tables : A and B and the equal need be C.

A:
8
4

B:
6
1

C:
2
3

Upvotes: 0

Views: 673

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

To cover cases when there are no busy places corresponding to an ID_ROOM you can write as:

with cte as
(SELECT STAY_ID_ROOM , COUNT(2)as  busy 
        FROM DatabaseHotel2 
        WHERE (STAY_FROM <= CONVERT(VARCHAR(8), GETDATE(), 112)) 
        AND (PREDICT_STAY >= CONVERT(VARCHAR(8), GETDATE(), 112)
             OR PREDICT_STAY <= 0) 
        --AND (STAY_ID_ROOM = DatabaseHotel.ID_ROOM) 
        AND (STAY_TO <= 0)
        AND (STAY_RESERV = 0) or (STAY_RESERV = 1)
        group by STAY_ID_ROOM
)
select ID_ROOM,isnull(busy,0) as busy,(number_of_places - isnull(busy,0)) as  
num_free_pl 
from cte right join DatabaseHotel on  DatabaseHotel.ID_ROOM = cte.STAY_ID_ROOM 
;

[Edit]: Cosidering following table structure: 

create table DatabaseHotel  (ID_ROOM int,number_of_places int);
insert into DatabaseHotel values (1,8),(2,4);

select number_of_places, ID_ROOM from DatabaseHotel WHERE ID_ROOM > 0

create table DatabaseHotel2 (STAY_ID_ROOM int, STAY_FROM datetime,PREDICT_STAY datetime,STAY_TO int,STAY_RESERV int)
insert into DatabaseHotel2 values (1,GETDATE()-1,getdate()+1,0,0),
                                  (1,GETDATE()-1,getdate()+1,0,0),
                                  (1,GETDATE()-1,getdate()+1,0,0),
                                  (1,GETDATE()-1,getdate()+1,0,0),
                                  (1,GETDATE()-1,getdate()+1,0,0),
                                  (1,GETDATE()-1,getdate()+1,0,0),
                                  (2,GETDATE()-1,getdate()+1,0,0);

Upvotes: 1

oerkelens
oerkelens

Reputation: 5161

I was under the impression that ID_ROOM was your PK, but i guess it is not.

If you include in both your queries the HOTEL ID, whatever that is, you can join them on that, and you would get something like

SELECT (Q1).Number - (Q2).Number AS num_free_pl
  FROM Query1 as Q1
  JOIN Query2 as Q2
    ON Q1.HotelId = Q2.HotelId

That should match each record from query one to one record from query two?

Upvotes: 1

Related Questions