Stored Procedure Sum

[NeilV]

Hello,

I have the following stored procedure:

CREATE PROCEDURE avg_daily_prod
@start_date datetime = '01/11/2003',
@end_date datetime = '12/11/2003',
@day int =3,
@sums int = null output,
@days int = null output
@tempsum int= null,
as
set datefirst 1
set dateformat dmy
select @sums= sum(total_build) from shift_report where report_date >= @start_date)
and report_date <=@end_date and datepart(dw,report_date)=@day and total_build>0 group by report_date
select @days = count(distinct report_date) from shift_report where report_date >= @start_date and report_date <=@end_date and datepart(dw,report_date)=@day and total_build>0
print @tempsum
print @days
GO

the first select statement returns several rows and what I would like to do is to get the sum of the results from this statement and store it in the variable @tempsums. How can this be done? It seems like quite simple operation but i can't for the life of me figure out how to do it!!

Please Help!

Neil

 

[jby1]

What exactly do you want to get into @tempsum?

If it is the sum of total_build in shift_report, then drop the group by clause

Otherwise, please explain what it is you are after.

 

[NeilV]

Yep, that fixed it. I think was being a bit overcomplicated!

Thanks,

Neil