Calculations
-
Hi, I need to create a table in SQL that will compute costs. I have to read in some information from two other tables and use this to populate the new table from which the costs are calculated. As the data is from a website, it is possible that it will change and I need the costs to update if the data changes. Do I need to create a function to do this, or will the table do it on its own? ( I elected to do a function, but if there is a better way I am open to that) This is what I have so far:
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
goCREATE FUNCTION [dbo].[fcn_computecosts]
( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint,
@Cost money, @percent int, @totalDays int, @totalcost)RETURNS @ComputeCosts TABLE
( ProjectID int, userID int, startDate datetime, enddate datetime, quarter tinyint,
Cost money, percent int, totalDays int, totalcost)AS
BEGIN
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*totaldays*Cost*8) AS totalcostFROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
totalDays, totalcost
ORDER BY P.ProjectID
RETURN
ENDHowever when I run this I am getting the following error: "msg 207, Level 16, state 1: Invalid column name 'totalDays', 'totalcost', 'Cost' Please help!
-
Hi, I need to create a table in SQL that will compute costs. I have to read in some information from two other tables and use this to populate the new table from which the costs are calculated. As the data is from a website, it is possible that it will change and I need the costs to update if the data changes. Do I need to create a function to do this, or will the table do it on its own? ( I elected to do a function, but if there is a better way I am open to that) This is what I have so far:
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
goCREATE FUNCTION [dbo].[fcn_computecosts]
( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint,
@Cost money, @percent int, @totalDays int, @totalcost)RETURNS @ComputeCosts TABLE
( ProjectID int, userID int, startDate datetime, enddate datetime, quarter tinyint,
Cost money, percent int, totalDays int, totalcost)AS
BEGIN
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*totaldays*Cost*8) AS totalcostFROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
totalDays, totalcost
ORDER BY P.ProjectID
RETURN
ENDHowever when I run this I am getting the following error: "msg 207, Level 16, state 1: Invalid column name 'totalDays', 'totalcost', 'Cost' Please help!
Isn't @totalcost missing the datatype declaration ?
CREATE FUNCTION [dbo].[fcn_computecosts]( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint, @Cost money, @percent int, @totalDays int, @totalcost) -
Hi, I need to create a table in SQL that will compute costs. I have to read in some information from two other tables and use this to populate the new table from which the costs are calculated. As the data is from a website, it is possible that it will change and I need the costs to update if the data changes. Do I need to create a function to do this, or will the table do it on its own? ( I elected to do a function, but if there is a better way I am open to that) This is what I have so far:
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
goCREATE FUNCTION [dbo].[fcn_computecosts]
( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint,
@Cost money, @percent int, @totalDays int, @totalcost)RETURNS @ComputeCosts TABLE
( ProjectID int, userID int, startDate datetime, enddate datetime, quarter tinyint,
Cost money, percent int, totalDays int, totalcost)AS
BEGIN
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*totaldays*Cost*8) AS totalcostFROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
totalDays, totalcost
ORDER BY P.ProjectID
RETURN
ENDHowever when I run this I am getting the following error: "msg 207, Level 16, state 1: Invalid column name 'totalDays', 'totalcost', 'Cost' Please help!
In your group by clause, you are trying to group by totalDays and totalcost which are aliases to computed columns. I've seen it where you have to repeat the function again in the group by clause instead of referencing the alias name. Something like below.
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent, COUNT(C.date), ((percent/100)*totaldays*Cost*8)Do you really need to group by these values ? -
Hi, I need to create a table in SQL that will compute costs. I have to read in some information from two other tables and use this to populate the new table from which the costs are calculated. As the data is from a website, it is possible that it will change and I need the costs to update if the data changes. Do I need to create a function to do this, or will the table do it on its own? ( I elected to do a function, but if there is a better way I am open to that) This is what I have so far:
set ANSI_NULLS ON
go
set QUOTED_IDENTIFIER ON
goCREATE FUNCTION [dbo].[fcn_computecosts]
( @ProjectID int, @userID int, @startDate datetime, @enddate datetime, @quarter tinyint,
@Cost money, @percent int, @totalDays int, @totalcost)RETURNS @ComputeCosts TABLE
( ProjectID int, userID int, startDate datetime, enddate datetime, quarter tinyint,
Cost money, percent int, totalDays int, totalcost)AS
BEGIN
SELECT
P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
COUNT(C.date) AS totalDays, ((percent/100)*totaldays*Cost*8) AS totalcostFROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <=
P.enddate
WHERE (C.isWeekday =1) AND (C.isHoliday=0)
GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent,
totalDays, totalcost
ORDER BY P.ProjectID
RETURN
ENDHowever when I run this I am getting the following error: "msg 207, Level 16, state 1: Invalid column name 'totalDays', 'totalcost', 'Cost' Please help!
You Not Grouping Properly SELECT P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent, COUNT(C.date) AS totalDays, ((percent/100)*COUNT(C.date)*Cost*8) AS totalcost FROM Project AS P INNER JOIN Calendar AS C ON Calendar.date >P.startDate AND Calendar.date <= P.enddate WHERE (C.isWeekday =1) AND (C.isHoliday=0) GROUP BY P.ProjectID, P.userID, P.startDate, P.enddate, C.quarter,P.Cost, P.percent ORDER BY P.ProjectID You Have No Need To It totalDays,totalCost in Group By