Click here to Skip to main content
12,076,053 members (34,308 online)
Rate this:
 
Please Sign up or sign in to vote.
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160208.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2016
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