April 10, 2008 at 11:26 pm
The weird thing for me is that the same exact sql script posted here works on another database that runs the same exact application (version may differ slightly).
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_expert_room_registry]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotel_expert_room_registry]
GO
CREATE TABLE [dbo].[hotel_expert_room_registry] (
[room_code] [varchar] (15) NOT NULL ,
[guest_lname] [varchar] (50) NULL ,
[guest_fname] [varchar] (50) NULL ,
[freq_guest_num] [varchar] (30) NULL ,
[addr1] [varchar] (100) NULL ,
[addr2] [varchar] (100) NULL ,
[city] [varchar] (50) NULL ,
[state] [varchar] (40) NULL ,
[zip] [varchar] (20) NULL ,
[country] [varchar] (30) NULL ,
[company_name] [varchar] (100) NULL ,
[varchar] (200) NULL ,
[adults_num] [varchar] (20) NULL ,
[room_type] [varchar] (50) NULL ,
[crs] [varchar] (12) NULL ,
[phone] [varchar] (40) NULL ,
[share1_lname] [varchar] (50) NULL ,
[share1_fname] [varchar] (50) NULL ,
[share2_lname] [varchar] (50) NULL ,
[share2_fname] [varchar] (50) NULL ,
[share3_lname] [varchar] (50) NULL ,
[share3_fname] [varchar] (50) NULL ,
[m_message] [varchar] (20) NULL ,
[rand] [varchar] (8) NULL ,
[shareacct] [varchar] (10) NULL ,
[account] [varchar] (12) NULL ,
[id] [varchar] (30) NULL,
[date_from] [varchar] (20) NULL ,
[date_to] [varchar] (20) NULL ,
[cxl_acct] [varchar] (14) NULL ,
[special1] [varchar] (6) NULL ,
[typer] [varchar] (12) NULL ,
[status] [varchar] (1) NULL ,
[status_desc] [varchar] (8) NULL ,
[arr_time] [varchar] (8) NULL ,
[add_type] [varchar] (8) NULL ,
[group_id] [varchar] (8) NULL ,
[group_name] [varchar] (8) NULL ,
[child] [varchar] (2) NULL ,
[honor] [varchar] (12) NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_expert_room_registrypt2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hotel_expert_room_registrypt2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE hotel_expert_room_registrypt2 AS
DECLARE @outoforder varchar (12)
DECLARE @ooofrom varchar (20)
DECLARE @oooto varchar (20)
DECLARE @room_code varchar (6)
DECLARE @guest_name varchar (70)
DECLARE @guest_fname varchar (1)
DECLARE @account varchar(14)
DECLARE @date_from varchar (20)
DECLARE @date_to varchar (20)
DECLARE @freq_guest_num varchar (12)
DECLARE @addr1 varchar (40)
DECLARE @addr2 varchar (40)
DECLARE @city varchar (20)
DECLARE @state varchar (6)
DECLARE @zip varchar (10)
DECLARE @country varchar (6)
DECLARE @company_name varchar (70)
DECLARE @adults_num varchar (2)
DECLARE @phone varchar (15)
DECLARE @special1 varchar (6)
DECLARE @special2 varchar (6)
DECLARE @special3 varchar (6)
DECLARE @special4 varchar (6)
DECLARE @special5 varchar (6)
DECLARE @room_type varchar (6)
DECLARE @out_time varchar (20)
DECLARE @share1_lname varchar (50)
DECLARE @share1_fname varchar (50)
DECLARE @share2_lname varchar (50)
DECLARE @share2_fname varchar (50)
DECLARE @share3_lname varchar (50)
DECLARE @share3_fname varchar (50)
DECLARE @email varchar (40)
DECLARE @shareacct varchar (12)
DECLARE @checked_out varchar (20)
DECLARE @crs varchar (12)
DECLARE @m_message varchar (20)
DECLARE @rand varchar (8)
DECLARE @id varchar (30)
DECLARE @cxl_acct varchar (14)
DECLARE @status varchar (1)
DECLARE @status_desc varchar (1)
DECLARE @arr_time varchar (8)
DECLARE @add_type varchar (1)
DECLARE @group_id varchar (1)
DECLARE @group_name varchar (1)
DECLARE @child varchar (2)
DECLARE @typer varchar (12)
DECLARE @honor varchar (12)
declare curse cursor
for select code from z_rooms where property='VICERO'
open curse
fetch next from curse into @room_code
while (@@fetch_status<>-1)
begin
if (@@fetch_status<>-2)
begin
set @account=isnull((select TOP 1 min(g.account) from guest g, z_property z where (g.room=@room_code and g.status='I') or (g.room=@room_code and g.status='O' and CONVERT (datetime,departure,1)+ CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate())) group by account),'')
set @guest_name=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @guest_fname=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @date_from=isnull(isnull((select CONVERT (varchar,arrival,1) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by arrival),@ooofrom),'')
set @date_to=isnull(isnull((select CONVERT (varchar,departure,1) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by departure),@oooto),'')
set @freq_guest_num=isnull((select ghacct from guest where account=@account and room=@room_code group by ghacct),'')
set @addr1=isnull((select address from guest where account=@account and room=@room_code group by address),'')
set @addr2=isnull((select address2 from guest where account=@account and room=@room_code group by address2),'')
set @city=isnull((select city from guest where account=@account and room=@room_code group by city),'')
set @state=isnull((select state from guest where account=@account and room=@room_code group by state),'')
set @zip=isnull((select zip from guest where account=@account and room=@room_code group by zip),'')
set @country=isnull((select country from guest where account=@account and room=@room_code group by country),'')
set @company_name=isnull((select company from guest where account=@account and room=@room_code group by company),'')
set @adults_num=isnull((select adult from guest where account=@account and room=@room_code group by adult),'')
set @phone=isnull((select phone from guest where account=@account and room=@room_code group by phone),'')
set @room_type=(select roomtype from z_rooms where code=@room_code group by roomtype)
set @email=isnull((select email from guest where account=@account and room=@room_code group by email),'')
set @share1_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname)),'')
set @share3_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname and g.name<>@share2_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname and n.name<>@share2_lname)),'')
set @share1_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname)),'')
set @share3_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname and g.name<>@share2_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname and n.name<>@share2_fname)),'')
set @m_message=isnull((select m_message from guest where account=@account and room=@room_code group by m_message),'')
set @rand=''
set @special1=isnull((select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special),'')
set @shareacct=isnull((select shareacct from guest where account=@account and room=@room_code group by shareacct),'')
set @id=isnull((select ghacct from guest where account=@account and room=@room_code group by ghacct),'')
set @status=isnull((select status from guest where account=@account and room=@room_code group by status),'')
set @status_desc=isnull((select status from guest where account=@account and room=@room_code group by status),'')
set @typer=isnull((select room_type from guest where account=@account and room=@room_code group by room_type),'')
set @add_type=''
set @group_id=''
set @group_name=''
set @honor=isnull((select ghacct from guest where account=@account and room=@room_code group by ghacct),'')
set @crs=isnull((select crs from guest where account=@account and room=@room_code group by crs),'')
set @cxl_acct=isnull((select cxl_acct from guest where account=@account and room=@room_code group by cxl_acct),'')
set @arr_time=isnull((select CONVERT (varchar,arr_time,108) from guest where account=@account and room=@room_code group by arr_time),'')
set @child=isnull((select child from guest where account=@account and room=@room_code group by child),'')
INSERT INTO hotel_expert_room_registry (room_code,guest_lname,guest_fname,freq_guest_num,addr1,addr2,city,state,zip,country,company_name,email,adults_num,room_type,crs,phone,share1_lname,share1_fname,share2_lname,share2_fname,share3_lname,share3_fname,m_message,rand,shareacct,id,account,date_from,date_to,cxl_acct,special1,typer,status,status_desc,add_type,group_id, group_name,arr_time,child,honor)
VALUES (@room_code,@guest_name,@guest_fname,@freq_guest_num,@addr1,@addr2,@city,@state,@zip,@country,@company_name,@email,@adults_num,@room_type,@crs,@phone,@share1_lname,@share1_fname,@share2_lname,@share2_fname,@share3_lname,@share3_fname,@m_message,@rand,@shareacct,@id,@account,@date_from,@date_to,@cxl_acct,@special1,@typer,@status,@status_desc,@add_type,@group_id,@group_name,@arr_time,@child,@honor)
end
fetch next from curse into @room_code
end
close curse
deallocate curse
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec hotel_expert_room_registrypt2
This same sql works fine on another DB with same application but not here...the table "GUEST"...has the same structure....the arrival and departure columns are "datetime" in the table.
Can anyone help me understand what subtle differences might be agonizing me here?
April 11, 2008 at 12:03 am
Hi,
I thick you cannt add dates using "+".
CONVERT (datetime,departure,1)+ CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate()))
Instead of "+" use dateadd function on right hand side also.
Cheers,
Amol
April 11, 2008 at 12:51 am
[font="Verdana"]
Does your problem get solved by Amol’s suggestion or still you are facing any?
Do let us know the exact scene.
Mahesh
[/font]
MH-09-AM-8694
April 11, 2008 at 8:24 am
Amol Mitkari (4/11/2008)
Hi,I thick you cannt add dates using "+".
CONVERT (datetime,departure,1)+ CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate()))
Instead of "+" use dateadd function on right hand side also.
Cheers,
Amol
can you post your proposed code change? I'm a little unclear.
April 13, 2008 at 10:06 pm
[font="Verdana"]
set @account=isnull((select TOP 1 min(g.account) from guest g, z_property z where (g.room=@room_code and g.status='I') or (g.room=@room_code and g.status='O' and CONVERT (datetime,departure,1) ' + ' CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate())) group by account),'')
What you need to do is to place plus sign [+] between quotes. + sign is used to concatenate two strings in SQL Server.
Let us know.
Mahesh[/font]
MH-09-AM-8694
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply