Stored procedure to select fields from different tables with COUNT function - SQLServer 2014

Question

Stored procedure to select fields from different tables with COUNT function - SQLServer 2014

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to make an SP that allows me to show for a certain hotel and in a range of dates, the number of free rooms and the number of rooms occupied. Then I have to group them by date and category. I have the tables: Hotel, Reservations, Category and Occupation. So far I have only the quantities of free and occupied rooms

select SUM(Cantidad_ocupantes) as 'Habitaciones ocupadas',
  (select COUNT(Cantidad_ocupantes) from Ocupacion where Cantidad_ocupantes=0) as 'Habitaciones Libres' 
from Ocupacion

I appreciate all the ideas that come my way.

sql-server stored-procedure

asked by Pablo Matias 28.06.2017 в 20:03

source

2 answers

DomPDF with repeated tables Get links of all the images stored in FIREBASE

score 0 · Answer 1

Responding to your group, this would be the script that would help you to know how many rooms are occupied, by Hotel, Date and Category:

SELECT Ho.Descripcion, O.Fecha, C.Descripcion, COUNT(*)
FROM OCUPACION AS O
INNER JOIN HABITACIONES AS H
    ON (O.Codigo_habitacion = H.Codigo_habitacion)
INNER JOIN HOTELES AS Ho
    ON (H.Codigo_hotel = Ho.Codigo_hotel)
INNER JOIN CATEGORIAS AS C
    ON (C.Codigo_categoria = H.Codigo_categoria)
WHERE O.Fecha BETWEEN 'FechaInicio' and 'FechaFinal'
GROUP BY Ho.Descripcion, O.Fecha, C.Descripcion;

I want to emphasize that I take the Occupation Table as a date. I hope my answer has been helpful, greetings.

score 0 · Answer 2

I got something similar with the help of @Rostan

declare @Fecha_Reserva_Desde date = '20170601'
declare @Fecha_Reserva_Hasta date = '20170603'

SELECT Ho.Descripcion as Hotel, O.Fecha, C.Decripcion, SUM(Cantidad_ocupantes) as 'Habitaciones ocupadas',
(select COUNT(Cantidad_ocupantes) from Ocupacion where Cantidad_ocupantes=0) as 'Habitaciones Libres' 
FROM OCUPACION AS O
INNER JOIN HABITACIONES AS H
    ON (O.Codigo_habitacion = H.Codigo_habitacion)
INNER JOIN HOTELES AS Ho
    ON (H.Codigo_hotel = Ho.Codigo_hotel)
INNER JOIN CATEGORIAS AS C
    ON (C.Codigo_categoria = H.Codigo_categoria)
WHERE O.Fecha BETWEEN @Fecha_Reserva_Desde and @Fecha_Reserva_Hasta 
GROUP BY Ho.Descripcion, O.Fecha, C.Decripcion
ORDER BY Ho.Descripcion;

I got the following:

The column 'Free Rooms' should give me "17400" where there is no occupation, and where there is 1 occupation I should get 17399.