CODEWITHSUNDEEP
sql-servert-sql
Marshall10001
Marshall10001

Reputation: 45

get a running total of dates in SQL SERVER

I want to create a running list of dates which adds 6 weeks on to the date above it. So far my code gives me a start and end dates. I need to show the 6 week intervals between the start and end dates

aLearnerRef     startdate     enddate       reviewdate
------------------------------------------------------
399750423600    2013-11-27    2014-06-12    2014-01-08
399750423600    2013-11-27    2014-06-12    2014-02-19
399750423600    2013-11-27    2014-06-12    2014-04-02
399750423600    2013-11-27    2014-06-12    2014-05-14

I want it to loop until it gets to the last date before the enddate. I cant work out a possible way to do this. Here is what code I have so far:

SELECT a.aLearnerRef,  x.startdate, x.enddate
FROM aims a
inner join vStudyP s
on s.LearnerRef = a.aLearnerRef
join (SELECT aLearnerRef, min(learnstartdate) startdate, max(learnplannedenddate) enddate
        FROM aims
        GROUP BY aLearnerRef) x
on a.aLearnerRef = x.aLearnerRef
group by a.aLearnerRef,  x.startdate, x.enddate

Upvotes: 0

Views: 69

Answers (2)

AK47
AK47

Reputation: 3797

try this code,

with cte as
(
select ak.akid,sdate,edate,DATEADD(WEEK,6,sdate) rdate from ak
Union All

select ak.akid,ak.sdate,ak.edate,DATEADD(WEEK,6,rdate) rdate from ak
inner join cte on  rdate between ak.sdate and ak.edate 
where  DATEADD(WEEK,6,rdate) between ak.sdate and ak.edate 


)
select * from CTE

If more than one records,

with cte as
    (
    select ak.akid,sdate,edate,DATEADD(WEEK,6,sdate) rdate from ak
    Union All

    select ak.akid,ak.sdate,ak.edate,DATEADD(WEEK,6,rdate) rdate from ak
    inner join cte on  rdate between ak.sdate and ak.edate 
    where  DATEADD(WEEK,6,rdate) between ak.sdate and ak.edate 
          and cte.akid = ak.akid

    )
    select * from CTE

link for demo, http://sqlfiddle.com/#!3/31d30/5

Upvotes: 1

dean
dean

Reputation: 10098

Use the auxilliary numbers table:

SELECT a.aLearnerRef,  dateadd(day,n,a.startdate), a.enddate
FROM aims a
inner join numbers num
on n < datediff(day,startdate, enddate)

Upvotes: 0

Related Questions