SQL Server Function

[RayMilhon]

I need to create a function in SQL Server That will have 2 parameters: 1 is a guid the second is a date



The function will run a Select statement to return all records with the same guid and all dates after the date parameter.

The function then needs to compare those records to the original date and return a 1 if the new date is within 30 days of the date parameter and a 0 if it is not.

I've created functions before but not where I need to iterate through a recordset. I've done it in MS Access but the query that uses this function takes hours to complete. Since all of the data I need is in SQL Server I thought if I could create this function and create a view in SQL Server that uses it I could cut that time down considerably just not sure how to proceed with a recordset. Any help is appreciated.

[Perceptus]

Could you post your code for the custom function. Perhaps it has an issue there.

If you were using VB or SQL, i would implement use of the DateDiff Function.

[RayMilhon]

Don't have the code as yet that's what I'm trying to figure out.

But essentially it would look something similar to this However this is in VBA format not sql server

Function Readmits (ByRef mbr as String,vardate as date) As Integer
     Dim strsql as string, rs as new adodb.recordset
     strsql = "Select datefrom from dbo_claims where mbr_keyid = " & mbr & " and datefrom > #" & vardate & "#"
     rs.open strsql,currentproject.connection,adopenforwardonly,adlockreadonly
     Do until rs.eof
           If readmits = 0 then
                 If datediff("d",vardate,rs!datefrom) < 30 then Readmits = 1 Else Readmits = 0
           End if
          rs.movenext
    Loop
    rs.close
    set rs = nothing

[pbaldy]

I don't see a function being necessary. Are you trying to update the table, or just return a result with that field? I'm thinking a stored procedure with parameters, and a calculated field like:

CASE WHEN datediff(day,@vardate,datefrom) < 30 THEN 1 ELSE 0 END As Readmits

where @vardate is the parameter. When possible, you want to do set based operations, not looping (RBAR, row by agonizing row). Even if you want to update, it could be done with an update query and the same statement.

[RayMilhon]

What I posted is because it's a function the query that uses it is much more involved.

The primary query returns each Inpatient for our Medical Group to include
Date Addmitted
Date Discharged
Length of stay (which is calculated from the first 2 fields
PCP Information
Hospital Admitted To
Readmission
Admitting Doctor
Admitting Diagnosis

I have everything needed in my query with the exception of the Readmission. This function will be used to determine If a Readmission was necessary.

For example John Smith was Admitted to the hospital on April 1, 2015
He was Admitted again on May 15th.

So that would be No Readmission Necessary it exceeded 30 days

If he was Admitted on April 1, 2015 and then Admitted again on April 29th Then a Readmission was necessary.

[pbaldy]

I'm probably dense, but I still don't see a function. That said, you probably need a subquery or function to get the previous or next admitted date. Once you have the dates, the comparison is simple.

[ssanfu]

After looking at the function (which I reformatted for ease of understanding ...and I might have modified ), I can't get a grasp on how many records the record set could/would return.

Function Readmits(ByRef pMBR As String, pvarDate As Date) As Integer
    Dim strsql As String
    Dim rs As New adodb.Recordset
    
    'set default return value
    Readmits = 0
    
    strsql = "Select datefrom from dbo_claims where mbr_keyid = " & pMBR & " and datefrom > #" & pvarDate & "#"
    
    rs.Open strsql, CurrentProject.Connection, adopenforwardonly, adlockreadonly
    
    Do Until rs.EOF
        If Readmits = 0 Then
            If DateDiff("d", pvarDate, rs!datefrom) < 30 Then
                Readmits = 1
                Exit Do  ' exit loop the first time Readmits = 1
            Else
                Readmits = 0
            End If
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Function

If there could be multiple records spanning two months, does the order of the records matter? There is no sort order in the record set.
If there was a (descending) sort order, could I check the first record's date to see if the latest date was greater than or less than 30 days?