August 16, 2013 at 1:22 pm
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
August 16, 2013 at 1:42 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2013 at 1:53 pm
Thank you. I put together the tables now.
August 16, 2013 at 2:16 pm
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_NBRB2H_STATUSMax_Effect_DT
140824 8/12/2009
8990 4 6/12/2009
139914 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
August 16, 2013 at 2:46 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2013 at 2:52 pm
Yes I do. I just want to apply this to all date ranges so I have a big list.
August 16, 2013 at 3:09 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2013 at 3:17 pm
Hi. A count of total clients enrolled during the month.
Total_Clients_Enrolled Month Year
1 8 2009
1 6 2009
1 7 2009
August 16, 2013 at 3:28 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2013 at 3:31 pm
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.
August 19, 2013 at 7:53 am
Rick44 (8/16/2013)
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.
The small sample is fine. What I need it to understand the business rules. What would you expect as output based on the sample data you provided and how do you come up with the counts and such?
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 19, 2013 at 10:50 am
Hi. Thank you for getting back to me. 😀
I need to pull a count of clients enrolled in a program by month. CLT_NBR is a unique ID for each client.
•I want the total count of clients that were enrolled at the end of the month.
•So include all clients enrolled prior to the period end date. I am using there Max(EFFECT_DT) during the month to determine the count at the end of the month. The EFFECT_DT gives the status change dates. SO the Max(Effect_DT) is the last status change the client had during the month.
•I need exclude all clients disenrolled or transferred out prior to the start date and Exclude all clients who transferred in after the prior end date
•The B2H_Status variable holds the codes for the actually different types of status changes.
o 4=Enrolled
o 7= Disenrolled
o 8= Transfer In
o 9= Transfer Out
If the Period date range selected is 1/01/2013 to 3/01/2013
Total_Clients_Enrolled_Count Month Year
252 Jan 2013
247 Feb 2013
303 March 2013
August 19, 2013 at 11:30 am
What I need it to understand the business rules. What would you expect as output based on the sample data you provided and how do you come up with the counts and such?
I hope my response is what you are looking for. Thanks again,
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply