Advance Sql Query
ALTER procedure [dbo].[usp_generateCitySampleMappingslist]
as
begin
drop TABLE [dbo].[tab_CitySampleMappings_new]
end
begin
CREATE TABLE [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new](
mapid int IDENTITY(1,1),
id int,
mappedto int,
cityid int,
MAPCHECKSTATUS varchar(50),
checkbyuser varchar(50),
MAPCHECKTIME varchar(200)
)
end
begin
insert into tab_CitySampleMappings_new(id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME)
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings_20130612
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
union
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings_20130627
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
union
select id, mappedto, cityid, MAPCHECKSTATUS, checkbyuser, MAPCHECKTIME from IbeHotels.dbo.tab_CitySampleMappings
where (MAPCHECKSTATUS in ('match') and checkbyuser in ('manish@amantravelgroup.com','swadha@amantravelgroup.com','ravi.sinha@amantravelgroup.com'))
or id=mappedto
end
--update mappedto
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='hotelbeds') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='hpro') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='gta') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
begin
update [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new]
set mappedto=y.id
--select *
from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] X
inner join
(select a.* from [IbeCitySampleMappings].[dbo].[tab_CitySampleMappings_new] A
inner join IbeHotels.dbo.tab_hotels B on a.id=b.id
where b.source='expedia') Y on x.mappedto=y.mappedto
--order by y.mappedto
end
Post a Comment