|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-10-28 : 16:30:34
|
| Rodney writes "CREATE PROCEDURE frsuser.sp_checkavail @unit_name varchar(30),@search_type varchar(1),@arrdate datetime, @depdate datetime,@adults int, @child int,@free_child int,@complex int,@country varchar(30),@state varchar(2),@city varchar(30), @zip varchar(10),@disc smallmoney,@bedrooms varchar(1),@bed_oper varchar(1),@baths varchar(1), @bath_oper varchar(1),@view varchar(1),@hottub varchar(1),@pool varchar(1),@laundry varchar(1), @tennis varchar(1),@golf varchar(1),@skiing varchar(1),@kitchen varchar(1),@bedding1 varchar(1), @bedding2 varchar(1),@bedding3 varchar(1),@bedding4 varchar(1),@extrabedding varchar(1), @extraroom varchar(1),@smoking varchar(1),@pets varchar(1),@handicap varchar(1),@floor varchar(1), @elevator varchar(1),@restaurant varchar(1),@parking varchar(1),@home varchar(1),@rating varchar(1), @rent_per varchar(1),@rent_from smallmoney,@rent_to smallmoney,@maxocc_or varchar(1), @minrent_or varchar(1),@minstay_or varchar(1),@ta_allow varchar(1),@disc_allow varchar(1), @res_type varchar(2),@addons_selected varchar(100),@location varchar(1),@is_TA_booking bitAS/* declaring local variables */declare @nights int, -- to store no. of nights @cost_unit_id int, @cost money, -- to store calculated cost of each unit @book_date smalldatetime, -- to store booking date i.e. current date @is_rent_ranged bit, --flag to check if range is defined for rent @unit_cnt int, @rotation_order_basis char(2), -- to store business setup data @show_invalid_rate_unit bit, -- to store business setup data @unit_id int, @rate_unit_type_id int, @clean_status varchar(3), @allow_pets varchar(1), -- valid values 'Y' or 'N' @allow_smoking varchar(1), -- valid values 'Y' or 'N' @min_rent money, @max_occ int, @max_disc smallmoney, @min_stay int, @max_agent_nights int, -- booking rule data for Travel Nights booked @max_comp_nights int, -- booking rule data for Complimentary nights @booking_rule_passed bit, -- to check if all the booking rules are passed @total_agent_nights int, -- total travel agent nights booked so far @total_comp_nights int, -- total Complimentary Nights booked so far @arrival_day int, -- Arrival day of stay 0 - any day, 1-Monday, 2-Tuesday etc.. @season_id int set nocount on SET ANSI_WARNINGS OFF /* Added by PS on 06-28-2001*/ if @bedrooms = 'H' or @bedrooms = 'S' select @bedrooms = '1' /* End of Addition */ set @nights = datediff(dd,@arrdate,@depdate) set @book_date = convert(smalldatetime, convert(char(10), getdate(), 110)) -- to get the bookin date with time part as 0 set @unit_cnt = 0 set @max_agent_nights = 0 set @max_comp_nights = 0 set @booking_rule_passed = 0 set @total_agent_nights = 0 set @total_comp_nights = 0 /* season find */ select @season_id = season_id from season where @arrdate between season_start_date and season_end_date if (@rent_from >= 0 and @rent_to > 0) set @is_rent_ranged = 1 -- rent range is given else set @is_rent_ranged = 0 -- no rent range /* Retrieving business setup data */ select @rotation_order_basis=rotation_order_basis, @show_invalid_rate_unit=show_invalid_rate_unit from resusageavailabilitysetup /* the following table is created to store final result of unit details before applying the minimum rent rule */ create table #before_rent_unit(unit_id int primary key, unit_name varchar(30), rate_unit_type varchar(20), clean_status varchar(3),usage money,cost money,min_rent money) /* the following table is used to store unit details after filtering on minimum rent rule */ create table #final_unit(unit_id int primary key, unit_name varchar(30), rate_unit_type varchar(20), clean_status varchar(3),usage money,cost money) |
|
Store Procedure running two slow (6seconds).