英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论