query to return data with computed status field based on date range search-CodePudding

I need to create a query that returns information on products linked to a customer based on a specific range of dates used. I have provided some data to explain the scenario.

declare @Startperiod datetime= '2019-03-02'
,@Endperiod datetime = '2019-10-10'

create table #customer
(
[customer_bk] [varchar](100) NULL
,[product_bk] [varchar](100) NULL

)

insert into  #customer
(customer_bk
,product_bk
)

values ('cust_1','2-76')



create table #product
(

    [product_bk] [varchar](100) NULL,
    [valid_from] [date] NULL,
    [valid_till] [date] NULL,
    [product_type] [nvarchar](30) NULL,
    [product_number] [nvarchar](34) NULL,

    [status] [nvarchar](20) NULL,
    [start_date] [date] NULL,
    [end_date] [date] NULL



)

insert into #product
           ([product_bk]
           ,[valid_from]
           ,[valid_till]
           ,[product_type]
           ,[product_number]
           ,[status]
           ,[start_date]
           ,[end_date])




values ('2-76', '2018-11-01',   '2999-12-31',   'Hypothecair krediet',  'NL00NIBC01176' ,   'Frozen'    ,'2010-01-01',  '2999-12-31'),
       ('2-76', '2010-01-01',   '2018-11-01',   'Hypothecair krediet',  'NL00NIBC01176' ,   'Active',   '2010-01-01',   '2999-12-31')

select * from #customer
select * from #product

The @Startperiod and @Endperiod variables are used to query the data. The requirements for returning data are:

If @Startperiod and @Endperiod both fall before or after the end date fields then the returned status should be 'No Data Found'
If @Startperiod is before the end date field and the @Endperiod is after the end_date field then a status of 'discontinued' should be returned
If @Startperiod and @Endperiod both fall within the range of the start_date and end_date fields then we check if it falls within the range of the valid_from and valid_till fields. It should return the status for where the date range is valid

In the case of the example data I have

@Startperiod ='2019-03-02' ,@Endperiod = '2019-10-10'

should return the following output:

customer_bk | product_bk | product_number| status|

@Startperiod = '2019-03-02' @Endperiod = '2021-10-10'

should return the following:

customer_bk | product_bk | product_number| status |

@HABO in relation to your comment I wanted to add the status to the 2 rows

Thanks in advance

CodePudding user response：

Here's the SELECT statement I came up with to satisfy your requirements. However, I don't think your logic above makes sense, specifically for the "discontinued" products. Maybe you can take this and tinker with the logic to get what you want. If you can validate your logic, then I'll take another crack at it:

select
    c.customer_bk
    , p.product_bk
    , p.product_number
    , CASE 
        WHEN @Startperiod > p.[end_date] OR @Endperiod < p.[start_date] THEN 'No Data Found'
        WHEN @Startperiod < p.[end_date] AND @Endperiod > p.[end_date] THEN 'Discontinued'
        WHEN @Startperiod >= p.[start_date] AND @Endperiod <= p.end_date THEN  --Valid during variable start/end dates check.
            CASE 
                WHEN @Startperiod >= p.valid_from AND @Endperiod <= p.valid_till THEN p.[status]  --If variables within validity period, just grab status from product.
                ELSE 'Not Valid'  --Product start/end dates are valid, but we are outside the product validity period.
            END
        ELSE 'Unhandled/Unknown'  --If we get here, we have an unhandled case.
      END as [status]

      , p.*  --Using for debugging. Comment or remove in production.
from #customer as c
    INNER JOIN #product as p
        ON p.product_bk = c.product_bk
;