• All 4 sections of Step 2 are identical except for the "ID =" thing. Why wouldn't you just do the following for the proc?

    create procedure [dbo].[usp_TEST1]

    @StudentUID int,

    @AgingDate datetime

    AS

    ---------------------------------------

    --Step 1:

    Create table #Part1

    (

    ID int identity(1,1),

    StudentUID int,

    PaymentPlanDetailUID int,

    PaymentDueDate datetime,

    [Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,

    MinAmountDue Decimal(15,2)

    )

    Insert Into #Part1

    Select Distinct

    StudentUID,

    PaymentPlanDetailUID,

    PaymentDueDate,

    [Description],

    sum(MinAmountDue) Over(Partition by PaymentDueDate) as Amount

    from [dbo].[ImportTable]

    Where

    StudentUID = @StudentUID

    Create table #Part2

    (

    PlanDetailUID int,

    PaymentDueDate datetime,

    [Description] nvarchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,

    Amount Decimal(15,2),

    Years int,

    Months int,

    [Days] int,

    MonthDuration int

    )

    ---------------------------------------

    --Step 2:

    Insert into #Part2

    Select Distinct

    StudentUID,

    PaymentDueDate,[Description],MinAmountDue,

    (SELECT Years FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Years,

    (SELECT Months FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS Months,

    (SELECT [Days] FROM dbo.ADV_rptage(PaymentDueDate,@AgingDate)) AS [Days],

    DATEDIFF(MONTH,PaymentDueDate,@AgingDate) AS MonthDuration

    FROM #Part1

    Where

    StudentUID = @StudentUID

    AND

    ID IN (1,2,3,4)

    SELECT * FROM #Part2

    GO

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)