July 21, 2010 at 2:47 pm
I am not sure if this belongs here or the Report Forum... let me know if this is not the best place to post this....
I have a report that lists cases that we have received fees with the amount of the settlement. On some case, 2 attorneys may work on it so the fee is split. On the report, I need to exclude the settlement amount from one of them (preferrably the second row). I also only want to add the settlement amount to the total of the column once...
Test Data:
--=============================================================
-- temp table for test data
--========================================================
IF OBJECT_ID('TempDB..#test','u') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test
(
case_sk int not null,
case_number varchar (15) null,
closed_date datetime null,
settle_date datetime null,
settle_amount decimal(14,2)null,
fee_portion decimal(14,2) null,
fee_percent decimal(4,1) null,
initials varchar(3) null
)
INSERT INTO #test (case_sk, case_number, closed_date, settle_date, settle_amount, fee_portion, fee_percent, initials)
SELECT 1, '111111', '2010-03-16', '2010-03-16', 5000.00, 2000.00, 100.0, 'AAA' UNION ALL
SELECT 2, '222222', '2010-03-16', '2010-03-16',25000.00, 6000.00, 60.0, 'BBB'UNION ALL
SELECT 3, '222222', '2010-03-16', '2010-03-16',25000.00, 6000.00, 40.0, 'CCC'UNION ALL
SELECT 4, '333333', '2010-06-16', '2010-06-16',10000.00, 1500.00, 50.0, 'AAA'UNION ALL
SELECT 5, '333333', '2010-04-16', '2010-04-16',10000.00, 1500.00, 50.0, 'CCC'UNION ALL
SELECT 6, '555555', '2010-05-16', '2010-05-16',15000.00, 5000.00, 100.0, 'CCC'UNION ALL
SELECT 7, '666666', '2010-02-16', '2010-02-16', 3000.00, 1000.00, 100.0, 'DDD'UNION ALL
SELECT 8, '777777', '2010-01-25', '2010-01-25', 3000.00, 1000.00, 100.0, 'DDD'
SELECT *
FROM #test
DROP TABLE #test
This is how I need to display it:
Case Number Fee % Fee Settle Amount Initials
111111 100% $ 2,000.00 $ 5,000.00 AAA
222222 60% $ 6,000.00 $25,000.00 BBB
222222 40% $ 4,000.00 CCC
333333 50% $ 1,500.00 $10,000.00 AAA
333333 50% $ 1,500.00 CCC
555555 100% $ 5,000.00 $15,000.00 CCC
666666 100% $ 1,000.00 $ 3,000.00 DDD
777777 100% $ 1,000.00 $ 3,000.00 DDD
Total amount: $61,000.00
Not even sure where to begin.... any help would be appreaciated....
Thanks
July 21, 2010 at 3:17 pm
Not sure what you're wanting to display for the Fee ... whether that line's portion or the full thing, but hopefully you can modify this to get what you want:
SELECT case_number,
fee_percent,
fee = fee_portion * (fee_percent/100),
settle_amount = case RN
when 1 then settle_amount end,
initials
FROM
(SELECT case_number,
fee_portion,
fee_percent,
settle_amount,
initials,
RN = ROW_NUMBER() OVER(PARTITION BY case_number ORDER BY case_sk)
FROM #test) sq
July 22, 2010 at 6:11 am
Thanks for the reply... I will play with that today to see if that will work.
For the Fee, It would need to display both since it is for 2 different people but only display the settlement amount once.
July 22, 2010 at 6:52 am
I may have taken the requirements a bit too literally here, but anyway, here's my shot at it:
SELECT [Case Number] = case_number,
[Fee %] = RIGHT(SPACE(2) + CONVERT(VARCHAR(3), CONVERT(INTEGER, fee_percent)) + '%', 4),
[Fee] = '$ ' + SPACE((SELECT ROUND(MAX(LOG10(fee_portion)), 0, 1) FROM #test) - ROUND(LOG10(fee_portion), 0, 1)) + CONVERT(VARCHAR(15), fee_portion),
[Settle Amount] =
CASE ROW_NUMBER() OVER (PARTITION BY case_number ORDER BY fee_portion, initials)
WHEN 1 THEN '$ ' + SPACE((SELECT ROUND(MAX(LOG10(settle_amount)), 0, 1) FROM #test) - ROUND(LOG10(settle_amount), 0, 1)) + CONVERT(VARCHAR(15), settle_amount)
ELSE SPACE(0)
END,
[Initials] = initials
FROM #test
ORDER BY
case_number, fee_portion, initials;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 22, 2010 at 8:50 am
Hey Paul,
First thanks... I used yours to put the CASE in SELECT statement cause it fit better in the actual query I have. I do most of the calculations and formating at the report design level. But I did run into an isssue when I removed all the formating and calcs from yours (should have included that the fees are the actual fees, no calc is needed for them).... I am getting an error "unable to convert varchar to numeric... so my question would be, what columns are required to be converted? and for my own personal knowledge why (i.e. cause of the CASE or PARTITION BY)?
July 22, 2010 at 11:05 am
Paul White's sans formatting (since you're doing that at presentation) would just be:
SELECT case_number,
fee_percent,
fee_portion,
settle_amount = CASE ROW_NUMBER() OVER(PARTITION BY case_number ORDER BY case_sk)
WHEN 1 THEN settle_amount
ELSE 0 END,
initials
FROM #test
ORDER BY case_number, fee_portion, initials;
It's effectively the same (same query plan) as using a derived table, but I do like it ... cleaner and more straight forward to read.
July 22, 2010 at 11:17 am
ahh thanks... I had left the SPACE(0) on the ELSE... I guess I need to read up on that to see why I was getting an error...
Thanks!
July 22, 2010 at 11:20 am
Thanks for the tidy-up BT. Sorry I didn't respond sooner - time zone issues 🙂
edit: though to get strictly the same execution plan, I'd need to modify the final ORDER BY slightly:
SELECT case_number,
fee_percent,
fee_portion,
settle_amount =
CASE ROW_NUMBER() OVER(PARTITION BY case_number ORDER BY case_sk)
WHEN 1 THEN settle_amount
ELSE 0
END,
initials
FROM #test
ORDER BY case_number, case_sk;
(that removes the extra sort)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply