I need help to create one script where i got stuck.
MemberId BeginDate EndDate Output
1039725910 3/1/2014 8/10/2014 0 End on 10th August
1039725910 8/11/2014 11/10/2014 1 Start on 11th August, 1 day gap
1039725910 11/11/2014 12/31/2014 1 Start on 11th August, 1 day gap
1166164140 1/1/2014 4/30/2039 0 End on 30 April
1166164140 2/5/2014 12/30/2039 2 Start on 1st May, Here is a 2 days gap
Here For one member I have three different begin and end date. for the first records for each member, it would be 0, for the 2nd records, the gap would be (2nd Begindate - 1st EndDate). For 3rd record, The difference would be (3rd Begin date - 2nd EndDate) and so on...I am not able to attach any screenshot.
Kindly help me on this.
Regards, Ratan
You can use the row_number() window function together with a self-join to access the previous row partitioned by MemberId like this:
select
a.MemberId,
a.BeginDate,
a.EndDate,
Output = ISNULL(DATEDIFF(DAY, isnull(b.EndDate, a.BeginDate), a.BeginDate), 0)
from
(select *, rn = ROW_NUMBER() over (partition by memberid order by begindate) from members) a
left join
(select *, rn = ROW_NUMBER() over (partition by memberid order by begindate) from members) b
on a.MemberId = b.MemberId and a.rn - 1 = b.rn
With your sample data this would give you:
MemberId BeginDate EndDate Output
1039725910 2014-03-01 2014-08-10 0
1039725910 2014-08-11 2014-11-10 1
1039725910 2014-11-11 2014-12-31 1
1166164140 2014-01-01 2039-04-30 0
1166164140 2014-05-02 2039-12-30 -9129
If you need to disregard the year component you'll have to do some date arithmetic.
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다