How to return the right status based on date condition

Question

Hello,

I have 2 tables (Fact_Manufacturing and Dim_ProductStatusHistory) as bellow the tables with example of data

create table #Fact_Manufacturing(
   ID_SK int,
   IdProd int,
   DateManu datetime,
   DateLIV datetime
   )
 insert into #Fact_Manufacturing
 select 1,7100,'2016-01-01','2016-06-07'
 union
 select 2,7100,'2016-06-07','2019-12-31'
 union
 select 3,NULL,'2016-01-01','2016-03-08'
 union
 select 4,7200,'2016-03-08','2020-12-31'

create table #Dim_ProductStatusHistory(
    idsk int,
    id_product int,
    Begin_valid datetime,
    End_valid datetime,
    Status varchar(15)
)

 insert into #Dim_ProductStatusHistory
 select 1,7100,'2014-01-01','2016-01-01','Non Valid'
 union
 select 2,7100,'2016-01-01','2020-12-31','Valid'
 union
 select 3,7200,'2016-01-01','2016-05-15','Non Valid'
 union
 select 4,7200,'2016-05-15','2021-12-31','Valid'

I want to return the status of each product based on date of fact and dim table

If the DateManu >= Begin_valid and DateLIV <End_valid the return the status but we have an exception with Id_Prod = 7200 i need to return the range of date with each Status

As bellow the result that i want to get :

IdProd  DateManu    DateLIV Status
7100    2016-01-01 00:00:00.000 2016-06-07 00:00:00.000 Valid
7100    2016-06-07 00:00:00.000 2019-12-31 00:00:00.000 Valid
NULL    2016-01-01 00:00:00.000 2016-03-08 00:00:00.000 NULL
7200    2016-03-08 00:00:00.000        2016-05-15 00:00:00.000         Non Valid
7200    2016-05-15 00:00:00.000        2020-12-31 00:00:00.000         Valid

I tried the query as bellow :

select IdProd,DateManu,DateLIV,Status from #Fact_Manufacturing
 left join #Dim_ProductStatusHistory
 on id_product=IdProd
 where DateManu >= Begin_valid  and DateLIV <End_valid  
 union 
 select IdProd,DateManu,DateLIV,Status from #Fact_Manufacturing
left join #Dim_ProductStatusHistory
 on id_product=IdProd
 where IdProd is null
 union 
 select  IdProd, ISNULL(lead(Begin_valid) over(partition by idProd order by  Begin_valid),DateManu) as DateManu,isnull(lag(End_valid) over(partition by idProd order by  End_valid),DateLIV) as DateLIV,Status
 from #Fact_Manufacturing
left join #Dim_ProductStatusHistory
 on id_product=IdProd
 where IdProd=7200

The query works correctly but i want to do something dynamic without filter in IdProd in the last query

How can i do that ?

If you have any suggestion to improve that ?
Thanks

Answer 1

Good day and welcome to the QnA forum,

The query works correctly but I want to do something dynamic without filter in IdProd in the last query

What do you want to get in this query? Why did you selected specific IdProd in the first place?

Your requirement is not clear

we have an exception with Id_Prod = 7200

Yu explicitly say that you have exception which this specific Id_Prod. So , what do you need to do dynamic here?

Please try to clarify and in the mean time, you do not need to use the first two queries and read the table twice. The first two queries can be merged into simple one, using the condition: where (DateManu >= Begin_valid and DateLIV <End_valid) or (IdProd is null)

select IdProd,DateManu,DateLIV,Status
from Fact_Manufacturing
left join Dim_ProductStatusHistory on id_product=IdProd
where (DateManu >= Begin_valid  and DateLIV <End_valid) or (IdProd is null)

union 

select  
    IdProd, ISNULL(lead(Begin_valid) over(partition by idProd order by  Begin_valid),DateManu) as DateManu,
    isnull(lag(End_valid) over(partition by idProd order by  End_valid),DateLIV) as DateLIV,Status
from Fact_Manufacturing
left join Dim_ProductStatusHistory on id_product=IdProd
where IdProd=7200

Answer 2

This may be what you want:

;with cte  
as(  select  IdProd, ISNULL(lead(Begin_valid) over(partition by idProd order by  Begin_valid),DateManu) as DateManu  
  ,isnull(lag(End_valid) over(partition by idProd order by  End_valid),DateLIV) as DateLIV,Status  
  from #Fact_Manufacturing  
 left join #Dim_ProductStatusHistory  
  on id_product=IdProd)  
  
 select IdProd,DateManu,DateLIV,Status   
 from #Fact_Manufacturing  
  left join #Dim_ProductStatusHistory  
  on id_product=IdProd  
  where (DateManu >= Begin_valid and DateLIV <End_valid)  or IdProd is null  
  union   
  select * from cte  
  where IdProd=7200  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Note sure that I understand this correctly, but there are a bunch of products which you need to track in the same way as product 7200, you should somewhere have table that tracks these products. Or add a column indicating this to your products table. Then you would use this information to filter in the query.