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