I have following tables

Table 1 i have rates

Table 2 i have Dates

Table 3 i have data which show price of rates which are applicable between start and end

i want to create table 4 with SQL query in which i have every

rate (from table 1) every date (from table 2) if that date exist between start and end price other wise 0 display as table 4 below

i dont know how its achievable in SQL query as i am new in SQL and how to link if there are no relation between table

Table 1

Table 2

Table 3

Table 4

CodePudding user response：

Nice job sharing data and desired output. It would be easier if next time you make your data consumable. Something like this.

create table Table1
(
    Rate varchar(10)
    , ID int
)

insert Table1
select 'ConfD1', 46 union all
select 'ConfD2', 47

set dateformat dmy

create table Table2
(
    Dates date
)

insert Table2
select '15-09-2018' union all
select '16-09-2018' union all
select '17-09-2021' union all
select '18-09-2021' union all
select '19-02-2022'

create table Table3
(
    Rate varchar(10)
    , ID int
    , startdate date
    , enddate date
    , price int
)

insert Table3
select 'ConfD1', 46, '01-01-2021', '31-10-2021', 111 union all
select 'ConfD1', 46, '01-11-2021', '01-03-2022', 222 union all
select 'ConfD2', 46, '01-01-2021', '31-10-2021', 333 union all
select 'ConfD2', 46, '01-11-2021', '01-03-2022', 444 union all
select 'ConfD3', 46, '01-01-2021', '31-10-2021', 555 union all
select 'ConfD3', 46, '01-11-2021', '01-03-2022', 666

This query will return the data you are looking for.

select t1.Rate
    , t2.Dates
    , price = coalesce(t3.price, 0)
from Table1 t1
cross join Table2 t2
left join Table3 t3 on t3.startdate <= t2.Dates 
                    and t3.enddate >= t2.Dates 
                    and t3.rate = t1.rate

CodePudding user response：

with dates ( coddate ) as 
(  select convert(datetime , '15/09/2018' , 103) union all
   select convert(datetime , '16/09/2018' , 103) union all
   select convert(datetime , '17/09/2021' , 103) union all
   select convert(datetime , '18/09/2021' , 103) union all
   select convert(datetime , '19/02/2021' , 103) 
) ,
rates  ( rate, id ) as
( 
select 'ConfD1' , 46 union all 
select 'ConfD2' , 47  
), 
rateprices ( Rate , Id, Startdate , EndDate, price ) 
as
(  select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 111 union all
   select 'ConfD1' , 46 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 222 union all
   select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 333 union all
   select 'ConfD2' , 47 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 444 union all
   select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '31/10/2021' , 103 ), 555 union all 
   select 'ConfD3' , 48 , convert(datetime , '01/01/2021' , 103) , convert(datetime , '01/03/2022' , 103 ), 666 
), 
rates_and_dates ( rate , id , coddate ) 
as 
( select a.rate , a.id , b.coddate from rates a cross join dates b
), 
result ( rate, id, coddate, price )
as
(
select a.rate , a.id , a.coddate , price = coalesce(b.price, 0)
from rates_and_dates a inner join rates c 
  on a.rate=c.rate  
left join rateprices b 
  on a.rate=b.rate and b.startdate <= a.coddate and b.enddate >= a.coddate 
) 
select rate, coddate, price  from result 
order by 2, 1

db<>fiddle