如何计算4个学期的上课天数.不包括周末 [英] How to calculate school days for 4 terms. excluding weekends

问题描述

我有以下一段代码可以计算一个学期的上学日，但是我需要它来满足4个学期的需要.

i''ve got the following piece of code which calculates schooldays for a single term but then i need it to cater for 4terms.

Declare 
 @total int = 0,
 @counter int = 0,
 @strt date,
 @end date
Begin 
  set @total= (select distinct DATEDIFF(DAY,startdate,enddate) 
			from SchoolTerms
			 where EmisCode =''500226884'' 
		   AND CurrentYear=''2009''
		   AND Quater=''Term1''	)
  set @strt=(Select Distinct StartDate
		   From SchoolTerms
		   where EmisCode =''500226884'' 
		   AND CurrentYear=''2009''
		   AND Quater=''Term1'')
  Set @end=(Select Distinct EndDate
		   From SchoolTerms
		   where EmisCode =''500226884'' 
		   AND CurrentYear=''2009''
		   AND Quater=''Term1'')
  while (@strt<@end) 
  BEGIN
	if DATEPART(weekday,@strt)=7  or DATEPART(weekday,@strt)= 1 
	begin
	    Declare @totals int = 0
 
	end
	else
	Begin
	
		set @counter+=1
		
		--print @counter
		--print DATEPART(weekday,@strt)
    end
    set @strt=dateadd(day,1,@strt)
    --
  End
  print @strt
  print @end
  print @counter
--print @total
End;

当我注释掉"AND Quater =" Term1""语句时，出现以下错误.

when i comment out the statement "AND Quater= ''Term1''" i get the following error.

Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

推荐答案

我知道这是一个古老的问题，但我认为我至少会给一个答案.

出现该错误的原因是因为SQL期望从子查询返回一个值，并且当您不提供该术语时，有超过一行与条件匹配.该查询需要的是所选术语的开始日期和结束日期.取消学期时，它会给出每个学期的开始日期和每个学期的结束日期.这就是为什么您会收到错误消息.

您在这里有两个选择.最简单的方法是运行此查询四次(每个术语一次).那不是很有效，但是会很快.

最好的办法是重写查询以处理一个或多个术语.但是，这将需要大量的SQL知识.

I know this is an old question, but I thought I would at least give it an answer.

The reason you get that error is because SQL is expecting one value back from the sub-query and when you don''t supply the term, more than one row matches the criteria. What this query wants is the start date and end date for the selected term. When you take off the term, it gives the start date for each term and the end date for each term. That is why you get the error.

You have two options here. The easiest thing to do would be to run this query four times (once for each term). That isn''t terribly efficient but it would be quick.

The best thing to do would be to rewrite the query to handle one or more terms. However, that would take a lot of SQL knowledge.

这篇关于如何计算4个学期的上课天数.不包括周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！