SQL Server 基于数据区域创建额外行的更有效方法

bprjcwpo 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(128)

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 :

sql-server

来源：https://stackoverflow.com/questions/74727951/more-efficient-way-of-creating-extra-rows-based-on-data-range

2条答案

按热度按时间

bqf10yzr1#

看起来可以使用VALUES表构造和JOIN来实现：

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;

赞(0）回复(0）举报 2022-12-10

piztneat2#

@Larnu我又看了一遍，不得不添加case语句才能得到所需的输出。谢谢你把我带到了正确的方向

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,

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 基于数据区域创建额外行的更有效方法

2条答案

相关问题

热门标签

最新问答