Changing a function to work with multiple records

Question

Changing a function to work with multiple records

NHS Baz

SSC Rookie

Points: 30
More actions
June 28, 2016 at 3:36 pm

#311310

I have a function that I use to compute the number of days that a patient is unavailable in order to reduce the number of days they have breached a waiting times deadline.
The function is as follows:
CREATE FUNCTION [dbo].[GetDaysUnavailable]
(
@intRefint
)
RETURNS int
AS
BEGIN
DECLARE @ReturnValueint
DECLARE @dtUnavailFromdatetime
DECLARE @dtUnavailTodatetime
DECLARE @dtRefRcvddatetime
SELECT @dtUnavailFrom = UnavailableFrom FROM Referrals WHERE ReferralID = @intRef
SELECT @dtUnavailTo = UnavailableTo FROM Referrals WHERE ReferralID = @intRef
SELECT @dtRefRcvd = ReferralReceivedDate FROM Referrals WHERE ReferralID = @intRef
IF ISNULL(@dtUnavailFrom,'') = '' OR ISNULL(@dtUnavailTo,'') = ''
BEGIN
SELECT @ReturnValue = 0
END
ELSE
BEGIN
IF @dtRefRcvd > @dtUnavailFrom
BEGIN
SELECT @ReturnValue = DATEDIFF(day,@dtRefRcvd, @dtUnavailTo)
END
ELSE
BEGIN
SELECT @ReturnValue = DATEDIFF(day,@dtUnavailFrom, @dtUnavailTo)
END
END
RETURN @ReturnValue
END
As you can see it works out the difference between the date that the person is unavailable to and either the date they are unavailable from or the date their referral was received. This was fine using my original structure whereby the 2 date fields were in the main table. The problem with this was that there could only ever be 1 period of unavailability.
I have now changed the table structure so that the unavailability periods are in their own table which has a 1-to-many relationship with the main table.
But now I can't figure out how to get my function to cope withe new structure. I basically need to be able to pull every row in the unavailability table that relates to the parent record and them loop through each row, summing up the unavailability and returning the total at the end.
Any ideas?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) Is there a question here?

2) ALWAYS use WITH SCHEMABINDING when using UDFs, views and any dang thing else you can use it with! Build a box around the user, and in this case the user is all developers. Oh, it also prevents a nasty table spool for Halloween protection on UPDATEs.

3) NEVER EVER EVER use Scalar (that touch data) or Multi-statement Table Valued Functions if you can help it!!! They are HORRIBLY HORRIBLY BAD!! Inline the logic, or convert to an Inline Table Valued Function.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

NHS Baz SSC Rookie Points: 30 More actions · Answer 2

1. Sorry, I jumped the gun with my post. The full question is now there.

2. What do you mean by build a box around the user and what is Halloween protection? I'm not performing any UPDATE in the function so I fail to see the relevance.

3. What do you mean by inline the logic or convert to an inline table valued function? My function only returns a single integer value.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 3

NHS Baz (6/28/2016)
3. What do you mean by inline the logic or convert to an inline table valued function? My function only returns a single integer value.

Please see the following article where that question is answered and demonstrated.

[font="Arial Black"]How to Make Scalar UDFs Run Faster (SQL Spackle)[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 4

NHS Baz (6/28/2016)
2. What do you mean by build a box around the user and what is Halloween protection? I'm not performing any UPDATE in the function so I fail to see the relevance.

In many cases, especially when a function references a table, using WITH SCHEMABINDING frequently eliminates an extra "Spool", whether an UPDATE occurs or not. From what I've seen, that's not so true with iTFVs (inline Table Valued Functions) but others may have observed otherwise. A fellow by the name of Paul White wrote an article on the subject and can be found at the following URL.

http://sqlperformance.com/2013/02/sql-plan/halloween-problem-part-4

As for me, I almost always never say always, if I can help it, because there are almost always exceptions.:-P

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

As for me, I almost always never say always, if I can help it, because there are almost always exceptions.:-P

Which is why I said "if you can help it" about never. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

TheSQLGuru (6/28/2016)
As for me, I almost always never say always, if I can help it, because there are almost always exceptions.:-P
Which is why I said "if you can help it" about never. 😎

😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NHS Baz SSC Rookie Points: 30 More actions · Answer 7

Thanks for the input folks but does anyone have an answer to the question that was actaully posed in the original post?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

NHS Baz (6/29/2016)
Thanks for the input folks but does anyone have an answer to the question that was actaully posed in the original post?

Think set-based 😉

CREATE FUNCTION [dbo].[GetDaysUnavailable]

(

@intRefint

)

RETURNS TABLE

AS

RETURN

SELECT SUM(CASE WHEN ReferralReceivedDate > UnavailableFrom

THEN DATEDIFF(day,ReferralReceivedDate, UnavailableTo)

ELSE DATEDIFF(day,UnavailableFrom, UnavailableTo) END) AS ReturnValue

FROM Referrals

WHERE ReferralID = @intRef;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

NHS Baz (6/29/2016)
Thanks for the input folks but does anyone have an answer to the question that was actaully posed in the original post?

It would be nice to have some sample data and expected output so we can test possible solutions.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service