April 3, 2018 at 8:26 am
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!
April 3, 2018 at 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;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 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;
April 3, 2018 at 1:54 pm
sgmunson - Tuesday, April 3, 2018 9:04 AMTry 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!
April 3, 2018 at 1:58 pm
sgmunson - Tuesday, April 3, 2018 9:04 AMTry 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.
April 3, 2018 at 1:59 pm
Jason A. Long - Tuesday, April 3, 2018 9:42 AMYea... 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;
GOCREATE 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;
GONow 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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply