More efficient way of creating extra rows based on data range-CodePudding

I have some product information data where I need to create extra rows to show when a product is frozen. The structure of the data is as follows:

CREATE TABLE #Products(
    [ProductId] [int] NULL,
    [Product] [nvarchar](255) NULL,
    [Startdate] [date] NULL,
    [Enddate] [date] NULL,
    [Startdate_blocked] [date] NULL,
    [Enddate_blocked] [date] NULL
) 

INSERT INTO #Products(
[ProductId] ,
[Product] ,
[Startdate] ,
[Enddate] ,
[Startdate_blocked] ,
[Enddate_blocked]
)


VALUES('33',    'PRODUCTNUMBER33',  '2010-01-01',   NULL,   '2018-10-01',   NULL)
     ,('36',    'PRODUCTNUMBER36',  '2010-01-01',   NULL,   '2018-11-01',   NULL)
     ,('58',    'PRODUCTNUMBER58',  '2010-01-01',   NULL,   '2018-10-01',   '2020-10-30')
     ,('75',    'PRODUCTNUMBER75',  '2010-01-01',   NULL,   '2020-01-01',   '2020-07-07')
     ,('80',    'PRODUCTNUMBER80',  '2010-01-01',   '2015-08-31',   NULL,   NULL)

The startdate and enddate represent the validity of an overall product. The the startdate_blocked and enddate_blocked represent the range when a product is frozen. If and enddate is null the the valid_till should be 2999-12-31. i.e till infinity. If a row has a startdate_blocked filled then a new record should be created with the valid_from and valid_till filled with startdate_blocked and enddate_blocked respectively and a status of 'frozen' There should also be a record with a status 'active' and valid_from = tpo the startdate and a valid_till = to the enddate_blocked.

I have been able to get the output required with the following query:

SELECT [ProductId]  
,[Startdate_blocked]  as valid_from
,isnull(cast([Enddate_blocked] as date),'2999-12-31') as  valid_till
,'frozen' as [status]
,[Startdate]
,isnull([Enddate],'2999-12-31') as Enddate
FROM #Products
WHERE Startdate_blocked IS NOT NULL 

UNION

SELECT [ProductId]  
,[Startdate] as valid_from
,[Startdate_blocked] as  valid_till
,'Active' as [status]
,[Startdate]
,isnull([Enddate],'2999-12-31') as Enddate
FROM #Products
WHERE Startdate_blocked IS NOT NULL 

UNION

SELECT [ProductId]  
,[Startdate] as valid_from
,[Enddate] as  valid_till
,'Active' as [status]
,[Startdate]
,isnull([Enddate],'2999-12-31') as Enddate
FROM #Products
WHERE Startdate_blocked IS NULL

Whilst this code produces the output I feel I repeat a lot of the code with the unions. Therefore I am looking for another way to produce the output if only for aesthetic reasons.

The expected output is :

CodePudding user response：

Seems you could use a VALUES table construct and JOIN to that:

SELECT [ProductId],
       [Startdate] AS valid_from,
       [Enddate] AS valid_till,
       V.[status],
       [Startdate],
       ISNULL([Enddate], '2999-12-31') AS Enddate
FROM #Products P
     JOIN (VALUES ('frozen'),
                  ('active'))V (status) ON (P.Startdate_blocked IS NULL AND V.status = 'active')
                                        OR P.Startdate_blocked IS NOT NULL;

CodePudding user response：

@Larnu I had another look and had to add case statements to get the desired output. Thanks for putting me in the right direction

CASE WHEN  V.[status] = 'frozen' then Startdate_blocked ELSE Startdate END AS valid_from,
       CASE WHEN  V.[status] = 'active' and  Startdate_blocked IS NULL THEN ISNULL([Enddate], '2999-12-31')
            WHEN  V.[status] = 'active' and  Startdate_blocked IS NOT NULL THEN [Startdate_blocked]
            ELSE ISNULL([Enddate_blocked], '2999-12-31')
            END AS valid_till,