Not getting the right row in the function

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

General SQL Server Forums

New to SQL Server Programming

Not getting the right row in the function

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2010-04-22 : 09:50:17

HI I have a problem getting the right data. Here is the data.
USCATVAL BudgetDateFrom BudgetAmount Dept
Incon. Pro 2008-12-01 00:00:00.000 0.00 NULL
Incon. Pro 2009-07-01 00:00:00.000 8900.00 Incontinence
Incon. Pro 2010-01-20 00:00:00.000 0.00 Medical
Incon. Pro 2009-07-01 00:00:00.000 76.26 NULL
Incon. Pro 2010-01-01 00:00:00.000 0.00 NULL

In my transaction process, I am reading on the USCATVAL. When I encounter
Incon. Pro, I check the above table for the latest budget date-from.
which ALSO has a dept value, that is not NULL.
But the code is only looking apparently at the latest value. Currently I am getting Medical department but not incontinence.

there are 2 functions that is returning a 0. The second gets the department.

ALTER FUNCTION [dbo].[_FnGetBudgetAmount]
(
-- Add the parameters for the function here
@Year Date,
@CustNMBR varchar(100),
@USCATVAL varchar(100)
)
RETURNS float
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar float
set @ResultVar = 0
-- Add the T-SQL statements to compute the return value here
SELECT @ResultVar = isnull( [BudgetAmount],0)
FROM [SUPPL].[dbo].[CustomerBudget] Where (@Year between BudgetDateFrom and BudgetDateTo) and CUSTNMBR = @CustNMBR and [USCATVAL] = @USCATVAL

-- Return the result of the function
RETURN @ResultVar

END

ALTER FUNCTION [dbo].[_FnGetBudgetDepartment]
(
-- Add the parameters for the function here
@Year Date,
@CustNMBR varchar(100),
@USCATVAL varchar(100)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar varchar(max)
set @ResultVar = ''
-- Add the T-SQL statements to compute the return value here
SELECT @ResultVar = isnull( [Dept],'')
FROM [CustomerBudget] Where (@Year between BudgetDateFrom and BudgetDateTo) and CUSTNMBR = @CustNMBR and [USCATVAL] = @USCATVAL

-- Return the result of the function
RETURN @ResultVar

END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 10:01:54

That is not clear to me. Maybe I misunderstood...

When I encounter "Incon. Pro", I check the above table for the latest budget date-from which ALSO has a dept value, that is not NULL.

But the code is only looking apparently at the latest value. Currently I am getting Medical department but not incontinence.

Sorry but "Medical department" is the latest budget date-from which ALSO has a dept value, that is not NULL.

So what is your problem or what is your needed output?

No, you're never too old to Yak'n'Roll if you're too young to die.

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2010-04-22 : 16:19:32

Thank you Fred, this is more complicated than I had communicated and I apologize. the issue is more on the app side, in Silverlight. There it is selecting the first row rather than do a total on the budgets.
Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources