Last post May 27, 2020 07:11 AM by YihuiSun
May 21, 2020 10:06 PM|progdever|LINK
I am able to use this stmt. for getting the results I want:
Select SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen))
I am trying to put this in a function so that I can add the function as a computed column in the table. But the computed column is always returning zero.
DECLARE @MTDLeads AS INT
@MTDLeads =SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen))
I added a new column to the table tblA as this function, but getting zero values for every row. As I indicated earlier, my first sql statement running on the table itself yields correct results.
May 22, 2020 11:53 AM|YihuiSun|LINK
Your code can only return the last value, I guess the last value of your data is zero, so each of your rows is zero.
You should use Table-Valued User-Defined Functions.
Note:For more information about "Use Table-Valued User-Defined Functions", please check this
CREATE FUNCTION dbo.CalculatedMTDLeads5
SELECT SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) AS MTDLeads
SELECT MTDLeads FROM dbo.CalculatedMTDLeads5()
Here is the result.
May 22, 2020 04:25 PM|progdever|LINK
Thanks, but, when I add this for a computed column, I get an error:
ALTER TABLE dbo.tblA
ADD MTDLeadsNew AS dbo.CalculatedMTDLeadsNew()
Error: Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CalculatedMTDLeadsNew", or the name is ambiguous.
May 25, 2020 07:43 AM|YihuiSun|LINK
First of all, if you use the method I mentioned above, because "CalculatedMTDLeadsNew" returns a table, you will get an error if you write like this.
Note: For more usage of ALTER, please refer to this link.
Second, I guess you want to store the value returned by Function in a the computed column of the database. You can create a computed column first, and then store the value in the calculated column.
CREATE FUNCTION dbo.CalculatedMTDLeads39
SELECT Id,SUM(TodaysLeads) OVER(PARTITION BY Username, MONTH(DateChosen)) AS MTDLeads
ALTER TABLE dbo.tblA
ADD MTDLeadsNew INT
FROM tblA A ,dbo.CalculatedMTDLeads39() B
May 26, 2020 05:05 PM|progdever|LINK
Thanks for this. So this would mean I have to run the update statement constantly to get the calculated column value? I was hoping that this gets calculated automatically. Any thoughts?
May 27, 2020 07:11 AM|YihuiSun|LINK
As long as you call dbo.CalculatedMTDLeads39 (), it will be calculated. "Update" is used to add the result returned by the function to calculated column.
Remarks: These links can help you, please refer to it.