插入函数中的SQL SP错误聚合函数 [英] SQL SP Error aggregate function in insert function
本文介绍了插入函数中的SQL SP错误聚合函数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
alter PROCEDURE [dbo].[Monthly_Report]--'15/05/2013 00:00','15/05/2013 23:59'
(
@fromdate datetime,
@todate datetime
)
AS
BEGIN
SET NOCOUNT ON
create table #tempmonthly
(
Duration varchar(50),
hub_id int,
occupancy float,
occupancyGreater4 float,
[adhoc request] int,
[average request] int,
[cut paste percentage] float,
[Total Unique users] float
)
DECLARE @pickcount float,
@dropcount float,
@pickcount4 float,
@dropcount4 float,
@totalemp float,
@totalemp4 float,
@routeidcount float,
@routeidcount4 float,
@occupancy float,
@occupancy4 float,
@adhocrequestcount float,
@routedfromroutepro float,
@cutpastecount float,
@cutpaste float,
@totaldays float,
@T_Drop_Emp float,
@T_Pick_Emp float,
@T_Emp float,
@Duration varchar(50),
@totalunique_users float
BEGIN TRY
BEGIN TRANSACTION
IF(DATEDIFF(mm, @fromdate, @todate) >= 0)
BEGIN
print @fromdate
print @todate
select facility_id into #facility from tbl_facility where hub=1 order by facility_id desc
Declare @R_id int
DECLARE CURSOR_G CURSOR FOR
Select facility_id from #facility
OPEN CURSOR_G
FETCH CURSOR_G INTO @R_id
WHILE @@Fetch_Status = 0
BEGIN
--------------------------------------------------occupancy--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print 'HUBID-->' + cast(@R_id as varchar)
print '@routeidcountBEFOREset-->' + cast(@routeidcount as varchar)
--set @routeidcount=0
print '@routeidcountBEFORE-->' + cast(@routeidcount as varchar)
select @routeidcount=count(1) from tbl_Route_Header WITH(NOLOCK)
where Inactive = 0 AND Company_ID = 1
AND DATEDIFF(dd,@fromdate,Appointment_DateTime) >= 0
AND DATEDIFF(dd,Appointment_DateTime,@todate) >= 0
AND Route_Status_ID IN(1,2,3,4,5,6,8,9,10,11) and Hub_ID= @R_id
and current_occupancy between 0 and 4
print '@routeidcountAFTER-->' + cast(@routeidcount as varchar)
--Pick employess
set @pickcount=0
select @pickcount=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=1 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID= @R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 0 and 4
print 'PICK COUNT AFTER-->' + cast(@pickcount as varchar)
--Drop employess
set @dropcount=0
select @dropcount=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=0 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 0 and 4
print 'DROPCOUNT AFTER-->' + cast(@dropcount as varchar)
set @totalemp=0
set @totalemp=@dropcount+@pickcount
print 'TOTAL COUNT-->' + cast(@totalemp as varchar)
if(@totalemp >0)
begin
set @occupancy=@totalemp/@routeidcount
end
else set @occupancy=0.00
print 'occupancy--> ' + cast(@occupancy as varchar)
--- the sp continues------i am skipping the rest of part----
--finally the cursor ends..
insert into #tempmonthly(Duration,hub_id,occupancy,occupancyGreater4,[adhoc request],[average request],[cut paste percentage],[Total Unique users]) values(@Duration,@R_id,@occupancy,@occupancy4,@adhocrequestcount,ISNULL(@T_Emp/@TotalDays,0.00),@cutpaste,isnull(@totalunique_users,0.00))
FETCH CURSOR_G INTO @R_id
END
CLOSE CURSOR_G
DEALLOCATE CURSOR_G
END
---after my cursor ends....i want to insert the last row as FINAL STATISTICS...
--that will give me the occupancy as avg of all the above rows...adhocrequest as the sum and so --on,,,,so what i am doing ,,,,after the cursor ends,,,i am writing as
insert into #tempmonthly(Duration,occupancy,occupancyGreater4,[adhoc request],[average request],[cut paste percentage],[Total Unique users]) values('FINAL STATISTICS',avg(@occupancy),avg(@occupancy4),sum(@adhocrequestcount),avg(ISNULL(@T_Emp/@TotalDays,0.00)),avg(@cutpaste),sum(isnull(@totalunique_users,0.00)))
select * from #tempmonthly with(nolock)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO tbl_Common_Error_Log
(Err_Number,Err_Severity,Err_State,Err_Pro,Err_Line,Err_Message,Created_On)
SELECT
ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage,getdate()
END CATCH
SET NOCOUNT OFF
END
- - 但是我得到了错误，比如
- 在插入语句的值列表中不允许聚合。
- 在我的上一篇文章中有些人建议我....使用插入...我必须尝试明天...但将---解决pblm
SHOUTING删除 - OriginalGriff [/ edit ]
--But i got error like
--Aggregates are not allowed in the values list of an insert statement.
--in my last post some suggested me....to use insert into ...i have to try tomorrow...but will ---that solve the pblm
[edit]SHOUTING removed - OriginalGriff[/edit]
推荐答案
insert into #tempmonthly(Duration,occupancy,occupancyGreater4,[adhoc request],[average request],[cut paste percentage],[Total Unique users])
SELECT 'FINAL STATISTICS',avg(occupancy),avg(occupancyGreater4),sum([adhoc request]),
ISNULL(@T_Emp/@TotalDays,0.00),avg([cut paste percentage]),sum(isnull([total unique users],0.00))
from #tempmonthly with(nolock)
这篇关于插入函数中的SQL SP错误聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
