declare c1 cursor FAST_FORWARD for         
select distinct Classes.LessonID,DateStart,RecurEndDate,RecurAlt,RecurType,ClassID,Classes.CourseID          
from Classes inner join Subscription on  Classes.CourseID=Subscription.CourseID and Subscription.StudentsID=@StudentsID        
and RecurType=1        
        
open c1        
fetch next from c1 into @lessonID,@stdate,@enddate1,@recalt,@recurtype,@classID,@courseID        
        
WHILE @@FETCH_STATUS=0        
        
 BEGIN       
     WHILE ( DATEADD(dd,0,DATEDIFF(dd,0,@stdate))<= (DATEADD(dd,0,DATEDIFF(dd,0,@enddate1))))      
       BEGIN        
           insert into #RecCalender (ClassDate,LessonID,ClassID,CourseID,RecurAlt,RecurType)       
           values(@stdate,@lessonID,@classID,@courseID,@recalt,@recurtype)       
           SET @stdate=@stdate+@recalt       
       END        
   fetch next from c1 into @lessonID,@stdate,@enddate1,@recalt,@recurtype,@classID,@courseID        
         
 END        
        
CLOSE C1        
DEALLOCATE C1       

;WITH cte AS
(
   SELECT 
      Classes.LessonID,
      DateStart,
      RecurEndDate,
      RecurAlt,
      RecurType,
      ClassID,
      Classes.CourseID
   FROM
      Classes
      INNER JOIN SUBSCRIPTION
         ON  Classes.CourseID = SUBSCRIPTION.CourseID
         AND SUBSCRIPTION.StudentsID = @StudentsID
         AND RecurType = 1
)
INSERT INTO #RecCalender
SELECT
   DATEADD(dd,Number*RecurAlt,CAST(DateStart AS DATE)) AS ClassDate,
   LessonID,
   ClassID,
   CourseID,
   RecurAlt,
   RecurType
FROM
   cte c
   CROSS JOIN master..spt_values s
WHERE
   Number*RecurAlt < DATEDIFF(dd,DateStart,RecurEndDate)
   AND s.type='P';

quote:

---

Originally posted by sunitabeck

If the relative cost is higher, that could also mean that the query I posted is wrong. Does it give the right results?

---