Changing a User Defined Function into a View

  • 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

  • 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/

  • Thank you. I put together the tables now.

  • 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

  • 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/

  • Yes I do. I just want to apply this to all date ranges so I have a big list.

  • 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/

  • Hi. A count of total clients enrolled during the month.

    Total_Clients_Enrolled Month Year

    1 8 2009

    1 6 2009

    1 7 2009

  • 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/

  • 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.

  • 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/

  • 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

  • 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