SQLTeam.com | Weblogs | Forums

Need HTML table to be incorporated in query

tsql

#1

Dear Concern,

This is the query, I need output of the query will be incorporated in the following HTML format

Date Ventura trujet zoo Nepal Mahan
BK CX Net BK CX Net BK CX Net BK CX Net Pax Count
Data will be reflectd in the third row

if(OBJECT_ID('tempdb..#tmpMultiCli') IS NOT NULL )
     DROP table #tmpMultiCli
	 

if(OBJECT_ID('tempdb..#tmpMultiCli1') IS NOT NULL )
     DROP table #tmpMultiCli1

if(OBJECT_ID('tempdb..#tmpMultiCli2') IS NOT NULL )
     DROP table #tmpMultiCli2

	 if(OBJECT_ID('tempdb..#tmpMultiCli3') IS NOT NULL )
     DROP table #tmpMultiCli3


Declare @DT int = NULL

declare @client varchar(50)= 'TruJet'

SET @DT =(SELECT CONVERT(INT,CONVERT(CHAR(8),GETDATE()-1,112)))

IF not exists(select 1 from  [ReportMultiClient].[dbo].[MultiClient] where CONVERT(INT,CONVERT(CHAR(8),[Dtlogdate]-1,112)) = CONVERT(INT,CONVERT(CHAR(8),GETDATE()-1,112) ))

BEGIN
		select @dt as RepDate,@client as Client,booking=(select isnull(sum(segment_qty),0) 

		from [180.179.226.58,21443].AIRS_U_TJ.DBO.t_inv_transactions with(nolock) where tr_addition=1 and convert(int,convert(char(8),tr_date,112))=@dt),
		cancellation=(select isnull(sum(segment_qty),0) from [180.179.226.58,21443].AIRS_U_TJ.DBO.t_inv_transactions with(nolock) where tr_addition=0 and convert(int,convert(char(8),tr_date,112))=@dt)
		into #tmpMultiCli
		select  RepDate,	Client,	booking,	cancellation,(booking-cancellation) net from #tmpMultiCli

		insert into ReportMultiClient.[dbo].[MultiClient](ReportDate, Client, Booked, Cancel, Netbooking)
		select  RepDate,	Client,	booking,cancellation,	(booking-cancellation)  net FROM #tmpMultiCli





declare @client1 varchar(50)= 'Ventura'

		select @dt as RepDate,@client1 as Client,booking=(select isnull(sum(segment_qty),0) 
		from [193.168.2.30].AIRS_U_VA.DBO.t_inv_transactions with(nolock) where tr_addition=1 and convert(int,convert(char(8),tr_date,112))=@dt),
		cancellation=(select isnull(sum(segment_qty),0) from [193.168.2.30].AIRS_U_VA.DBO.t_inv_transactions with(nolock) where tr_addition=0 and convert(int,convert   (char(8),tr_date,112))=@dt)
		into #tmpMultiCli1
		select  RepDate,	Client,	booking,	cancellation,(booking-cancellation) net from #tmpMultiCli1

		insert into ReportMultiClient.[dbo].[MultiClient](ReportDate, Client, Booked, Cancel, Netbooking)
		select  RepDate,	Client,	booking,cancellation,	(booking-cancellation)  net FROM #tmpMultiCli1



declare @client2 varchar(50)= 'ZoomAir'

		select @dt as RepDate,@client2 as Client,booking=(select isnull(sum(segment_qty),0) 
		from [43.224.136.66,2499].AIRS_U_ZO.DBO.t_inv_transactions with(nolock) where tr_addition=1 and convert(int,convert(char(8),tr_date,112))=@dt),
		cancellation=(select isnull(sum(segment_qty),0) from [43.224.136.66,2499].AIRS_U_ZO.DBO.t_inv_transactions with(nolock) where tr_addition=0 and convert(int,convert   (char(8),tr_date,112))=@dt)
		into #tmpMultiCli2
		select  RepDate,	Client,	booking,	cancellation,(booking-cancellation) net from #tmpMultiCli2

		insert into ReportMultiClient.[dbo].[MultiClient](ReportDate, Client, Booked, Cancel, Netbooking)
		select  RepDate,	Client,	booking,cancellation,	(booking-cancellation)  net FROM #tmpMultiCli2


		declare @client3 varchar(50)= 'Nepal AirWays'

		select @dt as RepDate,@client3 as Client,booking=(select isnull(sum(segment_qty),0) 
		from [96.45.76.82].AIRS_U_RA.DBO.t_inv_transactions_v2 with(nolock) where tr_addition=1 and convert(int,convert(char(8),tr_date,112))=@dt),
		cancellation=(select isnull(sum(segment_qty),0) from [96.45.76.82].AIRS_U_RA.DBO.t_inv_transactions_v2 with(nolock) where tr_addition=0 and convert(int,convert   (char(8),tr_date,112))=@dt)
		into #tmpMultiCli3
		select  RepDate,	Client,	booking,	cancellation,(booking-cancellation) net from #tmpMultiCli3

		insert into ReportMultiClient.[dbo].[MultiClient](ReportDate, Client, Booked, Cancel, Netbooking)
		select  RepDate,	Client,	booking,cancellation,	(booking-cancellation)  net FROM #tmpMultiCli3

END

#2

Can you provide DDL and sample data as well as expected outcome? We don't know what your data looks like, so we'd just be guessing. One thing I do see is this will not use an index: convert(int,convert(char(8),tr_date,112))=@dt