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.
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.