Complex insert problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex insert problem

Author

Topic

mavrick5
Starting Member

4 Posts

Posted - 2011-05-09 : 02:24:48

I hope someone can help me. I am trying to insert data into a table within a stored procedure which is returning an error. It is a copy of another stored procedure which does work. I am scratching my head trying to work out what I am doing wrong. Here is the code that does work. What I am trying to do is add an extra column for the product code, but when I do, I get an error saying that the column isn't in the the group by clause:

create proc TR_TimeUtilisation (
@inModel varchar(30),
@inStartDate datetime,
@inEndDate datetime,
@inTempTable sysname) as

declare @tSQL varchar(4000)

set @tSQL = 'declare @inModel varchar(30),
@inStartDate datetime,
@inEndDate datetime,
@FirstDoM datetime,
@NextMonth datetime,
@DaysRent int,
@NumberOfRents int,
@AvgRent numeric(19,2)

set @inModel = ''' + @inModel + '''
set @inStartDate = ''' + convert(varchar,@inStartDate,20) + '''
set @inEndDate = ''' + convert(varchar,@inEndDate,20) + '''
set @NumberOfRents = 0
set @FirstDoM = @inStartDate
set @NextMonth = dateadd(m, 1, convert(datetime, convert(varchar,year(@FirstDoM)) + ''-'' + convert(varchar,month(@FirstDoM)) + ''-1'',20))

while @FirstDoM < @inEndDate begin
if @NextMonth >= @inEndDate
set @NextMonth = @inEndDate+1

insert into ' + @inTempTable + '
select @FirstDoM ''FirstDoM'', isnull(sum(Available),0) ''AvailDays'', isnull(sum(DaysRent),0) ''RentDays'',
isnull((100.0 * sum(DaysRent))/sum(Available),0) ''Util'', isnull(sum(RentCount),0) ''RentCount'' from (
select MajorStatus,
case when MajorStatus = ''REN'' then count(1) else 0 end ''RentCount'',
case when MajorStatus = ''REN'' then
sum(datediff(d, case when StatusDate > @FirstDoM then StatusDate else @FirstDoM end,
case when EndStatusDate >= @NextMonth then @NextMonth when EndStatusDate = 0 then @NextMonth else EndStatusDate + 1 end))
else 0 end ''DaysRent'',
sum(datediff(d, case when StatusDate > @FirstDoM then StatusDate else @FirstDoM end,
case when EndStatusDate >= @NextMonth or EndStatusDate = 0 then
case when @NextMonth = @inEndDate+1 then @NextMonth - 1 else @NextMonth end else EndStatusDate end)) ''Available''
from AssetAud
where Model = @inModel
and MajorStatus not in (''SOL'', ''INT'')
and MinorStatus not in (''ROB'', ''ICO'', ''RPO'')
and StatusDate < @NextMonth
and (EndStatusDate >= @FirstDoM or EndStatusDate = 0)
group by MajorStatus) as Results

set @FirstDoM = @NextMonth
set @NextMonth = dateadd(m, 1, @NextMonth)
end'

exec (@tSQL)

GO

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 03:39:24

would you like to share complete text of error ???

--------------------------
http://connectsql.blogspot.com/

shamasm
Starting Member

11 Posts

Posted - 2011-05-09 : 07:28:25

can you high light which column you are adding

mavrick5
Starting Member

4 Posts

Posted - 2011-05-09 : 20:30:43

The above is the working version. All I want to do is add a column called AssetNo to the table and the insert statement. However, when I do that, I get the following error.

Server: Msg 8118, Level 16, State 1, Line 19
Column 'Results.AssetNo' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

The changes I added to the script were:

insert into ' + @inTempTable + '
select AssetNo ''AssetNo'',@FirstDoM ''FirstDoM'', isnull(sum(Available),0) ''AvailDays'', isnull(sum(DaysRent),0) ''RentDays'',
isnull((100.0 * sum(DaysRent))/sum(Available),0) ''Util'', isnull(sum(RentCount),0) ''RentCount'' from (
select AssetNo, MajorStatus,
case when MajorStatus = ''REN'' then count(1) else 0 end ''RentCount'',
case when MajorStatus = ''REN'' then
sum(datediff(d, case when StatusDate > @FirstDoM then StatusDate else @FirstDoM end,
case when EndStatusDate >= @NextMonth then @NextMonth when EndStatusDate = 0 then @NextMonth else EndStatusDate + 1 end))
else 0 end ''DaysRent'',
sum(datediff(d, case when StatusDate > @FirstDoM then StatusDate else @FirstDoM end,
case when EndStatusDate >= @NextMonth or EndStatusDate = 0 then
case when @NextMonth = @inEndDate+1 then @NextMonth - 1 else @NextMonth end else EndStatusDate end)) ''Available''
from AssetAud
where Model = @inModel
and MajorStatus not in (''SOL'', ''INT'')
and MinorStatus not in (''ROB'', ''ICO'', ''RPO'')
and StatusDate < @NextMonth
and (EndStatusDate >= @FirstDoM or EndStatusDate = 0)
group by AssetNo, MajorStatus) as Results

mavrick5
Starting Member

4 Posts

Posted - 2011-05-09 : 22:01:01

For testing purposes, I have added the following line to the where clause of the select:

and AssetNo = 130200

I get the same error. However, if I hardcode that number into the insert, such as:

insert into '+ @inTempTable + '
select 130200 'AssetNo', @FirstDoM....

then the insert all works. What am I not seeing here???

mavrick5
Starting Member

4 Posts

Posted - 2011-05-09 : 22:09:04

problem solved!!

The select query returns multiple rows per date, but my insert is expecting 1 row per date. So... I changed the insert to:

insert into '+ @inTempTable + '
select max(AssetNo) 'AssetNo', @FirstDoM....

and now it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources