SELECT sum( CASE 

                              WHEN Hours.Status=2 THEN Detail.TimeSpane

                              WHEN Hours.Status=4 THEN Detail.TimeSpane*2

                              ELSE 0 END) AS TimeSpane,Detail.Submitter,

                              

                    datepart(month,Detail.StartTime) AS Months,

                    datepart(year,Detail.StartTime) AS Years



             FROM   WfWorkLogDetail AS Detail

             JOIN   WorkingHour AS Hours

               ON   Detail.StartTime> = Hours.StartTime			   

             AND   Detail.StartTime<Hours.EndTime                         

              WHERE  Hours.Status = 2

                OR  Hours.Status = 4

             GROUP BY

                    Detail.Submitter,

                    datepart(month,Detail.StartTime),

                    datepart(year,Detail.StartTime)

USE [hamp]

GO

/****** 对象:  Table [dbo].[WfWorkLogDetail]    脚本日期: 07/04/2013 12:56:02 ******/