[TSQL] 每月工作紀錄表繳交狀況
說明：每張工作紀錄表均登載一工作時間，固定為每項課程，每週均須繳交一張工作紀錄表
目標：課程總表中包含每週繳交狀況(是否缺交...等)
SQL語法如下 (test for MSSQL 2005 Express OK!)：
declare @StartWeek int
declare @EndWeek int
declare @SQLCOMMAND varchar(8000)
set @StartWeek = 40
set @EndWeek = 50
Set @SQLCOMMAND = ' SELECT CourseCode ,StudentID, CourseDept, RTA_StudentID, '
While @StartWeek <= @EndWeek
Begin
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))
If(@StartWeek <@EndWeek)
Begin
Set @SQLCOMMAND = @SQLCOMMAND + ','
End
set @StartWeek = @StartWeek+1
End
Set @SQLCOMMAND = @SQLCOMMAND + ' FROM TA_CourseData as a WHERE (Len(RTA_StudentID)>0)'
--select @SQLCOMMAND
execute (@SQLCOMMAND)
頁:
[1]