问获取SQL SERVER中的当前学期。

EN

DECLARE @ActiveSemester INT

SET @ActiveSemester = ISNULL((SELECT [value]
FROM spms_tblProfileDefaults WHERE eid = 7078 and ps_id = 1), (SELECT [value] 
FROM spms_configs WHERE actions = 'semester_active') )

SELECT FORMAT((SUM((DATEDIFF(MINUTE, a.actual_start_time, a.actual_end_time) 
- isnull(datediff(minute, break_start, break_end), 0))/ 60.0)), '0.00')
FROM spms_tblSubTask as a
LEFT JOIN pmis.dbo.employee as b ON a.eid = b.eid
LEFT JOIN pmis.dbo.time_reference as c ON c.code = ISNULL(b.TimeReference, 'TIME14')
LEFT JOIN dbo.spms_vwOrganizationalChart as e ON a.eid = e.eid
cross apply
 (
        select  break_start = case  when c.break_from between a.actual_start_time and a.actual_end_time
                    then c.break_from
                    when a.actual_start_time between c.break_from and c.break_to
                    then a.actual_start_time
                    else NULL
                    end,
                break_end  = case   when c.break_to between a.actual_start_time and a.actual_end_time
                    then c.break_to
                    when a.actual_end_time between c.break_from and c.break_to
                    then a.actual_end_time
                    end
) as d
WHERE
b.Shift = 0 and a.eid = 7078 and
YEAR(a.start_date_time) = YEAR(GETDATE()) and a.action_code = 1 
and 
(CASE 
WHEN 
@ActiveSemester = 1
THEN 
a.start_date_time BETWEEN CAST(CONCAT('01/01/',YEAR(GETDATE())) as date) AND     
CAST(CONCAT('06/30/',YEAR(GETDATE())) as date)
ELSE 
a.start_date_time BETWEEN CAST(CONCAT('07/01/',YEAR(GETDATE())) as date) AND     
CAST(CONCAT('12/31/',YEAR(GETDATE())) as date)
END)