none
Disponibilidad por fechas (De camas atraves del Tipo de Habitacion) RRS feed

  • Pregunta

  • Buenas Tardes,

    Me gustaría poder obtener la disponibilidad de "camas" que tengo teniendo en cuenta que asi yo no "asigne una cama" o tenga registrado un "IDCama" en mi tabla "DetalleReserva" igual debería descontar en mi disponibilidad de camas restantes ya que estoy reservando un ese "Tipo de Habitacion". Para ello tengo la siguiente estructura de tablas. Actualmente si descuenta, siempre en cuando tengo el "IDCama" en mi tabla "DetalleReserva" pero quisiera q tmb descuente asi no lo tenga debido a que es un espacio en ese "Tipo de Habitacion. 

    CREATE TABLE TipoHabitacion ( IDTipoHabitacion int NOT NULL, Nombre varchar(20) null, ) CREATE TABLE Habitacion( IDHabitacion int, IDTipoHabitacion int, Nombre varchar(20) ) CREATE TABLE Camas ( IDCama int NOT NULL, IDHabitacion int, Descripcion varchar(20) NULL ) CREATE TABLE Detalle_Reserva ( IDDetalleReserva int NOT NULL,

    IDTipoHabitacion int NOT NULL, IDCama int NULL, FechaReservada datetime NULL )

    Uso la siguiente vista.

    DECLARE @FechaInicio date = '20160701';
    DECLARE @FechaFin date = '20160707';
    CREATE TABLE #RangoFechas (Fecha date);
    DECLARE @Cols nvarchar(MAX);
    DECLARE @ConsultaSQL nvarchar(MAX);
    
    WITH Fechas (Fecha) AS
    (
    	SELECT @FechaInicio
    	UNION ALL
    	SELECT DATEADD(DAY, 1, Fecha) FROM Fechas WHERE (Fecha < @FechaFin)
    )
    INSERT INTO #RangoFechas (Fecha) SELECT Fecha FROM Fechas;
    
    SET @Cols = STUFF((SELECT N',' + QUOTENAME(Fecha) FROM #RangoFechas
    					FOR XML PATH (''), TYPE).value('.[1]', 'varchar(MAX)'), 1, 1, N'');
    
    SET @ConsultaSQL = N'WITH PivotInput (TipoHabitacion, Fecha, NumCamasDisponible) AS (						
    						SELECT MAX(th.Nombre), f.Fecha, (SELECT COUNT(*) FROM Camas cc INNER JOIN Habitacion hh ON (cc.IDHabitacion = hh.IDHabitacion) WHERE hh.IDTipoHabitacion = th.IDTipoHabitacion) - SUM(CASE WHEN (hb.IDTipoHabitacion = th.IDTipoHabitacion) AND D.IDDetalleReserva IS NOT NULL THEN 1 ELSE 0 END) FROM Camas c INNER JOIN Habitacion hb ON (c.IDHabitacion = hb.IDHabitacion) LEFT JOIN Detalle_Reserva d ON (d.IDCama = c.IDCama) RIGHT JOIN #RangoFechas f ON (CONVERT(date, d.FechaReservada) = f.Fecha) CROSS APPLY (SELECT IDTipoHabitacion, Nombre FROM TipoHabitacion) th GROUP BY f.Fecha, th.IDTipoHabitacion)
    						SELECT * FROM PivotInput
    						PIVOT (SUM(NumCamasDisponible) FOR Fecha IN (' + @Cols + N')) AS PivotTable;';
    
    EXECUTE sp_executesql @ConsultaSQL;
    
    DROP TABLE #RangoFechas;

    • Editado _-.',Kevin miércoles, 23 de noviembre de 2016 19:24
    miércoles, 23 de noviembre de 2016 17:36

Respuestas

  • Ya veo donde esta el problema y es que para calcular las camas reservadas por tipo de habitacion deberas hacer dos agrupaciones distintas, unirlas y agrupar una vez mas.

    La primera es simple H - TH - C - R y agrupas por fecha de reservacion y tipo de habitacion. La segunda seria TH - R por tipo de habitacion y agrupar por fecha de reserva y tipo habitacion pero adicionando el predicado que el id de la cama sea la marca NULL.

    Una vez que tienes usas dos agrupaciones las unes usando UNION ALL y vuelves a agrupar por fecha de reservacion y tipo de habitacion.

    Aca el ejemplo y donde solo faltaria que vuelques el resultado hacia una tabla, digamos temporal, y realizes el pivoteo.

    DECLARE @TipoHabitacion table (
    IDTipoHabitacion int NOT NULL,
    Nombre           varchar(20) null
    );
    
    DECLARE @Habitacion table (
    IDHabitacion int,
    IDTipoHabitacion int,
    Nombre varchar(20)
    
    );
    
    DECLARE @Camas table (
    IDCama               int NOT NULL,
    IDHabitacion     int,
    Descripcion          varchar(20) NULL
    );
    
    DECLARE @Detalle_Reserva table (
    IDDetalleReserva     int NOT NULL,
    IDTipoHabitacion     int NOT NULL,
    IDCama               int NULL,
    FechaReservada       datetime NULL
    );
    
    INSERT INTO @TipoHabitacion VALUES
    (1,'8-BED'),
    (2,'6-BED'),
    (3,'4-BED')
    
    INSERT INTO @Habitacion VALUES
    (1,1,'205'),
    (2,1,'206'),
    (3,2,'305'),
    (4,2,'306'),
    (5,3,'405'),
    (6,3,'406')
    
    INSERT INTO @Camas VALUES
    (1,1,'205-1'),
    (2,1,'205-2'),
    (3,2,'206-1'),
    (4,2,'206-2'),
    (5,3,'305-1'),
    (6,3,'305-2'),
    (7,4,'306-1'),
    (8,4,'306-2'),
    (9,5,'405-1'),
    (10,5,'405-2'),
    (11,6,'406-1'),
    (12,6,'406-2')
    
    SET DATEFORMAT DMY;
    
    INSERT INTO @Detalle_Reserva VALUES
    (1,1,NULL,'11-11-16'),
    (2,1,1,'11-11-16'),
    (3,2,NULL,'11-11-16'),
    (4,3,NULL,'15-11-16'),
    (5,3,10,'15-11-16'),
    (6,3,12,'14-11-16');
    
    SET DATEFORMAT MDY;
    
    DECLARE @FechaInicio date = '20161110';
    DECLARE @FechaFin date = '20161116';
    
    WITH Fechas (Fecha) AS (
    SELECT @FechaInicio
    UNION ALL
    SELECT DATEADD(DAY, 1, Fecha) FROM Fechas WHERE (Fecha < @FechaFin)
    )
    , AGG AS (
    SELECT
    	TH.Nombre AS tipo_habitacion,
    	COUNT(*) AS total_camas
    FROM
    	@Habitacion AS H
    	INNER JOIN
        @TipoHabitacion AS TH
    	ON TH.IDTipoHabitacion = H.IDTipoHabitacion
    	INNER JOIN
        @Camas AS C
    	ON C.IDHabitacion = H.IDHabitacion
    GROUP BY
    	TH.Nombre
    )
    , CJ AS (
    SELECT
    	F.Fecha,
    	A.tipo_habitacion,
    	A.total_camas
    FROM
    	Fechas AS F
    	CROSS JOIN
    	AGG AS A
    )
    SELECT
    	CJ.Fecha,
        CJ.tipo_habitacion,
        CJ.total_camas,
    	R.total_camas_reservadas,
    	CJ.total_camas - ISNULL(R.total_camas_reservadas, 0) AS total_camas_disponibles
    FROM
    	CJ
    	LEFT OUTER JOIN
    	(
    	SELECT
    		T.Fecha,
    		T.tipo_habitacion,
    		SUM(T.camas_reservadas) AS total_camas_reservadas 
    	FROM 
    		(
    		SELECT
    			DR.FechaReservada AS Fecha,
    			TH.Nombre AS tipo_habitacion,
    			COUNT(*) AS camas_reservadas
    		FROM
    			@Habitacion AS H
    			INNER JOIN
    			@TipoHabitacion AS TH
    			ON TH.IDTipoHabitacion = H.IDTipoHabitacion
    			INNER JOIN
    			@Camas AS C
    			ON C.IDHabitacion = H.IDHabitacion
    			INNER JOIN
    			@Detalle_Reserva AS DR
    			ON DR.IDCama = C.IDCama
    		GROUP BY
    			DR.FechaReservada,
    			TH.Nombre
    
    		UNION ALL
    
    		SELECT
    			DR.FechaReservada AS Fecha,
    			TH.Nombre AS tipo_habitacion,
    			COUNT(*) AS camas_reservadas
    		FROM
    			@TipoHabitacion AS TH
    			INNER JOIN
    			@Detalle_Reserva AS DR
    			ON DR.IDTipoHabitacion = TH.IDTipoHabitacion AND DR.IDCama IS NULL
    		GROUP BY
    			DR.FechaReservada,
    			TH.Nombre
    		) AS T
    	GROUP BY
    		T.Fecha,
    		T.tipo_habitacion
    	) AS R
    	ON R.Fecha = CJ.Fecha
    	AND R.tipo_habitacion = CJ.tipo_habitacion
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Propuesto como respuesta Laura CeglzModerator jueves, 24 de noviembre de 2016 16:24
    • Marcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 17:01
    • Desmarcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 19:19
    • Marcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 19:39
    • Editado HunchbackMVP lunes, 28 de noviembre de 2016 13:45
    miércoles, 23 de noviembre de 2016 22:23

Todas las respuestas

  • Seria de ayuda si postearas data de ejemplo para nosotros probar nuestras sugerencias.

    La idea seria:

    - Generar dominio de fechas y habitaciones (CROSS JOIN) para luego agrupar por fecha y tipo de habitacion agregando el numero de habitaciones.

    - Hacer el mismo calculo usando las reservaciones (agrupar por fecha y tipo habitacion agrengando el numero de habitaciones).

    - Unir ambos conjuntos por fecha y tipo de habitacion para calcular disponibilidad por fecha y tipo de habitacion.

    - Pivotear resultado anterior y ara o cual seria de ayuda si este lo volcas hacia una tabla antes del pivoteo.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Editado HunchbackMVP miércoles, 23 de noviembre de 2016 18:44
    miércoles, 23 de noviembre de 2016 18:43
  • Hola Hunchback,

    Aqui te adjunto algo de Data:

    INSERT INTO TipoHabitacion VALUES
    (1,'8-BED'),
    (2,'6-BED'),
    (3,'4-BED')
    
    INSERT INTO Habitacion VALUES
    (1,1,'205'),
    (2,1,'206'),
    (3,2,'305'),
    (4,2,'306'),
    (5,3,'405'),
    (6,3,'406')
    
    INSERT INTO Camas VALUES
    (1,1,'205-1'),
    (2,1,'205-2'),
    (3,2,'206-1'),
    (4,2,'206-2'),
    (5,3,'305-1'),
    (6,3,'305-2'),
    (7,4,'306-1'),
    (8,4,'306-2'),
    (9,5,'405-1'),
    (10,5,'405-2'),
    (11,6,'406-1'),
    (12,6,'406-2')
    
    
    INSERT INTO Detalle_Reserva VALUES
    (1,1,NULL,'11-11-16'),
    (2,1,1,'11-11-16'),
    (3,2,NULL,'11-11-16'),
    (4,3,NULL,'15-11-16'),
    (5,3,10,'15-11-16'),
    (6,3,12,'14-11-16')
    

    La idea es tener como resultado la vista que adjunte en al imagen de arriba, como ves en la tabla #Detalle_Reserva en algunas ocasiones "asigno" una cama y en otras solo "reservo" un espacio del tipo de habitacion y lo que quiero es q de ambas formas descuente mi total de camas.

    Actulamente con el codigo que adjunte arriba respecto a mi procedimiento si me resta solo si asigno el "id" de la cama en mi tabla Detalle_reserva pero quiero q sea de ambas formas tanto si añado el 'id cama' o solo el 'id del tipo de habitacion' ya q igual es un espacio. Espero puedas ayudarme

    miércoles, 23 de noviembre de 2016 19:31
  • Ya veo donde esta el problema y es que para calcular las camas reservadas por tipo de habitacion deberas hacer dos agrupaciones distintas, unirlas y agrupar una vez mas.

    La primera es simple H - TH - C - R y agrupas por fecha de reservacion y tipo de habitacion. La segunda seria TH - R por tipo de habitacion y agrupar por fecha de reserva y tipo habitacion pero adicionando el predicado que el id de la cama sea la marca NULL.

    Una vez que tienes usas dos agrupaciones las unes usando UNION ALL y vuelves a agrupar por fecha de reservacion y tipo de habitacion.

    Aca el ejemplo y donde solo faltaria que vuelques el resultado hacia una tabla, digamos temporal, y realizes el pivoteo.

    DECLARE @TipoHabitacion table (
    IDTipoHabitacion int NOT NULL,
    Nombre           varchar(20) null
    );
    
    DECLARE @Habitacion table (
    IDHabitacion int,
    IDTipoHabitacion int,
    Nombre varchar(20)
    
    );
    
    DECLARE @Camas table (
    IDCama               int NOT NULL,
    IDHabitacion     int,
    Descripcion          varchar(20) NULL
    );
    
    DECLARE @Detalle_Reserva table (
    IDDetalleReserva     int NOT NULL,
    IDTipoHabitacion     int NOT NULL,
    IDCama               int NULL,
    FechaReservada       datetime NULL
    );
    
    INSERT INTO @TipoHabitacion VALUES
    (1,'8-BED'),
    (2,'6-BED'),
    (3,'4-BED')
    
    INSERT INTO @Habitacion VALUES
    (1,1,'205'),
    (2,1,'206'),
    (3,2,'305'),
    (4,2,'306'),
    (5,3,'405'),
    (6,3,'406')
    
    INSERT INTO @Camas VALUES
    (1,1,'205-1'),
    (2,1,'205-2'),
    (3,2,'206-1'),
    (4,2,'206-2'),
    (5,3,'305-1'),
    (6,3,'305-2'),
    (7,4,'306-1'),
    (8,4,'306-2'),
    (9,5,'405-1'),
    (10,5,'405-2'),
    (11,6,'406-1'),
    (12,6,'406-2')
    
    SET DATEFORMAT DMY;
    
    INSERT INTO @Detalle_Reserva VALUES
    (1,1,NULL,'11-11-16'),
    (2,1,1,'11-11-16'),
    (3,2,NULL,'11-11-16'),
    (4,3,NULL,'15-11-16'),
    (5,3,10,'15-11-16'),
    (6,3,12,'14-11-16');
    
    SET DATEFORMAT MDY;
    
    DECLARE @FechaInicio date = '20161110';
    DECLARE @FechaFin date = '20161116';
    
    WITH Fechas (Fecha) AS (
    SELECT @FechaInicio
    UNION ALL
    SELECT DATEADD(DAY, 1, Fecha) FROM Fechas WHERE (Fecha < @FechaFin)
    )
    , AGG AS (
    SELECT
    	TH.Nombre AS tipo_habitacion,
    	COUNT(*) AS total_camas
    FROM
    	@Habitacion AS H
    	INNER JOIN
        @TipoHabitacion AS TH
    	ON TH.IDTipoHabitacion = H.IDTipoHabitacion
    	INNER JOIN
        @Camas AS C
    	ON C.IDHabitacion = H.IDHabitacion
    GROUP BY
    	TH.Nombre
    )
    , CJ AS (
    SELECT
    	F.Fecha,
    	A.tipo_habitacion,
    	A.total_camas
    FROM
    	Fechas AS F
    	CROSS JOIN
    	AGG AS A
    )
    SELECT
    	CJ.Fecha,
        CJ.tipo_habitacion,
        CJ.total_camas,
    	R.total_camas_reservadas,
    	CJ.total_camas - ISNULL(R.total_camas_reservadas, 0) AS total_camas_disponibles
    FROM
    	CJ
    	LEFT OUTER JOIN
    	(
    	SELECT
    		T.Fecha,
    		T.tipo_habitacion,
    		SUM(T.camas_reservadas) AS total_camas_reservadas 
    	FROM 
    		(
    		SELECT
    			DR.FechaReservada AS Fecha,
    			TH.Nombre AS tipo_habitacion,
    			COUNT(*) AS camas_reservadas
    		FROM
    			@Habitacion AS H
    			INNER JOIN
    			@TipoHabitacion AS TH
    			ON TH.IDTipoHabitacion = H.IDTipoHabitacion
    			INNER JOIN
    			@Camas AS C
    			ON C.IDHabitacion = H.IDHabitacion
    			INNER JOIN
    			@Detalle_Reserva AS DR
    			ON DR.IDCama = C.IDCama
    		GROUP BY
    			DR.FechaReservada,
    			TH.Nombre
    
    		UNION ALL
    
    		SELECT
    			DR.FechaReservada AS Fecha,
    			TH.Nombre AS tipo_habitacion,
    			COUNT(*) AS camas_reservadas
    		FROM
    			@TipoHabitacion AS TH
    			INNER JOIN
    			@Detalle_Reserva AS DR
    			ON DR.IDTipoHabitacion = TH.IDTipoHabitacion AND DR.IDCama IS NULL
    		GROUP BY
    			DR.FechaReservada,
    			TH.Nombre
    		) AS T
    	GROUP BY
    		T.Fecha,
    		T.tipo_habitacion
    	) AS R
    	ON R.Fecha = CJ.Fecha
    	AND R.tipo_habitacion = CJ.tipo_habitacion
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas


    • Propuesto como respuesta Laura CeglzModerator jueves, 24 de noviembre de 2016 16:24
    • Marcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 17:01
    • Desmarcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 19:19
    • Marcado como respuesta _-.',Kevin jueves, 24 de noviembre de 2016 19:39
    • Editado HunchbackMVP lunes, 28 de noviembre de 2016 13:45
    miércoles, 23 de noviembre de 2016 22:23
  • He probado el codigo y me he dado cuenta de un detalle, "Al asignar una "cama" o colocar el "idcama" de ese tipo de habitacion a la tabla "Detalle_Reserva" me descuenta 2 espacios y no 1. Por favor espero me respondas
    jueves, 24 de noviembre de 2016 18:16
  • Chequea el codigo nuevamente y estate seguro que adicionastes esta parte al predicado donde contamos las reservaciones de tipo de habitacion sin tener el ID de la cama ( AND DR.IDCama IS NULL ).

    SELECT
    	DR.FechaReservada AS Fecha,
    	TH.Nombre AS tipo_habitacion,
    	COUNT(*) AS camas_reservadas
    FROM
    	@Habitacion AS H
    	INNER JOIN
    	@TipoHabitacion AS TH
    	ON TH.IDTipoHabitacion = H.IDTipoHabitacion
    	INNER JOIN
    	@Camas AS C
    	ON C.IDHabitacion = H.IDHabitacion
    	INNER JOIN
    	@Detalle_Reserva AS DR
    	ON DR.IDCama = C.IDCama
    GROUP BY
    	DR.FechaReservada,
    	TH.Nombre
    
    UNION ALL
    
    SELECT
    	DR.FechaReservada AS Fecha,
    	TH.Nombre AS tipo_habitacion,
    	COUNT(*) AS camas_reservadas
    FROM
    	@TipoHabitacion AS TH
    	INNER JOIN
    	@Detalle_Reserva AS DR
    	ON DR.IDTipoHabitacion = TH.IDTipoHabitacion
            AND DR.IDCama IS NULL <---------------------
    GROUP BY
    	DR.FechaReservada,
    	TH.Nombre


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 28 de noviembre de 2016 13:49