March 19, 2009 at 1:20 pm
Hi,
I'm trying to create a table that will compute cost information. The data will be imported from a pre-existing table and all computations will be done on that data and then stored in the new table. I need the table to update itself whenever the data is changed as the data is being read from/to an asp.net page (create a function???).
This is what I have so far, but I keep getting errors, specifically "msg 207, Level 16, state 1: Invalid Column Name for Cost, Days and UserCost"
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fcn_ComputeCost]
( @ProjectID int,
@user-id int,
@StartDate Datetime,
@EndDate Datetime,
@Quarter tinyint,
@UserCost money,
@Percentage int,
@TotalDays int,
@TotalUserCost money
)
RETURNS @ComputeCost TABLE
(ProjectID int,
UserID int,
StartDate Datetime,
EndDate Datetime,
Quarter tinyint,
UserCost money,
Percentage int,
TotalDays int,
TotalUserCost money
)
AS
BEGIN
INSERT @ComputeCost
SELECT P.ProjectID ,
P.UserID ,
P.StartDate ,
P.EndDate ,
C.Quarter ,
P.UserCost ,
P.Percentage ,
COUNT(C.date) AS TotalDays ,
((Percentage/100)*UserCost*Days*8) AS TotalUserCost
FROM Projects AS P INNER JOIN Calendar AS C ON C.date > P.StartDate AND C.date <= P.EndDate
WHERE (C.isWeekday = 1) AND (C.isHoliday =0)
GROUP BY (P.UserID ,
P.StartDate ,
P.EndDate ,
C.Quarter ,
P.UserCost ,
P.Percentage ,
TotalDays ,
TotalUserCost
)
ORDER BY P.ProjectID
RETURN
END
March 19, 2009 at 1:39 pm
Hi
It seems that some of your specified columns do not exist.
Try only to execute the SELECT part without any function:
SELECT P.ProjectID ,
P.UserID ,
P.StartDate ,
P.EndDate ,
C.Quarter ,
P.UserCost ,
P.Percentage ,
COUNT(C.date) AS TotalDays ,
((Percentage/100)*UserCost*Days*8) AS TotalUserCost
FROM Projects AS P INNER JOIN Calendar AS C ON C.date > P.StartDate AND C.date <= P.EndDate
WHERE (C.isWeekday = 1) AND (C.isHoliday =0)
GROUP BY (P.UserID ,
P.StartDate ,
P.EndDate ,
C.Quarter ,
P.UserCost ,
P.Percentage ,
TotalDays ,
TotalUserCost
)
Greets
Flo
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply