RE: Slowness of sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

RE: Slowness of sql query

Author

Topic

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-21 : 02:39:45

Hi guys kindly help,

Ihave used following table lined functions in sql

USE [EEMSPROD]
GO
/****** Object: UserDefinedFunction [dbo].[OpenTRF] Script Date: 08/21/2010 11:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[OpenTRF]() RETURNS @TblOpenTRF TABLE ([Req No] NVARCHAR(50),[Travel from Date] datetime,[Travel to Date] datetime,[Employee ID] varchar(50),[Employee Name] varchar(50),
Designation varchar(50),[Travel Purpose] varchar(50), Approver varchar(50),[Booking Type] varchar(50), [Amount Payable to TA] float, [Total Estimated Approved Amount] float,
[Est Travel Cost] float,[Est L and B] float,[Est Incidental Cost] float, Zone varchar(50), Location varchar(50), Department varchar(50), Status varchar(50), [Cost Center] varchar(50) collate database_default)
as
begin
declare @ActNo int
declare @BookId varchar(50)
declare @maxDate datetime
declare @minDate datetime
declare @CurrDate datetime
declare @TVBDate datetime
declare @PassName varchar(50)
declare @PassCName varchar(50)
declare @Desig varchar(50)
declare @TVLPurpose varchar(50)
declare @Approver varchar(50)
declare @BookType varchar(50)
declare @AmtPayable float
declare @TotEstAmt float
declare @EstTvlCost float
declare @EstLanB float
declare @EstIncCost float
declare @Zone varchar(50)
declare @Location varchar(50)
declare @Depart varchar(50)
declare @CostCenter varchar(50)
declare @AirCost float
declare @TrainCost float
declare @HotelCost float
declare @Status varchar(50)

set @CurrDate=getdate()
DECLARE FrmTicket CURSOR FOR select Book_Id,Activity_no,TVB_Date,Pass_Name,Pass_Contac t_Name,Purpose,Approver_Contact_Name,Book_Type,Dep artment,Cost_Center,isnull(Incidental_Cost,0),Stat us from [TATA_TRAVEL_TICKETINFO] where Status in ('Submitted','Approved','Invoiced','Booked') and Book_Id not in (select Book_Id from Tata_Travel_ExpenseInfoNew)
OPEN FrmTicket
FETCH NEXT FROM FrmTicket INTO @BookId,@ActNo,@TVBDate,@PassName,@PassCName,@TVLP urpose,@Approver,@BookType,@Depart,@CostCenter,@Es tIncCost,@Status
WHILE @@FETCH_STATUS = 0 Begin
select @Desig=Designation,@Zone=Zone_Name,@Location=Locat ion_Name from [TATA_Master_EmployeeMaster] where Emp_Code=@PassName
select @AirCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_AirwayDetails] where Temp_Activity_No=@ActNo
select @TrainCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_TrainDetails] where Temp_Activity_No=@ActNo
select @HotelCost=sum(isnull(Act_Cost,0)) from [TATA_TRAVEL_HotelDetails] where Temp_Activity_No=@ActNo
select @minDate=min(convert(datetime,tdate,105)),@maxDate =max(convert(datetime,tdate,105)) from [TATA_TRAVEL_TRAVELPLAN] where Temp_Activity_No=@ActNo and Trip_Status!='Cancelled'
set @TVBDate=@maxDate
if(@maxDate < @CurrDate)
begin
set @AmtPayable=isnull(@AirCost,0)+isnull(@TrainCost,0 )
select @EstTvlCost=sum(isnull(Est_cost,0)) from [TATA_TRAVEL_TRAVELPLAN] where temp_activity_no=@ActNo
select @EstLanB=sum(isnull(Estimated_cost,0)) from [TATA_TRAVEL_BoardingPlan] where temp_activity_no=@ActNo
set @TotEstAmt=isnull(@EstTvlCost,0)+isnull(@EstLanB,0 )+isnull(@EstIncCost,0)
set @EstLanB=isnull(@EstLanB,0)
INSERT INTO @TblOpenTRF ([Req No],[Travel from Date],[Travel to Date],[Employee ID],[Employee Name],Designation,[Travel Purpose],Approver,[Booking Type],[Amount Payable to TA],
[Total Estimated Approved Amount],[Est Travel Cost],[Est L and B],[Est Incidental Cost],Zone,Location,Department,[Cost Center],Status)
VALUES(@BookId,@minDate,@TVBDate,@PassName,@PassCN ame,@Desig,@TVLPurpose,@Approver,@BookType,@AmtPay able,@TotEstAmt,@EstTvlCost,@EstLanB,@EstIncCost,
@Zone,@Location,@Depart,@CostCenter,@Status)
End
FETCH NEXT FROM FrmTicket INTO @BookId,@ActNo,@TVBDate,@PassName,@PassCName,@TVLP urpose,@Approver,@BookType,@Depart,@CostCenter,@Es tIncCost,@Status
END
CLOSE FrmTicket
DEALLOCATE FrmTicket
return
end;

after creating this table inlined functions i have created views to get data from @tblOpentrf which is follows

ALTER VIEW [dbo].[OpenTRFView]
AS
SELECT [Req No], [Employee ID], [Employee Name], Designation, [Travel Purpose], Approver, [Booking Type], [Amount Payable to TA],
[Total Estimated Approved Amount], [Est Travel Cost], [Est L and B], [Est Incidental Cost], Zone, Location, Department, Status, [Cost Center],
[Travel from Date], [Travel to Date]
FROM dbo.OpenTRF() AS OpenTRF_1

after that i have used following query to display in front end

Select OpenTRFView.[Req No][Req No],OpenTRFView.[Travel from Date][Travel from Date],OpenTRFView.[Travel to Date][Travel to Date],OpenTRFView.[Employee ID][Employee ID],OpenTRFView.[Employee Name][Employee Name],OpenTRFView.[Designation][Designation],OpenTRFView.[Travel Purpose][Travel Purpose],OpenTRFView.[Approver][Approver],OpenTRFView.[Booking Type][Booking Type],OpenTRFView.[Amount Payable to TA][Amount Payable to TA],OpenTRFView.[Total Estimated Approved Amount][Total Estimated Approved Amount],OpenTRFView.[Est Travel Cost][Est Travel Cost],OpenTRFView.[Est L and B][Est L and B],OpenTRFView.[Est Incidental Cost][Est Incidental Cost],OpenTRFView.[Zone][Zone],OpenTRFView.[Location][Location],OpenTRFView.[Department][Department],OpenTRFView.[Cost Center][Cost Center],OpenTRFView.[Status][Status] from OpenTRFView

My problem here is if there is huge number of records the query takes huge time to execute around one hour i need reduce this into 10 or 15 seconds kindly help

Aravind.T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:04:23

why are you using cursors? cant you use set based approach?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-21 : 04:36:44

Dear vikash,

Can you explain in detail, i didnt come across set based approach in the past

Aravind.T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:10:07

can you state your requirement using some sample data? then I will be able to give you an accurate suggestion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-23 : 00:28:38

Dear visakh,

thanks for ur reply,

even i tried with single query, but its not returning the records as the above query returns...

select [dbo].[TATA_TRAVEL_TicketInfo].Book_Id[Req No],
(min(convert(datetime,[dbo].[TATA_TRAVEL_TravelPlan].tdate,105)))[Travel From Date],
(max(convert(datetime,[dbo].[TATA_TRAVEL_TravelPlan].tdate,105)))[Travel To Date],
[dbo].[TATA_TRAVEL_TicketInfo].Pass_Name[Employee id],
[dbo].[TATA_TRAVEL_TicketInfo].Pass_Contact_Name[Employee Name],
[dbo].[TATA_TRAVEL_TicketInfo].Purpose,
[dbo].[TATA_TRAVEL_TicketInfo].Approver_Contact_Name[Approver],
[dbo].[TATA_TRAVEL_TicketInfo].Book_Type[Book Type],
((isnull(sum(isnull([dbo].[TATA_TRAVEL_AirwayDetails ].Act_cost,0)),0)) + (isnull(sum(isnull([dbo].[TATA_TRAVEL_TrainDetails ].Act_cost,0)),0)))[Amount Payable To TA],
((isnull(sum(isnull([dbo].[TATA_TRAVEL_TravelPlan].est_cost,0)),0))+ (isnull(sum(isnull([dbo].[TATA_TRAVEL_Boardingplan].estimated_cost,0)),0))+ (isnull(sum(isnull([tata_travel_ticketinfo].Incidental_cost,0)),0)))[Total Estimated Approved Amount],
(isnull(sum(isnull([dbo].[TATA_TRAVEL_TravelPlan].est_cost,0)),0))[Est Travel Cost],
(isnull(sum(isnull([dbo].[TATA_TRAVEL_Boardingplan].estimated_cost,0)),0))[Est landb Cost],
isnull([tata_travel_ticketinfo].Incidental_Cost,0)[Est Incidental Cost],
[dbo].[TATA_Master_EmployeeMaster].Zone_Name[Zone],
[dbo].[TATA_Master_EmployeeMaster].Location_Name[Location],
[dbo].[TATA_TRAVEL_TicketInfo].department[department],
[dbo].[TATA_TRAVEL_TicketInfo].cost_center[Cost Center],
[dbo].[TATA_TRAVEL_TicketInfo].Status[Status] from [dbo].[TATA_TRAVEL_TicketInfo] inner join
[dbo].[TATA_TRAVEL_TravelPlan] on [dbo].[TATA_TRAVEL_TicketInfo].activity_no = [dbo].[TATA_TRAVEL_TravelPlan].Temp_activity_no
inner join [dbo].[TATA_TRAVEL_AirwayDetails ] on [dbo].[TATA_TRAVEL_TicketInfo].activity_no = [dbo].[TATA_TRAVEL_AirwayDetails ].Temp_activity_no
inner join [dbo].[TATA_TRAVEL_TrainDetails ] on [dbo].[TATA_TRAVEL_TicketInfo].activity_no = [dbo].[TATA_TRAVEL_TrainDetails ].Temp_activity_no
inner join [dbo].[TATA_TRAVEL_Boardingplan] on [dbo].[TATA_TRAVEL_TicketInfo].activity_no = [dbo].[TATA_TRAVEL_Boardingplan].Temp_activity_no
inner join [dbo].[tata_master_employeemaster] on [dbo].[TATA_TRAVEL_TicketInfo].pass_name=[dbo].[TATA_Master_EmployeeMaster].emp_code
group by [dbo].[TATA_TRAVEL_TicketInfo].Book_id,[dbo].[TATA_TRAVEL_TicketInfo].Pass_name,[dbo].[TATA_TRAVEL_TicketInfo].Pass_Contact_name,[dbo].[TATA_TRAVEL_TicketInfo].Purpose,[dbo].[TATA_TRAVEL_TicketInfo].Approver_Contact_Name,[dbo].[TATA_TRAVEL_TicketInfo].Book_Type,[dbo].[TATA_TRAVEL_TicketInfo].Incidental_Cost,[dbo].[TATA_Master_EmployeeMaster].Zone_Name,[dbo].[TATA_Master_EmployeeMaster].Location_Name,[dbo].[TATA_TRAVEL_TicketInfo].Department,[dbo].[TATA_TRAVEL_TicketInfo].Cost_Center,[dbo].[TATA_TRAVEL_TicketInfo].Status

Actualy the in my concept we are using one table named tata_travel_ticketinfo with book_id as primarykey and it contains employee travel details with status invoi and activity_no as foreign key...There are anther two tables tata_travel_traindetails(train costs),tata_travel_airwaydetails(air cost) where it contains traveldetails and airway details of book id's in tata_travel_ticketinfo.....actually yhe employee after finishing the travel will create expense for the travel book id's...my query is to get the details of the book id's for which the expense is not created along with the sum of train cost and air cost as total estimated approved with employee name,designation,approver name ,status. But the above which i tried using join is fast but it violates many rows for ex: if the train cost or air cost is empty it violates that row...it just return the rows which has which are not empty....

Aravind.T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 04:53:18

post some sample data from tables and then explain what you want. Its quite difficult to look into query and understand what you're trying to achieve

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 06:29:27

I've had a quick look at your function and I think you could replace the whole CURSOR with a single query that uses LEFT JOINS.

You asked:

quote:

Dear vikash,

Can you explain in detail, i didnt come across set based approach in the past

Aravind.T

A set based approach whould do the calculation for *every* relevant row in one go. You current approach gets a set of data. Then for *each row* in that data you are calling at least 4 additional select statements on another table.

Each select statement requires (at best) an index seek so if your dataset is 1000 lines long then you are doing (at least) 4000 queries.

It looks like you only need to do one.

Please post the following:

The DDL (the structure) of the tables involved : [TATA_Master_EmployeeMaster], [TATA_TRAVEL_AirwayDetails], [TATA_TRAVEL_TrainDetails], [TATA_TRAVEL_HotelDetails], [TATA_TRAVEL_TRAVELPLAN], [TATA_TRAVEL_BoardingPlan]
Some sample data for each : (doesn't have to be much -- just two different sets of data (For two passengers or whatever unit you are reporting on)

And the expected results

Throw away what you have right now -- it is absolutely not the way to solve this problem.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-23 : 08:44:17

tata_master_employeemaster table:

Activity_no Emp_code Emp_name designation zone_name Loction_name
981 3812255 Chowdhury National Manager South Bangalore
982 3813889 Chandra Senior Executive West Mumbai

likewise it contains records for 5000employees with some other column names..

Tata_travel_ticketinfo:

Temp_activity_no Activity_no Book_id TVB_Date Pass_name
15 15 TVB004 2010-04-07 3813997

pass_contactname Purpose Approver_Contact_name travelplan
Avhad,Mahesh S. Teammeet Gurajala,Rajkumar V TATA_TRAVEL_TravelPlan

boardingplan traindetails

TATA_TRAVEL_BoardingPlan(tablename) TATA_TRAVEL_TrainDetails(table name)

tata_Airwaydetails status incidental_cost
tata_travel_airwaydetails Invoiced 5000Text

Tata_travel_travelplan table:

Activity_no Temp_activity_no city fcity Tdate Est_cost
16 15 chennai trichy 2010-12-31 500
17 18 trichy chennai 2010-12-28 500

Tata_travel_traindetails:

Activity_no Temp_activity_no tcity fcity Tdate act_cost

now i
16 15 chennai trichy 2010-12-31 500
17 18 trichy chennai 2010-12-28 500

tata_travel_airwaydetails
as same as tata_travel_traindetails...for some it may not available as they didnot travel by airways

tata_travel_boardingplan:
as same as tata_travel_traindetails...for some it may not available as they didnot boarded.

1.now i need the book id from tata_travelticketinfo with status booked,invoiced,approved with employee name,designation,zone name,location name from tata_master_employeemaster
2. sum of act_cost from tata_travel_train details and tata_travel_airwaydetails(if nullor empty rows in any one of the tables for particular book id it should be replaced as zero and sum is calculated for the book id in tata_travel_ticketinfo) as column name [Amount payable to ta[.
3. sum of est_cost from tata_travel_travelplan and estimated_cost from tata_travel_boardingplan(if nullor empty rows in any one of the tables for particular book id it should be replaced as zero and sum is calculated for the book id in tata_travel_ticketinfo) and incidental cost from tata_travel_ticketinfo as column name [Total Estimated Approved Amount]
4. estimate_cost from tata_travel_travelplan (if it is null or empty rows in any one of the tables it should be replaced as zero ) as column name [Est Travel Cost]
5. estimate_cost from tata_travel_boardingplan (if it is null or empty rows in any one of the tables it should be replaced as zero ) as column name [Est l and cost ]
and finally incidenatl cost,status

Aravind.T

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-23 : 09:51:40

tata_master_employeemaster table:

Activity_no Emp_code Emp_name designation zone_name Loction_name
981 3812255 Chowdhury National Manager South Bangalore
982 3813889 Chandra Senior Executive West Mumbai

likewise it contains records for 5000employees with some other column names..

Tata_travel_ticketinfo table:

Temp_activity_no Activity_no Book_id TVB_Date Pass_name
15 15 TVB004 2010-04-07 3813997

pass_contactname Purpose Approver_Contact_name travelplan
Avhad,Mahesh S. Teammeet Gurajala,Rajkumar V TATA_TRAVEL_TravelPlan

boardingplan traindetails

TATA_TRAVEL_BoardingPlan(tablename) TATA_TRAVEL_TrainDetails(table name)

tata_Airwaydetails status incidental_cost
tata_travel_airwaydetails Invoiced 5000

Tata_travel_travelplan table:

Activity_no Temp_activity_no city fcity Tdate Est_cost
16 15 chennai trichy 2010-12-31 500
17 18 trichy chennai 2010-12-28 500

Tata_travel_traindetails table:

Activity_no Temp_activity_no tcity fcity Tdate act_cost

16 15 chennai trichy 2010-12-31 500
17 18 trichy chennai2010-12-28 500

tata_travel_airwaydetails table:
as same as tata_travel_traindetails...for some it may not available as they didnot travel by airways

tata_travel_boardingplan table:
as same as tata_travel_traindetails...for some it may not available as they didnot boarded.

1.now i need the book id from tata_travelticketinfo with status booked,invoiced,approved with employee name,designation,zone name,location name from tata_master_employeemaster
2. sum of act_cost from tata_travel_train details and tata_travel_airwaydetails(if nullor empty rows in any one of the tables for particular book id it should be replaced as zero and sum is calculated for the book id in tata_travel_ticketinfo) as column name [Amount payable to ta[.
3. sum of est_cost from tata_travel_travelplan and estimated_cost from tata_travel_boardingplan(if nullor empty rows in any one of the tables for particular book id it should be replaced as zero and sum is calculated for the book id in tata_travel_ticketinfo) and incidental cost from tata_travel_ticketinfo as column name [Total Estimated Approved Amount]
4. estimate_cost from tata_travel_travelplan (if it is null or empty rows in any one of the tables it should be replaced as zero ) as column name [Est Travel Cost]
5. estimate_cost from tata_travel_boardingplan (if it is null or empty rows in any one of the tables it should be replaced as zero ) as column name [Est l and cost ]
and finally incidenatl cost,status

Aravind.T

Aravind.T

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 10:39:12

Hi Aravind.

I'd like to help you, and I'm sure other would also but we're generally pretty busy people. I don't want to have to take what you've given and try and guess about the datatypes of the columns of the tables and then write some SQL to put the data you've posted into them.

Can you please give us data like this:


-- tata_master_employeemaster table:

CREATE TABLE #tata_master_employeemaster (
	Activity_no       <DATATYPE>
	, Emp_code        <DATATYPE>
	, Emp_name        <DATATYPE>
	, designation     <DATATYPE>   
	, zone_name       <DATATYPE>
	, Loction_name    <DATATYPE>
	)

INSERT #tata_master_employeemaster
      SELECT 981, 3812255, 'Chowdhury', 'National Manager', 'South', 'Bangalore'
UNION SELECT 982, 3813889, 'Chandra', 'Senior Executive', 'West Mumbai'

For each of the tables?

Otherwise we have to *guess* what data goes where and what the columns data types are.

Hint -- you can SCRIPT OUT the tables involved (right click and go to SCRIPT TABLE AS....) in management studio

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-24 : 01:44:22

Dear Charlie,

Thanks for ur patient reply, Kindly help me in this regard

Tata_master_employeemaster table:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_Master_EmployeeMaster] Script Date: 08/24/2010 10:50:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_Master_EmployeeMaster](
[Template_No] [bigint] NULL,
[created_by] [varchar](50) NULL,
[Created_on] [datetime] NULL,
[Activity_No] [bigint] IDENTITY(1,1) NOT NULL,
[Emp_Code] [varchar](250) NULL,
[Emp_Name] [nvarchar](250) NULL,
[Dept_Code] [nvarchar](50) NULL,
[Designation] [nvarchar](250) NULL,
[CostCenter_Code] [nvarchar](50) NULL,
[Job_Code] [nvarchar](50) NULL,
[Job_Function] [nvarchar](100) NULL,
[SubFunction_Code] [nvarchar](50) NULL,
[SubFunction] [nvarchar](50) NULL,
[Location_Code] [nvarchar](50) NULL,
[Location_Name] [nvarchar](250) NULL,
[Branch_Code] [nvarchar](50) NULL,
[Branch_Name] [nvarchar](200) NULL,
[Zone_Code] [nvarchar](50) NULL,
[Zone_Name] [nvarchar](100) NULL,
[State_Code] [nvarchar](50) NULL,
[State_Name] [nvarchar](100) NULL,
[Email_Id] [nvarchar](50) NULL,
[Mobile_No] [nvarchar](50) NULL,
[Grade_Code] [nvarchar](50) NULL,
[Grade_Name] [nvarchar](200) NULL,
[CreditCard_Number] [nvarchar](50) NULL,
[Bank_Name] [nvarchar](50) NULL,
[Reimbursement_Bank_Ac_No] [nvarchar](50) NULL,
[Supervisor_Name] [nvarchar](250) NULL,
[Supervisor_Designation] [nvarchar](250) NULL,
[Emp_Status] [nvarchar](50) NULL,
[PanCard_Number] [nvarchar](50) NULL,
[Create_TVP] [varchar](10) NULL,
[Approve_TVP] [varchar](10) NULL,
[Approve_Exp] [varchar](10) NULL,
[Sap_Status] [char](1) NULL,
[create_exp] [varchar](10) NULL,
[SUPERVISOR_ID] [float] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [EEMSPROD].[dbo].[TATA_Master_EmployeeMaster]
([Template_No]
,[created_by]
,[Created_on]
,[Emp_Code]
,[Emp_Name]
,[Dept_Code]
,[Designation]
,[CostCenter_Code]
,[Job_Code]
,[Job_Function]
,[SubFunction_Code]
,[SubFunction]
,[Location_Code]
,[Location_Name]
,[Branch_Code]
,[Branch_Name]
,[Zone_Code]
,[Zone_Name]
,[State_Code]
,[State_Name]
,[Email_Id]
,[Mobile_No]
,[Grade_Code]
,[Grade_Name]
,[CreditCard_Number]
,[Bank_Name]
,[Reimbursement_Bank_Ac_No]
,[Supervisor_Name]
,[Supervisor_Designation]
,[Emp_Status]
,[PanCard_Number]
,[Create_TVP]
,[Approve_TVP]
,[Approve_Exp]
,[Sap_Status]
,[create_exp]
,[SUPERVISOR_ID])
VALUES
(<Template_No, bigint,>
,<created_by, varchar(50),>
,<Created_on, datetime,>
,<Emp_Code, varchar(250),>
,<Emp_Name, nvarchar(250),>
,<Dept_Code, nvarchar(50),>
,<Designation, nvarchar(250),>
,<CostCenter_Code, nvarchar(50),>
,<Job_Code, nvarchar(50),>
,<Job_Function, nvarchar(100),>
,<SubFunction_Code, nvarchar(50),>
,<SubFunction, nvarchar(50),>
,<Location_Code, nvarchar(50),>
,<Location_Name, nvarchar(250),>
,<Branch_Code, nvarchar(50),>
,<Branch_Name, nvarchar(200),>
,<Zone_Code, nvarchar(50),>
,<Zone_Name, nvarchar(100),>
,<State_Code, nvarchar(50),>
,<State_Name, nvarchar(100),>
,<Email_Id, nvarchar(50),>
,<Mobile_No, nvarchar(50),>
,<Grade_Code, nvarchar(50),>
,<Grade_Name, nvarchar(200),>
,<CreditCard_Number, nvarchar(50),>
,<Bank_Name, nvarchar(50),>
,<Reimbursement_Bank_Ac_No, nvarchar(50),>
,<Supervisor_Name, nvarchar(250),>
,<Supervisor_Designation, nvarchar(250),>
,<Emp_Status, nvarchar(50),>
,<PanCard_Number, nvarchar(50),>
,<Create_TVP, varchar(10),>
,<Approve_TVP, varchar(10),>
,<Approve_Exp, varchar(10),>
,<Sap_Status, char(1),>
,<create_exp, varchar(10),>
,<SUPERVISOR_ID, float,>)

TATA_TRAVEL_TICKETINFO:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_TicketInfo] Script Date: 08/24/2010 10:54:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_TicketInfo](
[Template_No] [bigint] NULL,
[created_by] [varchar](50) NULL,
[Created_on] [datetime] NULL,
[Activity_no] [bigint] IDENTITY(1,1) NOT NULL,
[Prefix_Activity_Id] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Status_Id] [varchar](50) NULL,
[Button_Id] [bigint] NULL,
[WF_ID] [bigint] NULL,
[Travel_Id] [varchar](200) NULL,
[Book_Id] [varchar](200) NULL,
[TVB_Date] [datetime] NULL,
[Travel_Type] [varchar](200) NULL,
[Req_For] [varchar](200) NULL,
[Pass_Name] [varchar](200) NULL,
[Pass_Contact_Name] [varchar](200) NULL,
[Designation] [varchar](250) NULL,
[Department] [varchar](200) NULL,
[DepartmentCode] [varchar](100) NULL,
[Book_Type] [varchar](200) NULL,
[Book_Confirm] [varchar](200) NULL,
[Travel_Agent] [varchar](200) NULL,
[Gl_Code] [varchar](200) NULL,
[Approver] [varchar](200) NULL,
[Approver_Contact_Name] [varchar](200) NULL,
[Purpose] [varchar](400) NULL,
[Sex] [varchar](200) NULL,
[Age] [varchar](500) NULL,
[Branch] [varchar](200) NULL,
[Cost_Center] [varchar](200) NULL,
[App_EmailId] [varchar](200) NULL,
[Mobile_No] [varchar](200) NULL,
[Travel_Plan] [varchar](200) NULL,
[Board_Plan] [varchar](200) NULL,
[Train_Details] [varchar](200) NULL,
[Flight_Details] [varchar](200) NULL,
[Hotel_Details] [varchar](200) NULL,
[Cab_Details] [varchar](200) NULL,
[ETicket1] [varchar](200) NULL,
[ETicket2] [varchar](200) NULL,
[ETicket3] [varchar](200) NULL,
[ETicket4] [varchar](200) NULL,
[ETicket5] [varchar](200) NULL,
[Status] [varchar](200) NULL,
[User_EmailId] [varchar](200) NULL,
[Booking_Date] [datetime] NULL,
[Incidental_Cost] [float] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

TATA_TRAVEL_TRAVELPLAN:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_TravelPlan ] Script Date: 08/24/2010 10:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_TravelPlan ](
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Rtype] [varchar](200) NULL,
[Fcity] [varchar](200) NULL,
[Tcity] [varchar](200) NULL,
[Ocity] [varchar](200) NULL,
[Tdate] [datetime] NULL,
[Tclass] [varchar](200) NULL,
[Ftime] [varchar](200) NULL,
[Ttime] [varchar](200) NULL,
[Est_cost] [float] NULL,
[Remarks] [varchar](200) NULL,
[Trip_Status] [varchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [EEMS].[dbo].[TATA_TRAVEL_TravelPlan ]
([Project_Id]
,[Template_No]
,[Activity_No]
,[Temp_Activity_No]
,[Responsible_User]
,[Updated_DateTime]
,[REVISION_NO]
,[Rtype]
,[Fcity]
,[Tcity]
,[Ocity]
,[Tdate]
,[Tclass]
,[Ftime]
,[Ttime]
,[Est_cost]
,[Remarks]
,[Trip_Status])
VALUES
(<Project_Id, varchar(10),>
,<Template_No, bigint,>
,<Activity_No, bigint,>
,<Temp_Activity_No, bigint,>
,<Responsible_User, varchar(50),>
,<Updated_DateTime, datetime,>
,<REVISION_NO, bigint,>
,<Rtype, varchar(200),>
,<Fcity, varchar(200),>
,<Tcity, varchar(200),>
,<Ocity, varchar(200),>
,<Tdate, datetime,>
,<Tclass, varchar(200),>
,<Ftime, varchar(200),>
,<Ttime, varchar(200),>
,<Est_cost, float,>
,<Remarks, varchar(200),>
,<Trip_Status, varchar(200),>)

TATA_TRAVEL_BOARDINGPLAN:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_BoardingPlan ] Script Date: 08/24/2010 10:57:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_BoardingPlan ](
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Request_Type] [varchar](200) NULL,
[Travel_Agent] [varchar](200) NULL,
[City] [varchar](200) NULL,
[Room_Type] [varchar](200) NULL,
[Estimated_Cost] [float] NULL,
[BDate] [datetime] NULL,
[Stay_Days] [bigint] NULL,
[Stay_Nights] [bigint] NULL,
[Remarks] [varchar](200) NULL,
[Trip_Status] [varchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [EEMS].[dbo].[TATA_TRAVEL_BoardingPlan ]
([Project_Id]
,[Template_No]
,[Activity_No]
,[Temp_Activity_No]
,[Responsible_User]
,[Updated_DateTime]
,[REVISION_NO]
,[Request_Type]
,[Travel_Agent]
,[City]
,[Room_Type]
,[Estimated_Cost]
,[BDate]
,[Stay_Days]
,[Stay_Nights]
,[Remarks]
,[Trip_Status])
VALUES
(<Project_Id, varchar(10),>
,<Template_No, bigint,>
,<Activity_No, bigint,>
,<Temp_Activity_No, bigint,>
,<Responsible_User, varchar(50),>
,<Updated_DateTime, datetime,>
,<REVISION_NO, bigint,>
,<Request_Type, varchar(200),>
,<Travel_Agent, varchar(200),>
,<City, varchar(200),>
,<Room_Type, varchar(200),>
,<Estimated_Cost, float,>
,<BDate, datetime,>
,<Stay_Days, bigint,>
,<Stay_Nights, bigint,>
,<Remarks, varchar(200),>
,<Trip_Status, varchar(200),>)

TATA_TRAVEL_TRAINDETAILS:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_TrainDetails ] Script Date: 08/24/2010 10:58:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_TrainDetails ](
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Fcity] [varchar](200) NULL,
[Tcity] [varchar](200) NULL,
[Tdate] [datetime] NULL,
[Train_No] [varchar](200) NULL,
[Train_Name] [varchar](200) NULL,
[Train_DTime] [varchar](200) NULL,
[Train_ATime] [varchar](200) NULL,
[Train_class] [varchar](200) NULL,
[Act_cost] [float] NULL,
[Train_Tid] [varchar](200) NULL,
[Tcdate] [datetime] NULL,
[TCCharge] [varchar](200) NULL,
[Trefund] [varchar](200) NULL,
[Train_pnrno] [varchar](200) NULL,
[Inv_No] [varchar](200) NULL,
[Remarks] [varchar](200) NULL,
[Ticket_received] [varchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [EEMS].[dbo].[TATA_TRAVEL_TrainDetails ]
([Project_Id]
,[Template_No]
,[Activity_No]
,[Temp_Activity_No]
,[Responsible_User]
,[Updated_DateTime]
,[REVISION_NO]
,[Fcity]
,[Tcity]
,[Tdate]
,[Train_No]
,[Train_Name]
,[Train_DTime]
,[Train_ATime]
,[Train_class]
,[Act_cost]
,[Train_Tid]
,[Tcdate]
,[TCCharge]
,[Trefund]
,[Train_pnrno]
,[Inv_No]
,[Remarks]
,[Ticket_received])
VALUES
(<Project_Id, varchar(10),>
,<Template_No, bigint,>
,<Activity_No, bigint,>
,<Temp_Activity_No, bigint,>
,<Responsible_User, varchar(50),>
,<Updated_DateTime, datetime,>
,<REVISION_NO, bigint,>
,<Fcity, varchar(200),>
,<Tcity, varchar(200),>
,<Tdate, datetime,>
,<Train_No, varchar(200),>
,<Train_Name, varchar(200),>
,<Train_DTime, varchar(200),>
,<Train_ATime, varchar(200),>
,<Train_class, varchar(200),>
,<Act_cost, float,>
,<Train_Tid, varchar(200),>
,<Tcdate, datetime,>
,<TCCharge, varchar(200),>
,<Trefund, varchar(200),>
,<Train_pnrno, varchar(200),>
,<Inv_No, varchar(200),>
,<Remarks, varchar(200),>
,<Ticket_received, varchar(200),>)
TATA_TRAVEL_AIRWAYDETAILS:

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_AirwayDetails ] Script Date: 08/24/2010 10:59:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_AirwayDetails ](
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Fcity] [varchar](200) NULL,
[Tcity] [varchar](200) NULL,
[Tdate] [datetime] NULL,
[Flight_No] [varchar](200) NULL,
[Flight_Name] [varchar](200) NULL,
[Flight_DTime] [varchar](200) NULL,
[Flight_ATime] [varchar](200) NULL,
[Flight_class] [varchar](200) NULL,
[Act_cost] [float] NULL,
[Flight_Tid] [varchar](200) NULL,
[Fcdate] [datetime] NULL,
[FCCharge] [varchar](200) NULL,
[Frefund] [varchar](200) NULL,
[Flight_pnrno] [varchar](200) NULL,
[Inv_No] [varchar](200) NULL,
[Remarks] [varchar](200) NULL,
[Ticket_received] [varchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

TATA_TRAVEL_HOTELDETAILS

USE [EEMS]
GO
/****** Object: Table [dbo].[TATA_TRAVEL_HotelDetails ] Script Date: 08/24/2010 11:00:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TATA_TRAVEL_HotelDetails ](
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[HotelName] [varchar](200) NULL,
[others] [varchar](200) NULL,
[City] [varchar](200) NULL,
[Phone] [bigint] NULL,
[Act_cost] [float] NULL,
[ChkIn_Date] [datetime] NULL,
[ChkIn_Time] [varchar](200) NULL,
[ChkOut_Date] [datetime] NULL,
[ChkOut_time] [varchar](200) NULL,
[Room_Type] [varchar](200) NULL,
[Reservation_No] [varchar](200) NULL,
[Inv_No] [varchar](200) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT INTO [EEMS].[dbo].[TATA_TRAVEL_HotelDetails ]
([Project_Id]
,[Template_No]
,[Activity_No]
,[Temp_Activity_No]
,[Responsible_User]
,[Updated_DateTime]
,[REVISION_NO]
,[HotelName]
,[others]
,[City]
,[Phone]
,[Act_cost]
,[ChkIn_Date]
,[ChkIn_Time]
,[ChkOut_Date]
,[ChkOut_time]
,[Room_Type]
,[Reservation_No]
,[Inv_No])
VALUES
(<Project_Id, varchar(10),>
,<Template_No, bigint,>
,<Activity_No, bigint,>
,<Temp_Activity_No, bigint,>
,<Responsible_User, varchar(50),>
,<Updated_DateTime, datetime,>
,<REVISION_NO, bigint,>
,<HotelName, varchar(200),>
,<others, varchar(200),>
,<City, varchar(200),>
,<Phone, bigint,>
,<Act_cost, float,>
,<ChkIn_Date, datetime,>
,<ChkIn_Time, varchar(200),>
,<ChkOut_Date, datetime,>
,<ChkOut_time, varchar(200),>
,<Room_Type, varchar(200),>
,<Reservation_No, varchar(200),>
,<Inv_No, varchar(200),>)

These are the tables am using in my concept. The Insert details are done at front end . when a new employee creates login the details will tata_master_employeemaster table with unique employee id.When the employee creates travel for business purpose the details will be stored in tata_travel_ticketinfo also details if it is by train the details will be stored in tata_travel_traindetails or airways means airdetails table.once he creates the travel it will be in submitted status at tata_travel_ticketinfo.Once it is approved by his higher level authority it will be in approved status after that travel agent can invoice it....Now i need the details(ie., report) of book id which are in submitted,approved,invoiced status with sum of act_cost from traindetails and airway details(if user specifies hotel details hotel details also) also sum of estimated cost of travelPlan and boardingplan with status

Regards,

Aravind.T

Aravind.T

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 05:31:06

OK -- we are getting somewhere.

You still haven't provided any data though. The VALUES are all like

VALUES (
<Project_Id, varchar(10),>
...
...
...
)

Can you please provide sample data for each table (just 2 example people will be fine).

Once we have the data and the tables then we'll be able understand the relationships and help you with your query.

-- almost there!!!
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-24 : 06:15:12

tata_master_employeemaster:

INSERT tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3813957',convert(datetime,'2010-06-17 15:18:05.543',121),'52','919856','52','26','29','1','','TVB021',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3813957','Iyer,Ayyappan','Sales Manager','BROCA','3E','TravelAgent','No','TA000',NULL,'3808270','M,Anand','for vacation','Male','35','','MH383ECOMM','M.Anand@TATA-AIG.com','','DE30_TATA_TRAVEL_TravelPlan','DE6_TATA_TRAVEL_BoardingPlan','DE11_TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails','','','','','','Booked','',convert(datetime,NULL,121),'5000')
INSERT tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3808819',convert(datetime,'2010-06-17 18:41:49.077',121),'54','919856','24','26','29','1','','TVB022',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3808819','Kanade,Aditya','Vice President & Head - Internal Audit ','INTERNAL AUDIT','JA','TravelAgent','No','TA000',NULL,'3935313','M,Suresh','asdasd','Male','34','HO01','','M.Suresh@TATA-AIG.com','91-20-25411102','DE31_TATA_TRAVEL_TravelPlan','TATA_TRAVEL_BoardingPlan','TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails',NULL,NULL,NULL,NULL,NULL,'invoiced','Aditya.Kanade@TATA-AIG.com',convert(datetime,NULL,121),'5656')

Aravind.T

kvt.aravind
Starting Member

24 Posts

Posted - 2010-08-24 : 06:29:58

here with i have attached the travel details of an employee

kindly ignore the above reply:

tata_master_employeemaster:

INSERT into tata_master_employeemaster(Template_No,created_by,Created_on,Activity_No,Emp_Code,Emp_Name,Dept_Code,Designation,CostCenter_Code,Job_Code,Job_Function,SubFunction_Code,SubFunction,Location_Code,Location_Name,Branch_Code,Branch_Name,Zone_Code,Zone_Name,State_Code,State_Name,Email_Id,Mobile_No,Grade_Code,Grade_Name,CreditCard_Number,Bank_Name,Reimbursement_Bank_Ac_No,Supervisor_Name,Supervisor_Designation,Emp_Status,PanCard_Number,Create_TVP,Approve_TVP,Approve_Exp,Sap_Status,create_exp,SUPERVISOR_ID) VALUES('91980019','Deepa',convert(datetime,'2008-10-31 09:20:18.467',121),'942','3813957','Iyer,Ayyappan','3E','Sales Manager','MH383ECOMM','000007','Broking','601INDW075','Key Accounts','037','Nagpur','712','Nagpur','WST','West','MH','Maharashtra','Ayyappan.Iyer@tata-aig.com','0712 - 6581568','301','Assistant Managers','','HDFC BANK','01021040000150','Ramani,Rajeev','Area Sales Manager ','A',NULL,NULL,NULL,NULL,NUL,NULL,'3.80873e+006')
INSERT into tata_master_employeemaster(Template_No,created_by,Created_on,Activity_No,Emp_Code,Emp_Name,Dept_Code,Designation,CostCenter_Code,Job_Code,Job_Function,SubFunction_Code,SubFunction,Location_Code,Location_Name,Branch_Code,Branch_Name,Zone_Code,Zone_Name,State_Code,State_Name,Email_Id,Mobile_No,Grade_Code,Grade_Name,CreditCard_Number,Bank_Name,Reimbursement_Bank_Ac_No,Supervisor_Name,Supervisor_Designation,Emp_Status,PanCard_Number,Create_TVP,Approve_TVP,Approve_Exp,Sap_Status,create_exp,SUPERVISOR_ID) VALUES('91980019','Deepa',convert(datetime,'2008-10-31 09:17:53.747',121),'123','3808819','Kanade,Aditya','JA','Vice President & Head - Internal Audit ',NULL,'771X20','20','201','Internal Audit','MH16','Mumbai','HO01','Mumbai - Peninsula Tower 1','0HO','Head Office','MH','Maharashtra','Aditya.Kanade@TATA-AIG.com','91-20-25411102','702','Vice President Band 1','','HDFC BANK','00071050071000','M,Suresh','Managing Director','A',NULL,'3808835',NULL,NULL,NUL,NULL,'3.93531e+006')

tata_travel_ticketinfo:

INSERT INTO tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3813957',convert(datetime,'2010-06-17 15:18:05.543',121),'52','919856','52','26','29','1','','TVB021',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3813957','Iyer,Ayyappan','Sales Manager','BROCA','3E','TravelAgent','No','TA000',NULL,'3808270','M,Anand','for vacation','Male','35','','MH383ECOMM','M.Anand@TATA-AIG.com','','DE30_TATA_TRAVEL_TravelPlan','DE6_TATA_TRAVEL_BoardingPlan','DE11_TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails','','','','','','Booked','',convert(datetime,NULL,121),'5000')
INSERT tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3808819',convert(datetime,'2010-06-17 18:41:49.077',121),'54','919856','24','26','29','1','','TVB022',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3808819','Kanade,Aditya','Vice President & Head - Internal Audit ','INTERNAL AUDIT','JA','TravelAgent','No','TA000',NULL,'3935313','M,Suresh','asdasd','Male','34','HO01','','M.Suresh@TATA-AIG.com','91-20-25411102','DE31_TATA_TRAVEL_TravelPlan','TATA_TRAVEL_BoardingPlan','TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails',NULL,NULL,NULL,NULL,NULL,'invoiced','Aditya.Kanade@TATA-AIG.com',convert(datetime,NULL,121),'5656')

tata_travel_travelplan:

INSERT into tata_travel_travelplan(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Rtype,Fcity,Tcity,Ocity,Tdate,Tclass,Ftime,Ttime,Est_cost,Remarks,Trip_Status) VALUES('','91985633','30','52','TA000',convert(datetime,'2010-06-17 15:18:02.747',121),'1','Railways','Guntur','Hyderabad','',convert(datetime,'2010-06-18 00:00:00.000',121),'AC 3 Tier','','','3000','','Invoiced')
INSERT into tata_travel_travelplan(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Rtype,Fcity,Tcity,Ocity,Tdate,Tclass,Ftime,Ttime,Est_cost,Remarks,Trip_Status) VALUES('','91985633','30','52','TA000',convert(datetime,'2010-06-17 15:18:03.090',121),'2','Railways','Hyderabad','Vishakapatnam','',convert(datetime,'2010-06-18 00:00:00.000',121),'AC 2 Tier','','','2500','','Invoiced')

tata_travel_traindetails:

INSERT into tata_travel_traindetails(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Fcity,Tcity,Tdate,Train_No,Train_Name,Train_DTime,Train_ATime,Train_class,Act_cost,Train_Tid,Tcdate,TCCharge,Trefund,Train_pnrno,Inv_No,Remarks,Ticket_received) VALUES('','91985633','11','52','TA000',convert(datetime,'2010-06-17 15:18:04.543',121),'1','Guntur','Hyderabad',convert(datetime,'2010-06-18 00:00:00.000',121),'6758','red fort','','','AC 3 Tier','3000','234',convert(datetime,'2010-06-19 00:00:00.000',121),'50','2550','3232','1234','','Yes')
INSERT into tata_travel_traindetails(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Fcity,Tcity,Tdate,Train_No,Train_Name,Train_DTime,Train_ATime,Train_class,Act_cost,Train_Tid,Tcdate,TCCharge,Trefund,Train_pnrno,Inv_No,Remarks,Ticket_received) VALUES('','91985633','11','52','TA000',convert(datetime,'2010-06-17 15:18:04.670',121),'2','Hyderabad','Vishakapatnam',convert(datetime,'2010-06-18 00:00:00.000',121),'6787','tamilnadu','','','AC 2 Tier','4500','345',convert(datetime,'2010-06-19 00:00:00.000',121),'75','3550','3567','4576','','Yes')

tata_travel_airwaydetails:

Records null for particular activity_no and book id as the user selected railways. n this case the act_cost in tata_travel_airwaydetails should be treated as zero

tata_travel_boardingplan:

Records null for particular activity_no and book id as the user didnt claim it in this case the estimated cost in tata_travel_boardingplan should be treated as zero

Aravind.T

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 07:38:34

Hi Aravind.

I have produced a dataset for you with the data you provided. I've turned the tables into temp tables so that others can quickly grab them and get the same data. (THe dataset is at the end of this post).

You haven't included some of the tables used in your function (air details etc).

I think I see what you are doing wrong in your function now.

What it looks like you are trying to do is:

For each trip line in #TATA_TRAVEL_TICKETINFO you want to find the various details for that trip. So you are trying to find aggregated data from various table for a set of data in TATA_TRAVEL_TICKETINFO . It seems like you want to find:

Total cost of train tickets
Total cost of air tickets
Min and Max dates for some events.
. etc.
. etc.

The best way to do this would be to use derived tables. For example this little piece of Sql does some of what your function wanted to do and it does it without using a look:


select
	tInfo.Book_Id
	 ,tInfo.Activity_no
	, tInfo.TVB_Date
	, tInfo.Pass_Name
	, tInfo.Pass_Contact_Name
	, tInfo.Purpose
	, tInfo.Approver_Contact_Name
	, tInfo.Book_Type
	, tInfo.Department
	, tInfo.Cost_Center
	, isnull(tInfo.Incidental_Cost,0)
	, tInfo.Status

	, tc.[traincost] AS [TrainCost]

	, tp.[minTDate] AS [Min TDATE]
	, tp.[maxTDate] AS [Max TDATE]

from
	#TATA_TRAVEL_TICKETINFO AS tInfo

	-- [TATA_Master_EmployeeMaster]
	JOIN #TATA_Master_EmployeeMaster AS empMast
		ON empMast.Emp_Code = tInfo.Pass_Name

	-- Train Costs
	LEFT JOIN (
		SELECT
			SUM([Act_Cost]) AS [trainCost]
			, [temp_activity_no] AS [activity_no]
		FROM
			#TATA_TRAVEL_TrainDetails
		GROUP BY
			[temp_activity_no]
		)
		AS tc ON tc.[activity_no] = tINfo.[activity_no]
		
	-- [TATA_TRAVEL_TRAVELPLAN]
	LEFT JOIN (
		SELECT
			MIN([tDate]) AS [MinTdate]
			, MAX([tDate]) AS [MaxTdate]
			, [temp_activity_no] AS [activity_no]
		FROM
			#TATA_TRAVEL_TRAVELPLAN
		GROUP BY
			[temp_activity_no]
		)
		AS tp ON tp.[activity_no] = tInfo.[activity_no]

where
	Status in (
		'Submitted'
		,'Approved'
		,'Invoiced'
		,'Booked'
		)

I don't think I can help you more today -- I'm in dev meetings for the rest of the day. Hopefully you can see what I'm trying to say. Post questions based on this sample data and someone else can help you.

Good luck,

Charlie

-- DataSet


-- DROP / CREATE DATA TABLES

IF OBJECT_ID('tempDb..#TATA_Master_EmployeeMaster') IS NOT NULL
	DROP TABLE #TATA_Master_EmployeeMaster

CREATE TABLE #TATA_Master_EmployeeMaster(
[Template_No] [bigint] NULL,
[created_by] [varchar](50) NULL,
[Created_on] [datetime] NULL,
[Activity_No] [bigint]  NOT NULL,
[Emp_Code] [varchar](250) NULL,
[Emp_Name] [nvarchar](250) NULL,
[Dept_Code] [nvarchar](50) NULL,
[Designation] [nvarchar](250) NULL,
[CostCenter_Code] [nvarchar](50) NULL,
[Job_Code] [nvarchar](50) NULL,
[Job_Function] [nvarchar](100) NULL,
[SubFunction_Code] [nvarchar](50) NULL,
[SubFunction] [nvarchar](50) NULL,
[Location_Code] [nvarchar](50) NULL,
[Location_Name] [nvarchar](250) NULL,
[Branch_Code] [nvarchar](50) NULL,
[Branch_Name] [nvarchar](200) NULL,
[Zone_Code] [nvarchar](50) NULL,
[Zone_Name] [nvarchar](100) NULL,
[State_Code] [nvarchar](50) NULL,
[State_Name] [nvarchar](100) NULL,
[Email_Id] [nvarchar](50) NULL,
[Mobile_No] [nvarchar](50) NULL,
[Grade_Code] [nvarchar](50) NULL,
[Grade_Name] [nvarchar](200) NULL,
[CreditCard_Number] [nvarchar](50) NULL,
[Bank_Name] [nvarchar](50) NULL,
[Reimbursement_Bank_Ac_No] [nvarchar](50) NULL,
[Supervisor_Name] [nvarchar](250) NULL,
[Supervisor_Designation] [nvarchar](250) NULL,
[Emp_Status] [nvarchar](50) NULL,
[PanCard_Number] [nvarchar](50) NULL,
[Create_TVP] [varchar](10) NULL,
[Approve_TVP] [varchar](10) NULL,
[Approve_Exp] [varchar](10) NULL,
[Sap_Status] [char](1) NULL,
[create_exp] [varchar](10) NULL,
[SUPERVISOR_ID] [float] NULL
)


IF OBJECT_ID('tempDb..#TATA_TRAVEL_TicketInfo') IS NOT NULL
	DROP TABLE #TATA_TRAVEL_TicketInfo
CREATE TABLE #TATA_TRAVEL_TicketInfo(
[Template_No] [bigint] NULL,
[created_by] [varchar](50) NULL,
[Created_on] [datetime] NULL,
[Activity_no] [bigint] NOT NULL,
[Prefix_Activity_Id] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Status_Id] [varchar](50) NULL,
[Button_Id] [bigint] NULL,
[WF_ID] [bigint] NULL,
[Travel_Id] [varchar](200) NULL,
[Book_Id] [varchar](200) NULL,
[TVB_Date] [datetime] NULL,
[Travel_Type] [varchar](200) NULL,
[Req_For] [varchar](200) NULL,
[Pass_Name] [varchar](200) NULL,
[Pass_Contact_Name] [varchar](200) NULL,
[Designation] [varchar](250) NULL,
[Department] [varchar](200) NULL,
[DepartmentCode] [varchar](100) NULL,
[Book_Type] [varchar](200) NULL,
[Book_Confirm] [varchar](200) NULL,
[Travel_Agent] [varchar](200) NULL,
[Gl_Code] [varchar](200) NULL,
[Approver] [varchar](200) NULL,
[Approver_Contact_Name] [varchar](200) NULL,
[Purpose] [varchar](400) NULL,
[Sex] [varchar](200) NULL,
[Age] [varchar](500) NULL,
[Branch] [varchar](200) NULL,
[Cost_Center] [varchar](200) NULL,
[App_EmailId] [varchar](200) NULL,
[Mobile_No] [varchar](200) NULL,
[Travel_Plan] [varchar](200) NULL,
[Board_Plan] [varchar](200) NULL,
[Train_Details] [varchar](200) NULL,
[Flight_Details] [varchar](200) NULL,
[Hotel_Details] [varchar](200) NULL,
[Cab_Details] [varchar](200) NULL,
[ETicket1] [varchar](200) NULL,
[ETicket2] [varchar](200) NULL,
[ETicket3] [varchar](200) NULL,
[ETicket4] [varchar](200) NULL,
[ETicket5] [varchar](200) NULL,
[Status] [varchar](200) NULL,
[User_EmailId] [varchar](200) NULL,
[Booking_Date] [datetime] NULL,
[Incidental_Cost] [float] NULL
)

IF OBJECT_ID('tempDb..#TATA_TRAVEL_TravelPlan') IS NOT NULL
	DROP TABLE #TATA_TRAVEL_TravelPlan
CREATE TABLE #TATA_TRAVEL_TravelPlan(
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Rtype] [varchar](200) NULL,
[Fcity] [varchar](200) NULL,
[Tcity] [varchar](200) NULL,
[Ocity] [varchar](200) NULL,
[Tdate] [datetime] NULL,
[Tclass] [varchar](200) NULL,
[Ftime] [varchar](200) NULL,
[Ttime] [varchar](200) NULL,
[Est_cost] [float] NULL,
[Remarks] [varchar](200) NULL,
[Trip_Status] [varchar](200) NULL
)

IF OBJECT_ID('tempDb..#TATA_TRAVEL_BoardingPlan') IS NOT NULL
	DROP TABLE #TATA_TRAVEL_BoardingPlan
CREATE TABLE #TATA_TRAVEL_BoardingPlan(
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Request_Type] [varchar](200) NULL,
[Travel_Agent] [varchar](200) NULL,
[City] [varchar](200) NULL,
[Room_Type] [varchar](200) NULL,
[Estimated_Cost] [float] NULL,
[BDate] [datetime] NULL,
[Stay_Days] [bigint] NULL,
[Stay_Nights] [bigint] NULL,
[Remarks] [varchar](200) NULL,
[Trip_Status] [varchar](200) NULL
)

IF OBJECT_ID('tempDb..#TATA_TRAVEL_TrainDetails') IS NOT NULL
	DROP TABLE #TATA_TRAVEL_TrainDetails
CREATE TABLE #TATA_TRAVEL_TrainDetails(
[Project_Id] [varchar](10) NULL,
[Template_No] [bigint] NULL,
[Activity_No] [bigint] NULL,
[Temp_Activity_No] [bigint] NULL,
[Responsible_User] [varchar](50) NULL,
[Updated_DateTime] [datetime] NULL,
[REVISION_NO] [bigint] NULL,
[Fcity] [varchar](200) NULL,
[Tcity] [varchar](200) NULL,
[Tdate] [datetime] NULL,
[Train_No] [varchar](200) NULL,
[Train_Name] [varchar](200) NULL,
[Train_DTime] [varchar](200) NULL,
[Train_ATime] [varchar](200) NULL,
[Train_class] [varchar](200) NULL,
[Act_cost] [float] NULL,
[Train_Tid] [varchar](200) NULL,
[Tcdate] [datetime] NULL,
[TCCharge] [varchar](200) NULL,
[Trefund] [varchar](200) NULL,
[Train_pnrno] [varchar](200) NULL,
[Inv_No] [varchar](200) NULL,
[Remarks] [varchar](200) NULL,
[Ticket_received] [varchar](200) NULL
)


-- Populate Tables

INSERT #tata_master_employeemaster(Template_No,created_by,Created_on,Activity_No,Emp_Code,Emp_Name,Dept_Code,Designation,CostCenter_Code,Job_Code,Job_Function,SubFunction_Code,SubFunction,Location_Code,Location_Name,Branch_Code,Branch_Name,Zone_Code,Zone_Name,State_Code,State_Name,Email_Id,Mobile_No,Grade_Code,Grade_Name,CreditCard_Number,Bank_Name,Reimbursement_Bank_Ac_No,Supervisor_Name,Supervisor_Designation,Emp_Status,PanCard_Number,Create_TVP,Approve_TVP,Approve_Exp,Sap_Status,create_exp,SUPERVISOR_ID) VALUES('91980019','Deepa',convert(datetime,'2008-10-31 09:20:18.467',121),'942','3813957','Iyer,Ayyappan','3E','Sales Manager','MH383ECOMM','000007','Broking','601INDW075','Key Accounts','037','Nagpur','712','Nagpur','WST','West','MH','Maharashtra','Ayyappan.Iyer@tata-aig.com','0712 - 6581568','301','Assistant Managers','','HDFC BANK','01021040000150','Ramani,Rajeev','Area Sales Manager ','A',NULL,NULL,NULL,NULL,NULL,NULL,'3.80873e+006')
INSERT #tata_master_employeemaster(Template_No,created_by,Created_on,Activity_No,Emp_Code,Emp_Name,Dept_Code,Designation,CostCenter_Code,Job_Code,Job_Function,SubFunction_Code,SubFunction,Location_Code,Location_Name,Branch_Code,Branch_Name,Zone_Code,Zone_Name,State_Code,State_Name,Email_Id,Mobile_No,Grade_Code,Grade_Name,CreditCard_Number,Bank_Name,Reimbursement_Bank_Ac_No,Supervisor_Name,Supervisor_Designation,Emp_Status,PanCard_Number,Create_TVP,Approve_TVP,Approve_Exp,Sap_Status,create_exp,SUPERVISOR_ID) VALUES('91980019','Deepa',convert(datetime,'2008-10-31 09:17:53.747',121),'123','3808819','Kanade,Aditya','JA','Vice President & Head - Internal Audit ',NULL,'771X20','20','201','Internal Audit','MH16','Mumbai','HO01','Mumbai - Peninsula Tower 1','0HO','Head Office','MH','Maharashtra','Aditya.Kanade@TATA-AIG.com','91-20-25411102','702','Vice President Band 1','','HDFC BANK','00071050071000','M,Suresh','Managing Director','A',NULL,'3808835',NULL,NULL,NULL,NULL,'3.93531e+006')

-- tata_travel_ticketinfo:

INSERT #tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3813957',convert(datetime,'2010-06-17 15:18:05.543',121),'52','919856','52','26','29','1','','TVB021',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3813957','Iyer,Ayyappan','Sales Manager','BROCA','3E','TravelAgent','No','TA000',NULL,'3808270','M,Anand','for vacation','Male','35','','MH383ECOMM','M.Anand@TATA-AIG.com','','DE30_TATA_TRAVEL_TravelPlan','DE6_TATA_TRAVEL_BoardingPlan','DE11_TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails','','','','','','Booked','',convert(datetime,NULL,121),'5000')
INSERT #tata_travel_ticketinfo(Template_No,created_by,Created_on,Activity_no,Prefix_Activity_Id,Temp_Activity_No,Status_Id,Button_Id,WF_ID,Travel_Id,Book_Id,TVB_Date,Travel_Type,Req_For,Pass_Name,Pass_Contact_Name,Designation,Department,DepartmentCode,Book_Type,Book_Confirm,Travel_Agent,Gl_Code,Approver,Approver_Contact_Name,Purpose,Sex,Age,Branch,Cost_Center,App_EmailId,Mobile_No,Travel_Plan,Board_Plan,Train_Details,Flight_Details,Hotel_Details,Cab_Details,ETicket1,ETicket2,ETicket3,ETicket4,ETicket5,Status,User_EmailId,Booking_Date,Incidental_Cost) VALUES('91985633','3808819',convert(datetime,'2010-06-17 18:41:49.077',121),'54','919856','24','26','29','1','','TVB022',convert(datetime,'2010-06-17 00:00:00.000',121),'Domestic','','3808819','Kanade,Aditya','Vice President & Head - Internal Audit ','INTERNAL AUDIT','JA','TravelAgent','No','TA000',NULL,'3935313','M,Suresh','asdasd','Male','34','HO01','','M.Suresh@TATA-AIG.com','91-20-25411102','DE31_TATA_TRAVEL_TravelPlan','TATA_TRAVEL_BoardingPlan','TATA_TRAVEL_TrainDetails','TATA_TRAVEL_AirwayDetails','TATA_TRAVEL_HotelDetails','TATA_TRAVEL_CarDetails',NULL,NULL,NULL,NULL,NULL,'invoiced','Aditya.Kanade@TATA-AIG.com',convert(datetime,NULL,121),'5656')

-- tata_travel_travelplan:

INSERT #tata_travel_travelplan(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Rtype,Fcity,Tcity,Ocity,Tdate,Tclass,Ftime,Ttime,Est_cost,Remarks,Trip_Status) VALUES('','91985633','30','52','TA000',convert(datetime,'2010-06-17 15:18:02.747',121),'1','Railways','Guntur','Hyderabad','',convert(datetime,'2010-06-18 00:00:00.000',121),'AC 3 Tier','','','3000','','Invoiced')
INSERT #tata_travel_travelplan(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Rtype,Fcity,Tcity,Ocity,Tdate,Tclass,Ftime,Ttime,Est_cost,Remarks,Trip_Status) VALUES('','91985633','30','52','TA000',convert(datetime,'2010-06-17 15:18:03.090',121),'2','Railways','Hyderabad','Vishakapatnam','',convert(datetime,'2010-06-18 00:00:00.000',121),'AC 2 Tier','','','2500','','Invoiced')

-- tata_travel_traindetails:

INSERT #tata_travel_traindetails(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Fcity,Tcity,Tdate,Train_No,Train_Name,Train_DTime,Train_ATime,Train_class,Act_cost,Train_Tid,Tcdate,TCCharge,Trefund,Train_pnrno,Inv_No,Remarks,Ticket_received) VALUES('','91985633','11','52','TA000',convert(datetime,'2010-06-17 15:18:04.543',121),'1','Guntur','Hyderabad',convert(datetime,'2010-06-18 00:00:00.000',121),'6758','red fort','','','AC 3 Tier','3000','234',convert(datetime,'2010-06-19 00:00:00.000',121),'50','2550','3232','1234','','Yes')
INSERT #tata_travel_traindetails(Project_Id,Template_No,Activity_No,Temp_Activity_No,Responsible_User,Updated_DateTime,REVISION_NO,Fcity,Tcity,Tdate,Train_No,Train_Name,Train_DTime,Train_ATime,Train_class,Act_cost,Train_Tid,Tcdate,TCCharge,Trefund,Train_pnrno,Inv_No,Remarks,Ticket_received) VALUES('','91985633','11','52','TA000',convert(datetime,'2010-06-17 15:18:04.670',121),'2','Hyderabad','Vishakapatnam',convert(datetime,'2010-06-18 00:00:00.000',121),'6787','tamilnadu','','','AC 2 Tier','4500','345',convert(datetime,'2010-06-19 00:00:00.000',121),'75','3550','3567','4576','','Yes')

/*
tata_travel_airwaydetails: 

Records null for particular activity_no and book id as the user selected railways. n this case the act_cost in tata_travel_airwaydetails should be treated as zero 

tata_travel_boardingplan:

Records null for particular activity_no and book id as the user didnt claim it in this case the estimated cost in tata_travel_boardingplan should be treated as zero
*/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources