SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing a User Defined Function into a View


Changing a User Defined Function into a View

Author
Message
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Hi. I inherited a User Defined function I’m trying to modify and having some trouble. Crazy I would like to modify it into a regular query and then make it into a view. The function is ufn_B2H_Enrolled_Clients_List. This function pulls a list of clients enrolled in program during a time period selected. Within ufn_B2H_Enrolled_Clients_List is a function ufn_B2H_STATUSES_THROUGH_DT . This selects the maximum status change a client had during the time range selected so if a client was enrolled then disenrolled and then enrolled again the function is not function them into the count.

What I would like is to take ufn_B2H_Enrolled_Clients_List and turn it into a regular query/view:




**ufn_B2H_Enrolled_Clients_List Function**



CREATE FUNCTION [dbo].[ufn_B2H_Enrolled_Clients_List](@From_DT datetime, @To_DT datetime)
RETURNS @retB2H_Enrolled_Clients_List TABLE
(
CLT_NBR int
)
AS
BEGIN
INSERT INTO @retB2H_Enrolled_Clients_List
--Include all clients enrolled prior to the period end date


SELECT CLT_NBR
FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1
WHERE s1.B2H_STATUS=4 AND s1.Max_Effect_DT <DATEADD(d,1,@To_DT)
--Exclude all clients disenrolled or transferred out prior to the start date
EXCEPT
SELECT s2.CLT_NBR
FROM ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s2 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s2.CLT_NBR
WHERE s2.B2H_STATUS IN (7,9) AND s2.Max_Effect_DT <@From_DT AND s2.Max_Effect_DT>s1.Max_Effect_DT AND s1.B2H_STATUS IN (4,8)
--Exclude all clients who transferred in after the prior end date.
EXCEPT
SELECT s3.CLT_NBR
FROM ECMS.dbo.B2H_STATUS s3 JOIN ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT(@To_DT) s1 ON s1.CLT_NBR=s3.CLT_NBR
WHERE s3.B2H_STATUS = 8 AND s1.B2H_STATUS IN (4,8) AND s3.EFFECT_DT > @To_DT AND s3.Effect_DT>s1.Max_Effect_DT

RETURN
END

GO

CREATE FUNCTION [dbo].[ufn_B2H_STATUSES_THROUGH_DT](@Through_DT datetime)
RETURNS @retB2H_STATUSES_THROUGH_DT TABLE
(
CLT_NBR int
,B2H_STATUS int
,Max_EFFECT_DT datetime
)
AS
BEGIN
INSERT INTO @retB2H_STATUSES_THROUGH_DT
S


ELECT CLT_NBR
,B2H_STATUS
,MAX(EFFECT_DT) AS Max_Effect_DT
FROM [ECMS].[dbo].[B2H_STATUS]
WHERE EFFECT_DT<DATEADD(d,1,@Through_DT)
GROUP BY CLT_NBR, B2H_STATUS

RETURN
END

GO



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
Rick44 (8/16/2013)
Hi. I inherited a User Defined function I’m trying to modify and having some trouble. Crazy I would like to modify it into a regular query and then make it into a view. The function is ufn_B2H_Enrolled_Clients_List. This function pulls a list of clients enrolled in program during a time period selected. Within ufn_B2H_Enrolled_Clients_List is a function ufn_B2H_STATUSES_THROUGH_DT . This selects the maximum status change a client had during the time range selected so if a client was enrolled then disenrolled and then enrolled again the function is not function them into the count.

What I would like is to take ufn_B2H_Enrolled_Clients_List and turn it into a regular query/view with an output that looks like this:


Just curious, why do you want to take this out your functions? They both look like a decent iTFV. If you are trying to get some performance gains I don't know that this will help much.

The biggest problem we have here is that all we have to work with is a couple of queries. We don't know what these tables look like or what the objective is. If you want actual coding help you will need to post a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Thank you. I put together the tables now.
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Hi. I want to get the count of clients enrolled grouped by month and year. Having the user defined function as a view will make it easier to report on for me and having this is query form will give me a better understanding of the function:
The output I want to achieve Should look like this:


Total_Clients_Enrolled Month Year
252 1 2013
247 2 2013
303 3 2013




Here is an example of the table which is used:




CREATE TABLE B2H_STATUS (
CLT_NBR int,
EFFECT_DT datetime
B2H_STATUS int
)
INSERT INTO CLT_NBR EFFECT_DT B2H_STATUS VALUES
('14082', '4/27/2009', 1),
('14082', '7/14/2009', 2),
('14082', '8/10/2009', 3),
('14082', '8/12/2009', 4),
('9164', '3/5/2009', 1),
('8990', '3/5/2009', 1),
('8990', '5/8/2009', 2),
('8990', '6/8/2009', 3),
('8990', '6/12/2009', 4),
('13991', '4/2/2009', 1),
('13991', '5/21/2009', 2),
('13991', '6/9/2009', 3),
('13991', '7/13/2009', 4)





the ECMS.dbo.ufn_B2H_STATUSES_THROUGH_DT brings back this



CLT_NBR B2H_STATUS Max_Effect_DT
14082 4 8/12/2009
8990 4 6/12/2009
13991 4 7/13/2009



So the output I'm looking for would look like this:

Total_Clients_Enrolled Month Year
1 8 2009
1 6 2009
1 7 2009


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
You do know that you can't pass parameters to a view right?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Yes I do. I just want to apply this to all date ranges so I have a big list.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
OK so what would be the expected output based on the small sample of data you provided?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Hi. A count of total clients enrolled during the month.





Total_Clients_Enrolled Month Year
1 8 2009
1 6 2009
1 7 2009



Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
Rick44 (8/16/2013)
Hi. A count of total clients enrolled during the month.





Total_Clients_Enrolled Month Year
1 8 2009
1 6 2009
1 7 2009




I seem to be missing something. Why only 3 months? And why are the counts all 1? There seems to be more rows with those months and there are certainly more dates than those three. Basically your view will have to be the aggregate data you want for every month and then you will filter it down with a where clause. If you can help me understand your requirements I will jump in and help...right after the weekend to which I am headed right now. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Rick44
Rick44
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 107
Hi. That was just a small example. Yes, there could be years worth of counts. many more that 1 enrolled. Do you want me to send you larger sample data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search