telegramdb

-- test için oluşturulan tablolar
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'

-- case deyimi için oluşturulmuş değişkenler
declare @is_valid bit = 1
declare @is_valid_true bit = 1
declare @is_valid_false bit = 0

-- fake datalardan oluşan 1000 elemanın hepsini dönmek için oluşturulmuş döngü değişkenleri
declare @i int = 1
declare @n int = 1000

while @i <= @n 
begin	
    
    -- Kullanılan sorgu
    	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 = 0 and 
    				sc.deleted = 0 and
    				fc.firm_id = @i 
    			)
    -------------------------------------------------------------------------------------------------			
    
    -- döngü değeri 1 arttırıldı
    set @i = @i + 1
    
end -- end while