SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can this be decursified?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 01/02/2012 :  18:22:55  Show Profile  Reply with Quote
I'm thinking not, due to the "SET @stdate=@stDate+@recalt" line...


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       

Edited by - ferrethouse on 01/02/2012 18:23:18

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/02/2012 :  20:47:02  Show Profile  Reply with Quote
May be something like this? Hard to see if the logic is correct without some sample data to look at (at least for me, it is), so this is really a wild guess.
;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';
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 01/04/2012 :  17:27:03  Show Profile  Reply with Quote
That's genius. I'm still trying to figure out this line...

CROSS JOIN master..spt_values s

But interestingly, if I run this along with the cursor the execution plan indicates that this performs worse (65% of the query cost relative to the batch). I know those query cost estimates aren't always accurate but it is surprising.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/04/2012 :  17:44:10  Show Profile  Reply with Quote
If the relative cost is higher, that could also mean that the query I posted is wrong. Does it give the right results?
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 01/05/2012 :  02:20:43  Show Profile  Reply with Quote
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?




It does give the correct results.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000