April 27, 2015 at 11:44 am
I am hoping someone can assist me with a dilemma; I am trying to exclude records that have an assessed value that has been waived in an aggregation. For Example:
Here is my table:
CREATE TABLE #temptable (ReportingMonth Varchar(6), Fee_Code Varchar(20), Fee_Transaction_Amount Decimal(12,2), Fee_Transaction_Date Datetime, Fee_Transaction_Type Char)
INSERT INTO #temptable (ReportingMonth, Fee_Code, Fee_Transaction_Amount, Fee_Transaction_Date, Fee_Transaction_Type)
SELECT 'Jan-13', 'ONE TIME DRAFT FEE', '20', '01/24/2013', 'A'
UNION ALL SELECT 'Feb-13', 'LATE CHARGE', '33.6', '02/19/2013', 'A'
UNION ALL SELECT 'Mar-13', 'LATE CHARGE', '37.01', '03/18/2013', 'A'
UNION ALL SELECT 'Mar-13', 'ONE TIME DRAFT FEE', '20', '03/26/2013', 'A'
UNION ALL SELECT 'Apr-13', 'LATE CHARGE', '37.01', '04/16/2013', 'A'
UNION ALL SELECT 'Apr-13', 'ONE TIME DRAFT FEE', '20', '04/19/2013', 'A'
UNION ALL SELECT 'May-13', 'RETURN CHECK FEE', '10', '05/06/2013', 'A'
UNION ALL SELECT 'May-13', 'LATE CHARGE', '37.01', '05/16/2013', 'A'
UNION ALL SELECT 'Jun-13', 'LATE CHARGE', '37.01', '06/17/2013', 'A'
UNION ALL SELECT 'Jun-13', 'ONE TIME DRAFT FEE', '20', '06/27/2013', 'A'
UNION ALL SELECT 'Jul-13', 'LATE CHARGE', '37.01', '07/16/2013', 'A'
UNION ALL SELECT 'Jul-13', 'ONE TIME DRAFT FEE', '20', '07/29/2013', 'A'
UNION ALL SELECT 'Aug-13', 'LATE CHARGE', '37.01', '08/16/2013', 'A'
UNION ALL SELECT 'Aug-13', 'ONE TIME DRAFT FEE', '20', '08/30/2013', 'A'
UNION ALL SELECT 'Sep-13', 'LATE CHARGE', '37.01', '09/16/2013', 'A'
UNION ALL SELECT 'Sep-13', 'ONE TIME DRAFT FEE', '20', '09/30/2013', 'A'
UNION ALL SELECT 'Oct-13', 'LATE CHARGE', '37.01', '10/16/2013', 'A'
UNION ALL SELECT 'Oct-13', 'ONE TIME DRAFT FEE', '20', '10/31/2013', 'A'
UNION ALL SELECT 'Dec-13', 'ONE TIME DRAFT FEE', '20', '12/05/2013', 'A'
UNION ALL SELECT 'Apr-14', 'ONE TIME DRAFT FEE', '-80', '04/01/2014', 'W'
SELECT * FROM #temptable
Here are Data Mapping Description
Reporting Month = Month - Year
Fee Code = Fee Description Name
Fee Transaction Amount = Fee Amount
Fee Transaction Date = When Fee Amount was Applied
Fee Transaction Type = "A" = Assessed Fee; "W" = Waived Fee; "P" = Paid Fee
I've also included an image with beginning data set and what I want to identify in red and what my final data set should look like after the exclusion of those 4 records are removed.
Here are the logic requirements:
In the attachment what I need the logic to do is essentially identify the $20 One Time Draft Fee from the first instance using the MIN Transaction date. Since there were $80 waived for this fee code (One Time Draft Fee), I would expect to see the first 4 (highlighted in red) to be identified as the target and as you can see in the attachment the second data set had the 4 highlighted items removed. That should be my final output.
I'm just having a hard to in trying to loop and remove the waive amounts from the assess amounts and tied it back to remove from my base data. Any information or direction anyone can provide will be greatly appreciated! This logic is making my brain hurt :w00t:
April 29, 2015 at 9:34 am
I believe I finally figured out on my own the best approach on getting my desired results. I first thought that a While Loop syntax would be the appropriate direction I needed to take to resolve my coding dilemma until I learned on my own about Cumulative Aggregation. It worked best to identify the cumulative totals in the transaction amounts to then case out what fits in the waived amount and then ultimately remove from data result set.
So for those that have a similar issues; here is my logic:
CREATE TABLE #temptable
(
reportingmonth VARCHAR(6),
fee_code VARCHAR(20),
fee_transaction_amount DECIMAL(12, 2),
fee_transaction_date DATETIME,
fee_transaction_type CHAR
)
INSERT INTO #temptable
(reportingmonth,
fee_code,
fee_transaction_amount,
fee_transaction_date,
fee_transaction_type)
SELECT 'Jan-13', 'ONE TIME DRAFT FEE', '20', '01/24/2013', 'A'
UNION ALL SELECT 'Feb-13', 'LATE CHARGE', '33.6', '02/19/2013', 'A'
UNION ALL SELECT 'Mar-13', 'LATE CHARGE', '37.01', '03/18/2013', 'A'
UNION ALL SELECT 'Mar-13', 'ONE TIME DRAFT FEE', '20', '03/26/2013', 'A'
UNION ALL SELECT 'Apr-13', 'LATE CHARGE', '37.01', '04/16/2013', 'A'
UNION ALL SELECT 'Apr-13', 'ONE TIME DRAFT FEE', '20', '04/19/2013', 'A'
UNION ALL SELECT 'May-13', 'RETURN CHECK FEE', '10', '05/06/2013', 'A'
UNION ALL SELECT 'May-13', 'LATE CHARGE', '37.01', '05/16/2013', 'A'
UNION ALL SELECT 'Jun-13', 'LATE CHARGE', '37.01', '06/17/2013', 'A'
UNION ALL SELECT 'Jun-13', 'ONE TIME DRAFT FEE', '20', '06/27/2013', 'A'
UNION ALL SELECT 'Jul-13', 'LATE CHARGE', '37.01', '07/16/2013', 'A'
UNION ALL SELECT 'Jul-13', 'ONE TIME DRAFT FEE', '20', '07/29/2013', 'A'
UNION ALL SELECT 'Aug-13', 'LATE CHARGE', '37.01', '08/16/2013', 'A'
UNION ALL SELECT 'Aug-13', 'ONE TIME DRAFT FEE', '20', '08/30/2013', 'A'
UNION ALL SELECT 'Sep-13', 'LATE CHARGE', '37.01', '09/16/2013', 'A'
UNION ALL SELECT 'Sep-13', 'ONE TIME DRAFT FEE', '20', '09/30/2013', 'A'
UNION ALL SELECT 'Oct-13', 'LATE CHARGE', '37.01', '10/16/2013', 'A'
UNION ALL SELECT 'Oct-13', 'ONE TIME DRAFT FEE', '20', '10/31/2013', 'A'
UNION ALL SELECT 'Dec-13', 'ONE TIME DRAFT FEE', '20', '12/05/2013', 'A'
UNION ALL SELECT 'Apr-14', 'ONE TIME DRAFT FEE', '-80', '04/01/2014', 'W'
SELECT *
FROM #temptable
SELECT t1.reportingmonth,
t1.fee_code,
t1.fee_transaction_type,
t1.fee_transaction_amount,
Sum(t2.fee_transaction_amount) AS cumulative_tran_amount,
waiver_flag = CASE
WHEN t1.fee_code = w.fee_code
AND Sum(t2.fee_transaction_amount) <= w.fee_transaction_amount *- 1 THEN 'Y'
ELSE 'N'
END,
t1.fee_transaction_date
FROM #temptable t1
INNER JOIN #temptable t2
ON t1.fee_transaction_date >= t2.fee_transaction_date
AND t1.fee_code = t2.fee_code
AND t2.fee_transaction_type = 'A'
LEFT JOIN #temptable w
ON t1.fee_code = w.fee_code
AND w.fee_transaction_type = 'W'
WHERE t1.fee_transaction_type = 'A'
GROUP BY t1.reportingmonth,
t1.fee_code,
t1.fee_transaction_type,
t1.fee_transaction_amount,
t1.fee_transaction_date,
w.fee_code,
w.fee_transaction_amount
HAVING CASE
WHEN t1.fee_code = w.fee_code
AND Sum(t2.fee_transaction_amount) <= w.fee_transaction_amount *- 1 THEN 'Y'
ELSE 'N'
END = 'N'
ORDER BY t1.fee_code,
Sum(t2.fee_transaction_amount)
I am glad to put this to rest and move on to my next task! 😀 I'm sure there are other ways to efficiently re-work this logic so feel free to share.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply