April 24, 2015 at 10:05 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:
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
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
So I need to take the -80 figure with the Fee Tran Type = "W" for "ONE TIME DRAFT FEE" and apply it the all the "A" (Assessed) Fee Transaction Amount from the min Fee Transaction Date of each "ONE TIME DRAFT FEE" until it exhausted the full -80 waiver. Then need to remove these records from my output and reflect the rest. I was thinking a While Loop type of query, but I am having a hard time compiling it together. Is there anyone that can provide some assistance?
April 24, 2015 at 10:13 am
We can certainly help but we need a little bit more information. Please take a few minutes and read the first article in my signature for best practices when posting questions.
BTW, we don't need a while loop for this. Looping should be avoided when dealing with data changes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2015 at 11:03 am
My apologies! my first time using the site with visual example: here is a virtual table you can use:
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
April 24, 2015 at 11:47 am
henryalugo (4/24/2015)
My apologies! my first time using the site with visual example: here is a virtual table you can use: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
Awesome. We have half of the information we need. The sample data seems to be fine. Now what do you want to do with it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2015 at 11:59 am
Great!! I didn't know if you got to see my verbiage in my first message (towards the bottom), but in the event you haven't I'll add it here:
So I need to take the -80 figure with the Fee Tran Type = "W" for "ONE TIME DRAFT FEE" and apply it the all the "A" (Assessed) Fee Transaction Amount from the min Fee Transaction Date of each "ONE TIME DRAFT FEE" until it exhausted the full -80 waiver. Then need to remove these records from my output and reflect the rest.
The reason behind this is to only show assessed fees that are still outstanding that have not been paid or waived. It would have been an easy task if the amounts assessed matches the amounts waived and you probably can get this if you sum by fee code, but the business wants to see all itemized transactions. Since the waiver and sometimes payments are an accumulative amounts; I need to assess what the amounts should be applied towards the assessed fees from the beginning (min date of that fee_code) to present up until the amount paid or waived is reached.
April 24, 2015 at 12:09 pm
henryalugo (4/24/2015)
Great!! I didn't know if you got to see my verbiage in my first message (towards the bottom), but in the event you haven't I'll add it here:So I need to take the -80 figure with the Fee Tran Type = "W" for "ONE TIME DRAFT FEE" and apply it the all the "A" (Assessed) Fee Transaction Amount from the min Fee Transaction Date of each "ONE TIME DRAFT FEE" until it exhausted the full -80 waiver. Then need to remove these records from my output and reflect the rest.
Yes I saw that and I am sure it makes sense to you. However, since I don't understand your business rules it doesn't make any sense to me. Given your sample data what should the output be? I don't mean an explanation as much as the actual values that should be returned. A clearer explanation would certainly help though. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2015 at 12:24 pm
Okay.. I have attached a visualization. You will see two data sets; first one is what you will see when you run the virtual logic table and what you see highlighted in RED is what I need the logic to do; essentially identify the $20 One Time Draft Fee from the first inception using the MIN Transaction date. Since there were $80 waived for this fee code, 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. Hope this provides more clarity.
April 29, 2015 at 9:33 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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply