Line 1 - name = 001
start = 1/1/2011
end = 8/1/2011
Line 2 - name = 002
start = 1/1/2010
end = 12/31/2010
Line 3 - name = 003
start = 1/1/2008
end = 10/1/2009
Line 3结束时间和 Line 2的开始时间之间的间隔大于63天,因此Line 3这行数据被丢弃了.Line 2的结束时间和Line 1的开始时间小于63天,因此start的时间是1/1/2010(从Line 2中取得),end的时间是8/1/2011(从Line 1中取得)。
简单的说,2个line 相邻的开始时间和结束时间互相比较,时间大的Line的开始时间和小得Line的结束时间比较,看是否有63天的间隔,是的话,就取时间大的Line的结束时间和小的line的开始时间,然后组合成为一个新的时间。如果不存在,就扔掉小的Line,把大一点的Line继续和比它更大的Line对比,(Line直接的时间段没有重合的。比如Line1的start时间和Line2的end时间没有重合。) 最后,比较完所有的Line,(Line的个数未知)。获得最后组合成的start和end的时间。
问题:
这段MSSql怎么写?
用loop?
设置2个变量?
start = 1/1/2011
end = 8/1/2011
Line 2 - name = 002
start = 1/1/2010
end = 12/31/2010
Line 3 - name = 003
start = 1/1/2008
end = 10/1/2009
Line 3结束时间和 Line 2的开始时间之间的间隔大于63天,因此Line 3这行数据被丢弃了.Line 2的结束时间和Line 1的开始时间小于63天,因此start的时间是1/1/2010(从Line 2中取得),end的时间是8/1/2011(从Line 1中取得)。
简单的说,2个line 相邻的开始时间和结束时间互相比较,时间大的Line的开始时间和小得Line的结束时间比较,看是否有63天的间隔,是的话,就取时间大的Line的结束时间和小的line的开始时间,然后组合成为一个新的时间。如果不存在,就扔掉小的Line,把大一点的Line继续和比它更大的Line对比,(Line直接的时间段没有重合的。比如Line1的start时间和Line2的end时间没有重合。) 最后,比较完所有的Line,(Line的个数未知)。获得最后组合成的start和end的时间。
问题:
这段MSSql怎么写?
用loop?
设置2个变量?
select
case when abs(a.starttime,b.endtime)>=63 then a.endtime else ...
from
(select id=row_number()over(order by getdate()),* from tb)a,
(select idrow_number()over(order by getdate()),* from tb)b
where
a.id=b.id-1
DECLARE SetdateCur1 Cursor FOR
SELECT DISTINCT top 200 ek.memid,ek.eligibleorgid
FROM plandata_dev.dbo.enrollkeys AS ek
where ek.orgpolicyid IS NOT NULL
AND( ek.memid IS NOT NULL or ek.memid <>'')
AND ek.eligibleorgid LIKE 'ELG% '
ORDER BY ek.memID ASC
Open Setdatecur1
FETCH NEXT FROM Setdatecur1 into @MemID,@EligibleOrgID
While @@Fetch_Status=0
BEGIN
DECLARE SetdateCur Cursor Scroll FOR
SELECT en.EnrollKeysEffDate,en.EnrollKeysTermDate
FROM custom_dev.dbo.Vw_Dbc_Enrollment AS en
WHERE en.EligibleOrgID = @EligibleOrgID
AND en.MemID=@MemID
ORDER BY en.EnrollKeysEffDate ASC
Open Setdatecur
DECLARE @ved DATE,@vtd DATE,@ed1 DATE,@td1 DATE,@ed2 DATE,@td2 DATE,@tempEd DATE,@tempTd DATE
DECLARE @i int,@f int
set @i=0
set @f=0
Fetch SetdateCur into @ved,@vtd
While (@@Fetch_Status=0 AND @i<@@CURSOR_ROWS)
BEGIN
if @f=1
BEGIN
set @ved=@ed1
END
set @ed1=@ved
set @td1=@vtd
set @tempEd=@ved
set @tempTd=@vtd
Fetch next from SetdateCur into @ved,@vtd
set @ed2=@ved
set @td2=@vtd
if DateDiff("d", @td1, @ed2)<63
begin
set @tempEd=@ed1
set @tempTd=@td2
set @f=1
end
ELSE BEGIN
set @tempEd=@ed2
set @tempTd=@td2
set @f=0
END
SET @i=@i+1
END
--SELECT @tempEd EnrollKeysEffDate,@tempTd EnrollKeysTermDate,@MemID MemID,@EligibleOrgID EligibleOrgID
INSERT INTO custom_dev.dbo.Wrk_Member_Interface_Correspondence_CertificateCoverLetter
(EnrollKeysEffDate,EnrollKeysTermDate,MemID,EligibleOrgID)
Values( @tempEd,@tempTd ,@MemID,@EligibleOrgID)
Close SetdateCur
Deallocate SetdateCur
FETCH NEXT FROM Setdatecur1 into @MemID,@EligibleOrgID
END
Close SetdateCur1
Deallocate SetdateCur1我这边最多只能有700条数据就没有内存了。
求解决方案。
DECLARE @MemID CHAR(15),@EligibleOrgID CHAR(15)
DECLARE SetdateCur1 Cursor FOR
--SELECT DISTINCT en.MemID,en.EligibleOrgID
--FROM custom_dev.dbo.Vw_Dbc_Enrollment AS en
--WHERE en.OrgPolicyID IS NOT NULL
--ORDER BY en.memID ASC
SELECT DISTINCT top 700 ek.memid,ek.eligibleorgid
FROM plandata_dev.dbo.enrollkeys AS ek
where ek.orgpolicyid IS NOT NULL
AND( ek.memid IS NOT NULL or ek.memid <>'')
AND ek.eligibleorgid LIKE 'ELG% '
ORDER BY ek.memID ASC
Open Setdatecur1
FETCH NEXT FROM Setdatecur1 into @MemID,@EligibleOrgID
While @@Fetch_Status=0
BEGIN
DECLARE SetdateCur Cursor Scroll FOR
SELECT en.EnrollKeysEffDate,en.EnrollKeysTermDate
FROM custom_dev.dbo.Vw_Dbc_Enrollment AS en
WHERE en.EligibleOrgID = @EligibleOrgID
AND en.MemID=@MemID
ORDER BY en.EnrollKeysEffDate ASC
Open Setdatecur
DECLARE @ved DATE,@vtd DATE,@ed1 DATE,@td1 DATE,@ed2 DATE,@td2 DATE,@tempEd DATE,@tempTd DATE
DECLARE @i int,@f int
set @i=0
set @f=0
Fetch SetdateCur into @ved,@vtd
While (@@Fetch_Status=0 AND @i<@@CURSOR_ROWS)
BEGIN
if @f=1
BEGIN
set @ved=@ed1
END
set @ed1=@ved
set @td1=@vtd
set @tempEd=@ved
set @tempTd=@vtd
Fetch next from SetdateCur into @ved,@vtd
set @ed2=@ved
set @td2=@vtd
if DateDiff("d", @td1, @ed2)<63
begin
set @tempEd=@ed1
set @tempTd=@td2
set @f=1
end
ELSE BEGIN
set @tempEd=@ed2
set @tempTd=@td2
set @f=0
END
SET @i=@i+1
END
SELECT @tempEd EnrollKeysEffDate,@tempTd EnrollKeysTermDate,@MemID MemID,@EligibleOrgID EligibleOrgID
Close SetdateCur
Deallocate SetdateCur
FETCH NEXT FROM Setdatecur1 into @MemID,@EligibleOrgID
END
Close SetdateCur1
Deallocate SetdateCur1
求解!!