在特定区间内如何找到最大和最小日期?

huangapple go评论84阅读模式
英文:

How to find Max and Min dates in a specific interval?

问题

以下是您提供的代码的翻译部分:

DROP TABLE IF EXISTS #ExampelTable
CREATE TABLE #ExampelTable (Id INT IDENTITY(1,1), PersonId INT, FromDate DATE, ToDate DATE)

INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2009-01-01','2009-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-01-26','2010-01-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-02-01','2010-06-20')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-06-21','2011-02-17')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2011-02-18','2011-07-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2013-12-03','2014-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2015-11-03','2016-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (20, '2020-11-03','2021-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-03','2000-11-25')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-26','2000-11-30')

/* 创建带有下一行FromDate和该行PersonId值的CTE */

;WITH NextDates AS
(
    SELECT  id
        ,   PersonId
        ,   FromDate
        ,   ToDate
        ,   LEAD(FromDate) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextDate
        ,   LEAD(PersonId) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextPersonId
    FROM #ExampelTable
),

/* 创建标记每个间隔末尾的行的标志 */
RowNumbers AS
(
    SELECT  id
        ,   1 AS IntervalEnd
        ,   ROW_NUMBER() OVER ( PARTITION BY PersonId ORDER BY FromDate ) AS RowNr
    FROM    NextDates
    WHERE   PersonId = NextPersonId
        AND NextDate <> DATEADD(dd,1,ToDate)
        OR  NextDate IS NULL
)

SELECT  NextDates.Id
    ,   NextDates.PersonId
    ,   NextDates.FromDate
    ,   NextDates.ToDate
    ,   NextDates.NextDate
    ,   RowNumbers.IntervalEnd
    ,   RowNumbers.RowNr
    ,   NULL AS MinDate
    ,   NULL AS MaxDate
FROM    NextDates
    LEFT JOIN RowNumbers ON NextDates.id = RowNumbers.Id

希望这有助于理解代码的翻译。

英文:

I'm having trouble finding the first and last date in a certain interval. If you look at the picture below you can see 3 different PersonIds. An interval is when the same Personid has a FromDate and ToDate that is coherent. Fromdate is 1 day after ToDate in the row above, if these 2 rows have the same PersonId. Row 1 is one Interval, and row 2-5 is one interval. What I'm trying to do is add 2 extra columns which represent the first date in an interval and the last date.

在特定区间内如何找到最大和最小日期?

The code below is my attempt.
First I have created a table with data. Then I'm creating a CTE, "NextDates", with extra columns with the value in FromDate on the row below, and one column with that row's PersonId. Like this:
在特定区间内如何找到最大和最小日期?

Then I'm creating another CTE. "RowNumbers". Here I'm trying to mark every end of an interval, with either '1' or an increasing integer. This step might be totaly unnecessarily? If I can get the same "RowNr" in every row in that interval, then I should be able to group on that and find Max and Min date.

DROP TABLE IF EXISTS #ExampelTable
CREATE TABLE #ExampelTable (Id INT IDENTITY(1,1), PersonId INT, FromDate DATE, ToDate DATE)
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2009-01-01','2009-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-01-26','2010-01-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-02-01','2010-06-20')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2010-06-21','2011-02-17')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2011-02-18','2011-07-31')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2013-12-03','2014-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (10, '2015-11-03','2016-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (20, '2020-11-03','2021-06-30')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-03','2000-11-25')
INSERT INTO #ExampelTable (PersonId,FromDate,ToDate) VALUES (30, '2000-11-26','2000-11-30')
/*Creating CTE with FromDate in next row, together with that rows PersonId*/
;WITH NextDates AS
(
SELECT	id
,	PersonId
,	FromDate
,	ToDate
,	LEAD(FromDate) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextDate
,	LEAD(PersonId) OVER (PARTITION BY PersonId ORDER BY PersonId, FromDate) AS NextPersonId
FROM #ExampelTable
),
/*Creating flags on the rows where one interval end */
RowNumbers AS
(
SELECT	id
,	1 AS IntervalEnd
,	ROW_NUMBER() OVER ( PARTITION BY PersonId ORDER BY FromDate ) AS RowNr
FROM	NextDates
WHERE	PersonId = NextPersonId
AND	NextDate <> DATEADD(dd,1,ToDate)
OR	NextDate IS NULL
)
SELECT	NextDates.Id
,	NextDates.PersonId
,	NextDates.FromDate
,	NextDates.ToDate
,	NextDates.NextDate
,	RowNumbers.IntervalEnd
,	RowNumbers.RowNr
,	NULL AS MinDate
,	NULL AS MaxDate
FROM	NextDates
LEFT JOIN RowNumbers ON NextDates.id = RowNumbers.Id

This is how it looks with the extra columns I'm created. The red lines show the end of every interval. And the blue ones show which value I want to have in the "MinDate" and "MaxDate" columns.
Row 1 is simple, it's 1 interval, end then MinDate = FromDate and MaxDate = ToDate.
Rows 2-5 I need FromDate from row 2 in every row in that interval (Row 2-5) in the MinDate column. And ToDate on row 5 in every MaxDate row in that column.

在特定区间内如何找到最大和最小日期?

答案1

得分: 4

我不使用SQL Server,但在这种情况下,语法非常接近标准,此查询在SQL Server中有效[dbfiddle](https://dbfiddle.uk/cmx71fTP):

    with grps as (
      select id, personid, fromdate, todate, 
              sum(grp) over (partition by personid order by fromdate) grp
      from (
        select et.*, 
               case when lag(todate) over (partition by personid order by fromdate) 
                       = dateadd(dd, -1, fromdate) 
                    then 0 else 1 end grp
        from #ExampelTable et) a )
    select id, personid, fromdate, todate, 
           min(fromdate) over (partition by personid, grp) d1, 
           max(todate) over (partition by personid, grp) d2 
      from grps
英文:

I do not use SQL Server, but in this case syntax is very close to standard and this query works in SQL Server dbfiddle:

with grps as (
select id, personid, fromdate, todate, 
sum(grp) over (partition by personid order by fromdate) grp
from (
select et.*, 
case when lag(todate) over (partition by personid order by fromdate) 
= dateadd(dd, -1, fromdate) 
then 0 else 1 end grp
from #ExampelTable et) a )
select id, personid, fromdate, todate, 
min(fromdate) over (partition by personid, grp) d1, 
max(todate) over (partition by personid, grp) d2 
from grps

huangapple
  • 本文由 发表于 2023年4月6日 22:21:32
  • 转载请务必保留本文链接:https://go.coder-hub.com/75950621.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定