Home Forums SQL Server 2017 SQL Server 2017 - Development Very complex SQL Server logic to return multiple rows in a query even if there is only a single row of data, based on report date---will explain in post RE: Very complex SQL Server logic to return multiple rows in a query even if there is only a single row of data, based on report date---will explain in post

  • March 22, 2019 at 11:03 am

    #2025240

    drew.allen - Friday, March 22, 2019 9:06 AM

    Part of the problem is that your UDF is a scalar function (not a table-valued function) given the way that you are calling it.  I've substituted a Table Value Constructor for your function.
    PS: Scalar functions and table-valued functions are mutually exclusive.  You cannot have a scalar table-valued function.

    As Lynn mentioned, your sample data only covers one case.  I added a second case where the case had not yet been resolved.

    I believe that this gives you what you need.  It is very loosely based on work by Itzik Ben-Gan on packing intervals.

    WITH Case_Statuses AS
    (
        SELECT
            o.Case_ID
        ,    o.Contact_Date
        ,    o.Last_Updated_On AS Update_Status_Begin_Date
        ,    LEAD(o.Last_Updated_On, 1, COALESCE(r.Resolved_Date, '9999-12-31')) OVER(PARTITION BY o.Case_ID ORDER BY o.Last_Updated_On) AS Update_Status_End_Dt
        FROM #Open_Cases o
        LEFT OUTER JOIN #Resolved_Cases r
            ON o.Case_ID = r.Case_ID
    )

    SELECT *
    FROM Case_Statuses cs
    CROSS APPLY ( VALUES(1), (2), (3), (4), (5), (6) ) CalcAge(Age)
    CROSS APPLY ( VALUES(DATEADD(DAY, CalcAge.Age - 1, @Report_Start_Date) ) ) rd(Report_Date)
    WHERE cs.Update_Status_Begin_Date <= rd.Report_Date
        AND rd.Report_Date < cs.Update_Status_End_Dt
        AND rd.Report_Date BETWEEN @Report_Start_Date AND @Report_End_Date

    Drew

    Like the other post, the date selection range needs to be able to handle a dynamic date range...this date range will change, it is not going to remain the same and will vary by report and/or user entered values...is there a way to make the date range dynamic? (with the caveat it will skip over weekend and holidays and not include those---I can make a version of my calc age function that will return a date perhaps if it is not a weekend/holiday?)

    Drew, this solution works when I make the following change(minus the static date range):

      LEAD(o.Last_Updated_On, 1, COALESCE(r.Resolved_Date + 1, '9999-12-31')) OVER(PARTITION BY o.Case_ID ORDER BY o.Last_Updated_On) AS Update_Status_End_Dt

    EDIT: I fixed the below issue---I created the temp tables prior to running the code using SELECT... INTO
    (However, I am not sure what #Open_Cases o and #Resolved_Cases r refer to(is this a temp table)?  If so, it was throwing an error...when I changed it to the Open_Cases and Resolved_Cases(the actual physical tables), it created duplicate rows in there(that somehow now can't be deleted??), which is not what I want... Any advice on what to do with that issue? )

    Other than that it looks like the solution works properly...