Trying to assign payment amounts for each consecutive month within a date range

• shel 29143

  Mr or Mrs. 500

  Points: 502

  More actions

  April 3, 2018 at 8:26 am

  #330246

  I have authorizations that have start and end dates and I need to calulate the monthly payment and assign to each month within the date range to get the estimated value of the auth in an aggregate.   For example, I have an auth that started on 8/1/2017 and ends on 1/31/2018 which is 6 months in duration. The total value of the auth is $3559.50 so per month it is $593.25 so I need to somehow in my aggregate assign $593.25 to each month from August to January.
   I'm looking for this in results.
  Month member amount
  8/1/2017 12345 593.25
  9/1/2017 12345 593.25
  10/1/2017 12345 593.25
  11/1/2017 12345 593.25
  12/1/2017 12345 593.25
  1/1/2018 12345 593.25

  Here is my sample data:
  create table #temp
(month date,
memberId varchar(5),
auth_datefrom date,
auth_dateto date,
authmonths int,
est_monthly_payment money)

  INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','12345','8/1/2017','9/30/2017','2','762.75');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','67890','8/1/2017','9/30/2017','2','2440.8');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','23456','8/1/2017','6/30/2018','11','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','34567','8/1/2017','8/31/2017','1','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','45678','8/1/2017','8/31/2017','1','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','56789','8/1/2017','11/1/2017','3','996.66');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','67890','8/1/2017','6/30/2018','11','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','78901','8/1/2017','8/31/2017','1','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','89012','8/1/2017','8/31/2017','1','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','90123','8/1/2017','2/28/2018','7','813.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment) VALUES ('8/1/2017','1234','8/1/2017','6/30/2018','11','443.78');

  A member on another forum posted this to help me out which works except I need the date the counter starts to be dynamic based on each auth.  Right now it is set to 8/1/2017 and calculating all auths with a start date of 8/1.  Does anyone have any ideas how to help?  I tried using from #temp in the first select statement but that takes the auth dates from the first row and assigns those values to all the auths.  I think I need to specify which auth to get the data from but I'm not sure how to do it.

  CREATE TABLE #DateTable (Dates DATETIME, ID INT IDENTITY )

  DECLARE @Counter INT
SET @Counter = 0

  WHILE @Counter < 481

  BEGIN
INSERT INTO #DateTable
SELECT DATEADD (MONTH, @Counter, '8/1/2017')
SET @Counter = @Counter + 1

  END

  SELECT D.*, T.member_Id, T.auth_datefrom, T.auth_dateto, T.authmonths,
T.est_monthly_payment
FROM #DateTable AS D
CROSS JOIN #temp AS T
WHERE D.ID <= T.authmonths

  THANKS FOR ANY HELP OR INSIGHT!

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  April 3, 2018 at 9:04 am

  #1985307

  Try this on for size.  I'm assuming that the estimated monthly payment in your temp table was already calculated as the payment and that I didn't need to compute the payment amount, despite your including that calculation as part of what you are describing, and I made that assumption because of the column name.   If that's incorrect, let me know.
  CREATE TABLE #temp (
    [month] date,
    memberId varchar(5),
    auth_datefrom date,
    auth_dateto date,
    authmonths int,
    est_monthly_payment money
);
INSERT INTO #temp (month,memberId,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment)
    VALUES    ('8/1/2017','12345','8/1/2017','9/30/2017','2','762.75'),
            ('8/1/2017','67890','8/1/2017','9/30/2017','2','2440.8'),
            ('8/1/2017','23456','8/1/2017','6/30/2018','11','443.78'),
            ('8/1/2017','34567','8/1/2017','8/31/2017','1','1708.56'),
            ('8/1/2017','45678','8/1/2017','8/31/2017','1','4881.6'),
            ('8/1/2017','56789','8/1/2017','11/1/2017','3','996.66'),
            ('8/1/2017','67890','8/1/2017','6/30/2018','11','443.78'),
            ('8/1/2017','78901','8/1/2017','8/31/2017','1','1708.56'),
            ('8/1/2017','89012','8/1/2017','8/31/2017','1','4881.6'),
            ('8/1/2017','90123','8/1/2017','2/28/2018','7','813.6'),
            ('8/1/2017','1234','8/1/2017','6/30/2018','11','443.78');

  WITH Numbers AS (

      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
    SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
    SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N
),
    DateRanges AS (

          SELECT DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20170801') AS MonthStart,
            DATEADD(day, -1, DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20170801')) AS MonthEnd
        FROM Numbers AS N1
            CROSS APPLY Numbers AS N2
)
SELECT T.memberId, R.MonthStart AS [Month], T.est_monthly_payment,
    ROW_NUMBER() OVER(PARTITION BY T.memberId ORDER BY R.MonthStart) AS PaymentNumber
FROM #temp AS T
    CROSS APPLY DateRanges AS R
WHERE R.MonthStart >= T.auth_datefrom
    AND R.MonthEnd <= T.auth_dateto
ORDER BY T.memberId, R.MonthStart;

  DROP TABLE #temp;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  April 3, 2018 at 9:42 am

  #1985320

  Yea... There's no reason to use loops or cursors to do something like this. The performance of that would be horrible.

  Your best best is to simply create an inline table valued function to handle this. 
  Something like this should work well for you...

  SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

  CREATE FUNCTION dbo.SplitAuthAmtByPeriod
/* ===================================================================
04/03/2018 JL, Created: Splits the auth amt by the number of periods
                between begin & end dates of the auth.    
=================================================================== */
(
    @_beg_date DATE,
    @_end_date DATE,
    @_auth_amt MONEY
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
        cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
        cte_month ([month]) AS (
            SELECT TOP (DATEDIFF(MONTH, @_beg_date, DATEADD(MONTH, 1, @_end_date)))
                DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @_beg_date)
            FROM
                cte_n3 a CROSS JOIN cte_n3 b
            )
    SELECT
        m.[month],
        divided_auth_amt = @_auth_amt / DATEDIFF(MONTH, @_beg_date, DATEADD(MONTH, 1, @_end_date))
    FROM
        cte_month m;
GO

  Now your query is as simple as the following...

  IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
BEGIN DROP TABLE #temp; END;

  CREATE table #temp (
    [month] date,
    member_Id varchar(5),
    auth_datefrom date,
    auth_dateto date,
    auth_amt MONEY
    )

  INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','12345','8/1/2017','9/30/2017','762.75');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','67890','8/1/2017','9/30/2017','2440.8');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','23456','8/1/2017','6/30/2018','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','34567','8/1/2017','8/31/2017','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','45678','8/1/2017','8/31/2017','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','56789','8/1/2017','11/1/2017','996.66');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','67890','8/1/2017','6/30/2018','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','78901','8/1/2017','8/31/2017','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','89012','8/1/2017','8/31/2017','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','90123','8/1/2017','2/28/2018','813.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','1234','8/1/2017','6/30/2018', '443.78');

  /* the actual query */

  SELECT
    t.*,
    sap.month,
    monthly_amt = sap.divided_auth_amt
FROM
    #temp t
    CROSS APPLY dbo.SplitAuthAmtByPeriod(t.auth_datefrom, t.auth_dateto, t.auth_amt) sap;

• shel 29143

  Mr or Mrs. 500

  Points: 502

  More actions

  April 3, 2018 at 1:54 pm

  #1985372

  sgmunson - Tuesday, April 3, 2018 9:04 AM

  Try this on for size.  I'm assuming that the estimated monthly payment in your temp table was already calculated as the payment and that I didn't need to compute the payment amount, despite your including that calculation as part of what you are describing, and I made that assumption because of the column name.   If that's incorrect, let me know.
  CREATE TABLE #temp (
      [month] date,
      memberId varchar(5),
      auth_datefrom date,
      auth_dateto date,
      authmonths int,
      est_monthly_payment money
  );
  INSERT INTO #temp (month,memberId,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment)
      VALUES    ('8/1/2017','12345','8/1/2017','9/30/2017','2','762.75'),
              ('8/1/2017','67890','8/1/2017','9/30/2017','2','2440.8'),
              ('8/1/2017','23456','8/1/2017','6/30/2018','11','443.78'),
              ('8/1/2017','34567','8/1/2017','8/31/2017','1','1708.56'),
              ('8/1/2017','45678','8/1/2017','8/31/2017','1','4881.6'),
              ('8/1/2017','56789','8/1/2017','11/1/2017','3','996.66'),
              ('8/1/2017','67890','8/1/2017','6/30/2018','11','443.78'),
              ('8/1/2017','78901','8/1/2017','8/31/2017','1','1708.56'),
              ('8/1/2017','89012','8/1/2017','8/31/2017','1','4881.6'),
              ('8/1/2017','90123','8/1/2017','2/28/2018','7','813.6'),
              ('8/1/2017','1234','8/1/2017','6/30/2018','11','443.78');

  WITH Numbers AS (

      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N
  ),
      DateRanges AS (

          SELECT DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20170801') AS MonthStart,
              DATEADD(day, -1, DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20170801')) AS MonthEnd
          FROM Numbers AS N1
              CROSS APPLY Numbers AS N2
  )
  SELECT T.memberId, R.MonthStart AS [Month], T.est_monthly_payment,
      ROW_NUMBER() OVER(PARTITION BY T.memberId ORDER BY R.MonthStart) AS PaymentNumber
  FROM #temp AS T
      CROSS APPLY DateRanges AS R
  WHERE R.MonthStart >= T.auth_datefrom
      AND R.MonthEnd <= T.auth_dateto
  ORDER BY T.memberId, R.MonthStart;

  DROP TABLE #temp;

  This worked beautifully!  Thank you so much!

• shel 29143

  Mr or Mrs. 500

  Points: 502

  More actions

  April 3, 2018 at 1:58 pm

  #1985373

  sgmunson - Tuesday, April 3, 2018 9:04 AM

  Try this on for size.  I'm assuming that the estimated monthly payment in your temp table was already calculated as the payment and that I didn't need to compute the payment amount, despite your including that calculation as part of what you are describing, and I made that assumption because of the column name.   If that's incorrect, let me know.
  CREATE TABLE #temp (
      [month] date,
      memberId varchar(5),
      auth_datefrom date,
      auth_dateto date,
      authmonths int,
      est_monthly_payment money
  );
  INSERT INTO #temp (month,memberId,auth_datefrom,auth_dateto,authmonths,Est_Monthly_Payment)
      VALUES    ('8/1/2017','12345','8/1/2017','9/30/2017','2','762.75'),
              ('8/1/2017','67890','8/1/2017','9/30/2017','2','2440.8'),
              ('8/1/2017','23456','8/1/2017','6/30/2018','11','443.78'),
              ('8/1/2017','34567','8/1/2017','8/31/2017','1','1708.56'),
              ('8/1/2017','45678','8/1/2017','8/31/2017','1','4881.6'),
              ('8/1/2017','56789','8/1/2017','11/1/2017','3','996.66'),
              ('8/1/2017','67890','8/1/2017','6/30/2018','11','443.78'),
              ('8/1/2017','78901','8/1/2017','8/31/2017','1','1708.56'),
              ('8/1/2017','89012','8/1/2017','8/31/2017','1','4881.6'),
              ('8/1/2017','90123','8/1/2017','2/28/2018','7','813.6'),
              ('8/1/2017','1234','8/1/2017','6/30/2018','11','443.78');

  WITH Numbers AS (

      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL
      SELECT 1 AS N UNION ALL SELECT 1 AS N UNION ALL SELECT 1 AS N
  ),
      DateRanges AS (

          SELECT DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20170801') AS MonthStart,
              DATEADD(day, -1, DATEADD(month, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20170801')) AS MonthEnd
          FROM Numbers AS N1
              CROSS APPLY Numbers AS N2
  )
  SELECT T.memberId, R.MonthStart AS [Month], T.est_monthly_payment,
      ROW_NUMBER() OVER(PARTITION BY T.memberId ORDER BY R.MonthStart) AS PaymentNumber
  FROM #temp AS T
      CROSS APPLY DateRanges AS R
  WHERE R.MonthStart >= T.auth_datefrom
      AND R.MonthEnd <= T.auth_dateto
  ORDER BY T.memberId, R.MonthStart;

  DROP TABLE #temp;

  @sgmunson
  Thanks so much for the help and the fast reply.  Worked beautifully and exactly how I needed it to.

• shel 29143

  Mr or Mrs. 500

  Points: 502

  More actions

  April 3, 2018 at 1:59 pm

  #1985374

  Jason A. Long - Tuesday, April 3, 2018 9:42 AM

  Yea... There's no reason to use loops or cursors to do something like this. The performance of that would be horrible.

  Your best best is to simply create an inline table valued function to handle this. 
  Something like this should work well for you...

  SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

  CREATE FUNCTION dbo.SplitAuthAmtByPeriod
/* ===================================================================
04/03/2018 JL, Created: Splits the auth amt by the number of periods
                between begin & end dates of the auth.    
=================================================================== */
(
    @_beg_date DATE,
    @_end_date DATE,
    @_auth_amt MONEY
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
        cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
        cte_month ([month]) AS (
            SELECT TOP (DATEDIFF(MONTH, @_beg_date, DATEADD(MONTH, 1, @_end_date)))
                DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, @_beg_date)
            FROM
                cte_n3 a CROSS JOIN cte_n3 b
            )
    SELECT
        m.[month],
        divided_auth_amt = @_auth_amt / DATEDIFF(MONTH, @_beg_date, DATEADD(MONTH, 1, @_end_date))
    FROM
        cte_month m;
GO

  Now your query is as simple as the following...

  IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
BEGIN DROP TABLE #temp; END;

  CREATE table #temp (
    [month] date,
    member_Id varchar(5),
    auth_datefrom date,
    auth_dateto date,
    auth_amt MONEY
    )

  INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','12345','8/1/2017','9/30/2017','762.75');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','67890','8/1/2017','9/30/2017','2440.8');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','23456','8/1/2017','6/30/2018','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','34567','8/1/2017','8/31/2017','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','45678','8/1/2017','8/31/2017','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','56789','8/1/2017','11/1/2017','996.66');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','67890','8/1/2017','6/30/2018','443.78');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','78901','8/1/2017','8/31/2017','1708.56');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','89012','8/1/2017','8/31/2017','4881.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','90123','8/1/2017','2/28/2018','813.6');
INSERT INTO #temp (Month,member_id,auth_datefrom,auth_dateto,auth_amt) VALUES ('8/1/2017','1234','8/1/2017','6/30/2018', '443.78');

  /* the actual query */

  SELECT
    t.*,
    sap.month,
    monthly_amt = sap.divided_auth_amt
FROM
    #temp t
    CROSS APPLY dbo.SplitAuthAmtByPeriod(t.auth_datefrom, t.auth_dateto, t.auth_amt) sap;

  Thanks so much for the help Jason, I was able to get it to work using the first response prior to receiving your post.