如何在temptable中查找值的数量 [英] how to find number of values in temptable

问题描述

我使用以下编码来获取在日期之间预订的号码房间。

我从表中获得了确切的数据，但我希望获得特定类别的房间总数，如单个a / c - 3，单个非a / c - 2，双a / c - 4

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

推荐答案

我修改了你的存储过程以获得所需的结果。

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.

这篇关于如何在temptable中查找值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！