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
)
go
declare @BaslangicTarihi datetime = '2022-01-23'
declare @BitisTarihi datetime = '2023-05-10'
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
)