telegramdb

create table firm_codes
(
	firm_id int primary key identity(1, 1),
	food_registry_start_date datetime not null,
	food_registry_end_date datetime null,
	deleted bit not null
)

go 

create table supply_contracts
(
	supply_id int primary key identity(1, 1),
	firm_id int references firm_codes(firm_id),
	deleted bit not null
)

declare @BaslangicTarihi datetime = '2022-01-16'
declare @BitisTarihi datetime = '2022-08-15'
declare @FirmaId int = 835
declare @is_valid bit = 1
declare @is_valid_true bit = 1
declare @is_valid_false bit = 0

select
	fc.firm_id,
	fc.food_registry_start_date as Baslangic,
	fc.food_registry_end_date as Bitis,
	fc.deleted as fc_deleted,
	sc.deleted as sc_deleted
from dbo.firm_codes as fc with(nolock)
inner join dbo.supply_contracts sc with(nolock) 
on fc.firm_id = sc.firm_id
where	
		@is_valid = 
		( 
			case 
			when 
				fc.food_registry_end_date is null and 
				DATEDIFF(day, @BaslangicTarihi, fc.food_registry_start_date) > 0
			then @is_valid_true
			when 
				fc.food_registry_end_date is not null and 
				DATEDIFF(day, @BaslangicTarihi, fc.food_registry_start_date) > 0 and  
				DATEDIFF(day, @BitisTarihi, fc.food_registry_end_date) >= 0
			then @is_valid_true
			else @is_valid_false
			end
		)
		and 
		(
			fc.deleted = 1 and 
			sc.deleted = 1 and
			fc.firm_id = @FirmaId 
		)