[TSQL] 每月工作紀錄表繳交狀況

ENIX.DQ

說明：
　　每張工作紀錄表均登載一工作時間，固定為每項課程，每週均須繳交一張工作紀錄表

目標：課程總表中包含每週繳交狀況(是否缺交...等)


SQL語法如下 (test for MSSQL 2005 Express OK!)：

1. 
   
2. declare @StartWeek int
   
3. declare @EndWeek int
   
4. declare @SQLCOMMAND varchar(8000)
   
5. set @StartWeek = 40
   
6. set @EndWeek = 50
   
7. Set @SQLCOMMAND = ' SELECT CourseCode ,StudentID, CourseDept, RTA_StudentID, '
   
8. While @StartWeek <= @EndWeek
   
9. Begin
   
10. Set @SQLCOMMAND = @SQLCOMMAND + ' ISNULL((SELECT TracingLocation FROM TA_RTA_WorkRecord WHERE (DATEPART(ww, WorkEnd) = '+ Str(@StartWeek) +') and a.CourseCode=CourseCode),0) as Week'+ LTrim(str(@StartWeek))
    
11. If(@StartWeek <@EndWeek)
    
12. Begin
    
13. Set @SQLCOMMAND = @SQLCOMMAND + ','
    
14. End
    
15. set @StartWeek = @StartWeek+1
    
16. End
    
17. 
    
18. Set @SQLCOMMAND = @SQLCOMMAND + ' FROM TA_CourseData as a WHERE (Len(RTA_StudentID)>0)'
    
19. 
    
20. --select @SQLCOMMAND
    
21. execute (@SQLCOMMAND)
    

複製代碼