Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing a User Defined Function into a View Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 1:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
Hi. I inherited a User Defined function I’m trying to modify and having some trouble. 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


Post #1485357
Posted Friday, August 16, 2013 1:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
Rick44 (8/16/2013)
Hi. I inherited a User Defined function I’m trying to modify and having some trouble. 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 Moden's 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)
Post #1485360
Posted Friday, August 16, 2013 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
Thank you. I put together the tables now.
Post #1485365
Posted Friday, August 16, 2013 2:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
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

Post #1485374
Posted Friday, August 16, 2013 2:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1485379
Posted Friday, August 16, 2013 2:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
Yes I do. I just want to apply this to all date ranges so I have a big list.
Post #1485380
Posted Friday, August 16, 2013 3:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's 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)
Post #1485384
Posted Friday, August 16, 2013 3:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
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


Post #1485387
Posted Friday, August 16, 2013 3:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
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.


_______________________________________________________________

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 Moden's 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)
Post #1485389
Posted Friday, August 16, 2013 3:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
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.
Post #1485391
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse