根据日期将一条记录拆分为多行。游标中的过程发送错误
根据日期将一条记录拆分为多行。游标中的过程发送错误
提问于 2019-06-19 09:50:40
我正在尝试将一行拆分为几行，具体取决于某些日期。我试图在游标内创建一个过程，但失败了。似乎我没有声明两个变量，但是我应该如何解决这个问题呢？
我有一个原始表(名为Table1)，如下所示：
ID DOB Entry_date Termination_date
1 2000-6-1 2010-9-1 2012-7-1
2 2004-12-1 2011-11-20 2013-2-1假设这些是每个学生进入和离开学校的记录，以及生日。
我需要将记录分成几部分，以便计算他们每年、每个年龄和每个年级的在校天数。将此表命名为Table2，这是我使用游标成功生成的：
ID Year Start_date End_date
1 2010 2010-1-1 2010-6-1
1 2010 2010-6-1 2010-9-1
1 2010 2010-9-1 2010-12-31
1 2011 2011-1-1 2011-6-1
1 2011 2011-6-1 2011-9-1
1 2011 2011-9-1 2011-12-31
1 2012 2012-1-1 2012-6-1
1 2012 2011-6-1 2011-7-1
2 2011 2011-1-1 2011-11-20
2 2011 2011-11-20 2011-12-1
2 2011 2011-12-1 2011-12-31
2 2012 2012-1-1 2012-11-20
2 2012 2012-11-20 2012-12-1
2 2012 2012-12-1 2012-12-31
2 2013 2013-1-1 2013-2-1但是，现在也有一个3个月的观察期，从入职之日起，称为Table3。下面是Table3应该是什么样子：
ID Year Start_date End_date
1 2010 2010-1-1 2010-6-1
1 2010 2010-6-1 2010-9-1
1 2010 2010-9-1 2010-12-1
1 2010 2010-12-1 2010-12-31
1 2011 2011-1-1 2011-6-1
1 2011 2011-6-1 2011-9-1
1 2011 2011-9-1 2011-12-31
1 2012 2012-1-1 2012-6-1
1 2012 2011-6-1 2011-7-1
2 2011 2011-1-1 2011-11-20
2 2011 2011-11-20 2011-12-1
2 2011 2011-12-1 2011-12-31
2 2012 2012-1-1 2012-2-20
2 2012 2012-2-20 2012-11-20
2 2012 2012-11-20 2012-12-1
2 2012 2012-12-1 2012-12-31
2 2013 2013-1-1 2013-2-1我正在考虑添加一个过程来复制我所做的事情，但是在if @birthday>=@anniversary之前创建一个过程来判断观察日期是否在table2中的每个间隔内(但我希望直接从Table1生成Table3，而不是生成多余的Table2)。如果没有，就像以前一样插入记录；否则插入两次记录，一次从开始日期开始，到观察日期结束；下一次从观察日期开始，到结束日期结束。
我尝试了下面的代码，但失败了。
create table [dbo].[table3] (ID int, Year int, Start_date date, End_date date)
declare @ID int,
@DOB Date,
@Entry_date date,
@Termination_date date,
@startyr int,
@endyr int,
@birthday date,
@anniversary date,
@date1 date,
@date2 date
Declare cur1 cursor
For
Select ID, DOB, Entry_date, Termination_date
From [dbo].[Table1];
Open cur1;
fetch next from cur1 into @ID, @DOB, @Entry_date, @Termination_date;
while @@fetch_status=0
begin
set @startyr=year(@Entry_date);
set @endyr = year(@Termination_date);
set @obsdt=dateadd(day,90,@Entry_date);
while @startYr<=@EndYr
begin
set @birthday=datefromparts(@startyr,month(DOB),day(DOB));
set @anniversay= datefromparts(@startyr,month(Entry_date),day(Entry_date));
set @date1= datefromparts(@startYr,1,1);
set @date2 = case when datefromparts(@startYr,12,31)>@Termination_date then @Termination_date else datefromparts(@startYr,12,31) end;
create procedure dbo.test(@begindt date, @stopdt date)
as
if (@begindt>@ obsdt) and (@stopdt<@obsdt)
begin
insert into [dbo].[table3] (ID, Year, Start_date, End_date) values (@ID, @startyr, @ begindt, @obsdt);
insert into [dbo].[table3] (ID, Year, Start_date, End_date) values (@ID, @startyr, @ obsdt, @stopdt);
end
else
insert into [dbo].[table3] (ID, Year, Start_date, End_date) values (@ID, @startyr, @ begindt, @ stopdt);
if @birthday>=@anniversary
begin
exec [dbo].[test] @date1, @ anniversary
exec [dbo].[test] @anniversary, @birthday
exec [dbo].[test] @birthday, @date2
end
else
begin
exec [dbo].[test] @date1, @birthday
exec [dbo].[test] @birthday, @anniversary
exec [dbo].[test] @anniversary, @date2
end
set @startYr=@startYr+1
end
fetch next from cur1 into @ID, @DOB, @Entry_date, @Termination_date;
end
close cur1
go错误消息如下：
过程关键字‘
’附近的语法不正确。
必须声明标量变量"@begindt“。
必须声明标量变量“@stopdt”。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-19 12:17:37
编辑-在了解了你实际上想要实现的目标后，我更新了我所写的查询。
下面的查询通过多个CTE，这样做是为了使它更容易理解，并且毫无疑问可以进一步简化。
然后，我将其扩展为每个日期一行，以便我可以简单地对它们进行排序，然后丢弃任何超出注册范围或该年不需要的日期，最后将它们配对成范围。
;With daterange as (
Select year(min(Entry_date)) as y1, year(max(Termination_date)) as y2 from table1
),
-- Expand the rows into 1 per year
years as
(
Select y1 as y from daterange
union all
Select y + 1
from years
where y <= (select y2 from daterange)
)
-- Now work our all the birthdays, anniversaries etc for each source row and each applicable year
, alldates as
(
Select table1.*,
dateadd(day,90,Entry_date) obsdt,
datefromparts(y,month(DOB),day(DOB)) as birthday,
datefromparts(y,month(Entry_date),day(Entry_date)) as anniversary,
datefromparts(y,1,1) date1,
datefromparts(y,12,31) date2,
y
From table1
join years on y between year(Entry_Date) and year(Termination_date)
)
-- There are 7 possible dates - year start, birthday, anniversay, obsdt, year end, entry, termination
, dates as
(
select d from (values(1),(2),(3),(4),(5),(6),(7)) as starts(d)
)
-- Split it into multiple rows so we can sort the dates
, expanded as
(
select ID, y, entry_date, termination_date,
case d
when 1 then date1
when 2 then birthday
when 3 then anniversary
when 4 then obsdt
when 5 then date2
when 6 then entry_date
when 7 then termination_date
end as dt
from alldates
cross join dates
)
-- Exclude rows that are not from the year - entry, termination, obsdt, or are outside the entry/termination dates
, validrows as
(
select distinct ID, y, dt
from expanded
where year(dt)=y
and dt>=entry_date and dt<=termination_date
)
-- Pair each ro with its next row
, pairs as
(
select *, lead(dt,1) over(partition by ID,Y order by dt) as dt2
from validrows
)
-- The final insert
insert into [dbo].[table3] (ID, Year, Start_date, End_date)
select ID, y, dt, dt2
from pairs where dt2 is not null
order by ID, y, dt这样做的结果是：
ID y dt dt2
1 2010 2010-09-01 2010-11-30
1 2010 2010-11-30 2010-12-31
1 2011 2011-01-01 2011-06-01
1 2011 2011-06-01 2011-09-01
1 2011 2011-09-01 2011-12-31
1 2012 2012-01-01 2012-06-01
1 2012 2012-06-01 2012-07-01
2 2011 2011-11-20 2011-12-01
2 2011 2011-12-01 2011-12-31
2 2012 2012-01-01 2012-02-18
2 2012 2012-02-18 2012-11-20
2 2012 2012-11-20 2012-12-01
2 2012 2012-12-01 2012-12-31
2 2013 2013-01-01 2013-02-01页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56659048
复制相关文章
相似问题