gistfile1.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Tombstones_SearchTombstones]
	@industry_id smallint = null,
	@region_id_list varchar(8000) = null,
	@inbound_region_id_list varchar(8000) = null,
	@outbound_region_id_list varchar(8000) = null
AS
BEGIN

declare @region_list_count int
declare @inbound_region_list_count int
declare @outbound_region_list_count int
if @region_id_list is not null
	select @region_list_count = count(*) from iter_charlist_to_table(@region_id_list,',')
if @inbound_region_id_list is not null
	select @inbound_region_list_count = count(*) from iter_charlist_to_table(@inbound_region_id_list,',')
if @outbound_region_id_list is not null
	select @outbound_region_list_count = count(*) from iter_charlist_to_table(@outbound_region_id_list,',')

select t.id, matter_number, value, can_be_publicly_disclosed, has_image, client.name as client_name, industry.name as client_industry, max(announced.status_date) as announce_date, max(closed.status_date) as closed_date
from tombstones as t
left join parties as client
on client.id = (select top 1 party_id from tombstones_parties where tombstone_id = t.id and is_client = 1)
left join industries as industry
on industry.id = (select top 1 industry_id from tombstones_parties_industries where tombstone_id = t.id and party_id = client.id)
left join tombstones_deal_statuses as announced
on t.id = announced.tombstone_id and announced.deal_status_id = 1
left join tombstones_deal_statuses as closed
on t.id = closed.tombstone_id and closed.deal_status_id = 2

-- filters
left join tombstones_parties_industries as tpi
on t.id = tpi.tombstone_id
left join tombstones_parties as tp
	inner join iter_charlist_to_table(@region_id_list,',') as region_selections
	on tp.region_id = region_selections.str
on t.id = tp.tombstone_id
left join tombstones_parties as tp_inbound
	inner join tombstones_parties_roles as tpr_inbound
	on tp_inbound.tombstone_id = tpr_inbound.tombstone_id and tp_inbound.party_id = tpr_inbound.party_id and tpr_inbound.party_role_id in (10,11,12) -- sellers
	inner join iter_charlist_to_table(@inbound_region_id_list,',') as inbound_selections
	on tp_inbound.region_id = inbound_selections.str
on t.id = tp_inbound.tombstone_id
left join tombstones_parties as tp_outbound
	inner join tombstones_parties_roles as tpr_outbound
	on tp_outbound.tombstone_id = tpr_outbound.tombstone_id and tp_outbound.party_id = tpr_outbound.party_id and tpr_outbound.party_role_id = 1 -- acquirer
	inner join iter_charlist_to_table(@outbound_region_id_list,',') as outbound_selections
	on tp_outbound.region_id = outbound_selections.str
on t.id = tp_outbound.tombstone_id

-- conditions
where ((@industry_id is null) or (tpi.industry_id = @industry_id))

group by t.id, matter_number, value, can_be_publicly_disclosed, has_image, client.name, industry.name

having ((@inbound_region_id_list is null) or (count(distinct tp_inbound.region_id) = @inbound_region_list_count))
and ((@region_id_list is null) or (count(distinct tp.region_id) = @region_list_count))
and ((@outbound_region_id_list is null) or (count(distinct tp_outbound.region_id) = @outbound_region_list_count))

END