SQL Server Need split the data when condition is getting changed without loop

jm2pwxwz 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(105)

Below is my table

if exists(select top 1 1 from sys.tables where name='ObjInfo')
drop table ObjInfo

  create table ObjInfo(id int identity
                     ,ObjNumber int
,ObjDate datetime
,ObjConditionId int)
 
  insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId)
  values(1,'2014-01-03',1)
  ,(1,'2014-01-05',1)
  ,(1,'2014-01-06',1)
  ,(1,'2014-01-08',2)
  ,(1,'2014-01-13',1)
  ,(1,'2014-01-15',1)
  ,(1,'2014-01-25',4)
  ,(2,'2014-01-01',1)
  ,(2,'2014-01-05',1)
  ,(2,'2014-01-07',2)
  ,(2,'2014-01-08',2)
  ,(2,'2014-01-12',2)
  ,(2,'2014-01-14',3)
  ,(2,'2014-01-15',4)

My Job is to display ObjectNumbers wise when condition was changed and for which time period. My expected output is as below

ObjNumber ObjConditionId ConditionBeg ConditionEnd
1           1           2014-01-03      2014-01-08
1           2           2014-01-08      2014-01-13
1           1           2014-01-13      2014-01-25
1           4           2014-01-25      getdate()
2           1           2014-01-01      2014-01-07
2           2           2014-01-07      2014-01-14
2           3           2014-01-14      2014-01-15
2           4           2014-01-15      getdate()

I am trying below code but not getting how can I achieve this.

WITH ConditionCTE AS (
  SELECT ObjNumber, ObjConditionId, ObjDate AS ConditionBeg,
         LEAD(ObjDate, 1, GETDATE()) OVER (PARTITION BY ObjNumber ORDER BY ObjDate) AS ConditionEnd
  FROM ObjInfo
)
SELECT ObjNumber, ObjConditionId, ConditionBeg,
       CASE WHEN CAST(ConditionEnd as date)= GETDATE() THEN 'getdate()' ELSE ConditionEnd END AS ConditionEnd
FROM ConditionCTE
ORDER BY ObjNumber, ConditionBeg;

sql-server

来源：https://stackoverflow.com/questions/76412556/need-split-the-data-when-condition-is-getting-changed-without-loop

1条答案

按热度按时间

6fe3ivhb1#

This is a classic gaps-and-island problem; you can use the difference between row numbers to identify groups.

This gives you for each island, with its start and end dates:

select ObjNumber, ObjConditionId, 
    min(ObjDate) MinObjDate, 
    max(ObjDate) MaxObjDate
from (
    select o.*,
        row_number() over(partition by ObjNumber                 order by ObjDate) rn1,
        row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
    from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate

If you want to the start of the next island as MaxObjDate instead, as shown in your expected results, we can use lead() on top of this (the three-arguments form allows us to provide a default value):

select ObjNumber, ObjConditionId, 
    min(ObjDate) MinObjDate, 
    lead(min(ObjDate), 1, getdate()) over(partition by ObjNumber order by min(ObjDate)) MaxObjDate
from (
    select o.*,
        row_number() over(partition by ObjNumber                 order by ObjDate) rn1,
        row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
    from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate

fiddle

ObjNumber	ObjConditionId	MinObjDate	MaxObjDate
1	1	2014-01-03 00:00:00.000	2014-01-08 00:00:00.000
1	2	2014-01-08 00:00:00.000	2014-01-13 00:00:00.000
1	1	2014-01-13 00:00:00.000	2014-01-25 00:00:00.000
1	4	2014-01-25 00:00:00.000	2023-06-06 08:46:17.283
2	1	2014-01-01 00:00:00.000	2014-01-07 00:00:00.000
2	2	2014-01-07 00:00:00.000	2014-01-14 00:00:00.000
2	3	2014-01-14 00:00:00.000	2014-01-15 00:00:00.000
2	4	2014-01-15 00:00:00.000	2023-06-06 08:46:17.283

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server Need split the data when condition is getting changed without loop

1条答案

相关问题

热门标签

最新问答