October 7, 2010 at 10:31 pm
Hi,
Please execute below query that will create sample table with data.I trying to run stored procedure on this table but getting error,
Invalid column name 'Totalhours'.
Invalid column name 'EmpNo'
I have ColumnNames in my table but still getting error.
CREATE TABLE TestTable(
WeekStartDate DATETIME,
TotalHours varchar(50),
EMpNo int,
)
SET IDENTITY_INSERT mytable ON
Insert into TestTable(WeekStartDate,TotalHours,EmpNo)
select '8/1/2010','25:1','1' union all
select '8/8/2010','24:3','1' union all
select '8/15/2010','45:2','1' union all
select '8/22/2010','26:1','1' union all
select '8/1/2010','29:1','2' union all
select '8/8/2010','42:3','2' union all
select '8/15/2010','45:2','2' union all
select '8/22/2010','29:1','2' union all
select '8/1/2010','52:1','3' union all
select '8/8/2010','46:3','3' union all
select '8/15/2010','32:2','3' union all
select '8/22/2010','26:1','3' union all
select '8/1/2010','25:1','4' union all
select '8/8/2010','24:3','4' union all
select '8/15/2010','45:2','4' union all
select '8/22/2010','26:1','4'
StoredProcedure
ALTER PROCEDURE [dbo].[SAR_Sp_GetScheduledHours](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + '' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT convert(varchar,weekstartdate,110) as StartDate,'+
convert(varchar,dbo.FDiv(Count(Case When Totalhours<='24:0' Then EmpNo end),2)*100)+ '+% as Less Than 24 hrs,'+
convert(varchar,dbo.FDiv(Count(Case when Totalhours>'24:0' and Totalhours<='48:0' Then EmpNo end),2)*100)+ '+% as b/w 24 and 48,'+
convert(varchar,dbo.FDiv(Count(Case when Totalhours>'48:0' Then EmpNo end),2)*100) + '+% as More than 48 hrs
FROM TestTable where CampaignID=@CampaignID
AS SourceTable
PIVOT
(
max(Less Than 24 hrs),max(b/w 24 and 48),max(More than 48 hrs)
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable'
EXEC(@query)
END
October 8, 2010 at 4:33 am
You are missing a quote somewhere.
You are trying to use Totalhours and EmpNo in a SET @query = . It can only work inside quotes since your FROM TestTable is just a string so far.
It's not easy to explain in a post but obvious when you cut and paste your procedure in the Management Studio.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply