Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
I used below coding to get number rooms booked in between dates.
I am getting exact data from table, but I would like to get total number of rooms in particular category like single a/c - 3, single non a/c - 2, double a/c - 4
ALTER PROCEDURE FETCH_ADVANCED_ROOM_BOOKING_DETAILS
(
@FROM_DATE DATETIME,
@TO_DATE DATETIME
)
AS
BEGIN
 
DECLARE @TEMPTABLE TABLE
(
BOOKED_DATE NVARCHAR(250),
ARRIVAL_DATE NVARCHAR(250),
DEPARTURE_DATE NVARCHAR(250),
TOTAL_DAY BIGINT,
CUSNAME NVARCHAR(250),
MOBNO BIGINT,
ROOMNO BIGINT,
ROOMTYPE NVARCHAR(250),
 
TOTALROOMS BIGINT,
TOTALROOMTYPE NVARCHAR(4000),
TYPEROOMTOTAL BIGINT
)
 
INSERT INTO @TEMPTABLE (BOOKED_DATE,ARRIVAL_DATE,DEPARTURE_DATE,TOTAL_DAY,CUSNAME,MOBNO,ROOMNO,ROOMTYPE )
SELECT CONVERT(VARCHAR,T1.BOOKING_DATE,103), CONVERT(VARCHAR,T1.ARRDATE,103),
CONVERT(VARCHAR,T1.DEPTDTE,103),T1.TOTALDAY,T2.CUSNAME,T2.MOBNO,T3.ROOMNO,T3.ROOMTYPE FROM ROOMRESERVATION T1
LEFT OUTER JOIN CUSDETAILS T2 ON T1.CUSID=T2.CUSID LEFT OUTER JOIN ROOMDETAILS T3 ON T1.RID=T3.RID
 
WHERE T1.BOOKID NOT IN
(
SELECT BOOK_ID FROM CHECKEDOUT_DETAILS
)
AND (ARRDATE BETWEEN @FROM_DATE AND @TO_DATE OR DEPTDTE BETWEEN @FROM_DATE AND @TO_DATE)
 
--GROUP BY T3.ROOMTYPE

DECLARE @TOTALROOMS BIGINT
SELECT  @TOTALROOMS=COUNT(ROOMNO) FROM @TEMPTABLE
 

DECLARE @ROOMTYPENOS INT
DECLARE @ROOMT NVARCHAR(250)
 
SELECT  @ROOMTYPENOS=COUNT(ROOMTYPE),@ROOMT=ROOMTYPE FROM @TEMPTABLE GROUP BY ROOMTYPE
 
INSERT INTO @TEMPTABLE (TOTALROOMS,TOTALROOMTYPE,TYPEROOMTOTAL)
SELECT @TOTALROOMS,@ROOMT,@ROOMTYPENOS
 
SELECT * FROM @TEMPTABLE
Getting output like this
07/11/2012  09/11/2012  11/11/2012  2   umapathi    9942676484  3   single a/c  NULL    NULL    NULL
07/11/2012  15/11/2012  17/11/2012  2   umapathi    9942676484  8   single a/c  NULL    NULL    NULL
07/11/2012  15/11/2012  17/11/2012  2   Ramesh  8951056479  9   single a/c  NULL    NULL    NULL
07/11/2012  15/11/2012  17/11/2012  2   Ramesh  8951056479  1   single non a/c  NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    4   single non a/c  1
Posted 24-Nov-12 1:41am
Edited 24-Nov-12 1:43am
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I have modified your stored procedure to get the required result.
ALTER PROCEDURE FETCH_ADVANCED_ROOM_BOOKING_DETAILS
(
@FROM_DATE DATETIME,
@TO_DATE DATETIME
)
AS
BEGIN
 
DECLARE @TEMPTABLE TABLE
(
BOOKED_DATE NVARCHAR(250),
ARRIVAL_DATE NVARCHAR(250),
DEPARTURE_DATE NVARCHAR(250),
TOTAL_DAY BIGINT,
CUSNAME NVARCHAR(250),
MOBNO BIGINT,
ROOMNO BIGINT,
ROOMTYPE NVARCHAR(250),
 
TOTALROOMS BIGINT,
TOTALROOMTYPE NVARCHAR(4000),
TYPEROOMTOTAL BIGINT
)
 
INSERT INTO @TEMPTABLE (BOOKED_DATE,ARRIVAL_DATE,DEPARTURE_DATE,TOTAL_DAY,CUSNAME,MOBNO,ROOMNO,ROOMTYPE )
SELECT CONVERT(VARCHAR,T1.BOOKING_DATE,103), CONVERT(VARCHAR,T1.ARRDATE,103),
CONVERT(VARCHAR,T1.DEPTDTE,103),T1.TOTALDAY,T2.CUSNAME,T2.MOBNO,T3.ROOMNO,T3.ROOMTYPE FROM ROOMRESERVATION T1
LEFT OUTER JOIN CUSDETAILS T2 ON T1.CUSID=T2.CUSID LEFT OUTER JOIN ROOMDETAILS T3 ON T1.RID=T3.RID
 
WHERE T1.BOOKID NOT IN
(
SELECT BOOK_ID FROM CHECKEDOUT_DETAILS
)
AND (ARRDATE BETWEEN @FROM_DATE AND @TO_DATE OR DEPTDTE BETWEEN @FROM_DATE AND @TO_DATE)
 
--GROUP BY T3.ROOMTYPE

DECLARE @TOTALROOMS BIGINT
SELECT  @TOTALROOMS=COUNT(ROOMNO) FROM @TEMPTABLE
 
 
DECLARE @ROOMTYPENOS INT
DECLARE @ROOMT NVARCHAR(250)
 
SELECT  @ROOMTYPENOS=COUNT(ROOMTYPE),@ROOMT=ROOMTYPE FROM @TEMPTABLE GROUP BY ROOMTYPE
 
INSERT INTO @TEMPTABLE (TOTALROOMS,TOTALROOMTYPE,TYPEROOMTOTAL)
SELECT @TOTALROOMS,@ROOMT,@ROOMTYPENOS
 

INSERT INTO @TempTable (TOTALROOMS,TOTALROOMTYPE,TYPEROOMTOTAL)
SELECT @TOTALROOMS, RoomType, COUNT(RoomType) FROM @TempTable
GROUP BY RoomType
 
 
SELECT * FROM @TEMPTABLE
Hope this helps.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100