Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
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;


when i comment out the statement "AND Quater= 'Term1'" i get the following error.
XML
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.
Posted
Updated 2-Oct-11 21:20pm
v2

1 solution

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900