February 22, 2011 at 2:12 pm
Please bear with me through what I am being requested to do... it is a bit confusing.
I need to generate a report that displays cases thathave closed from the previous day listing fees distributed, initials of whom the fees went to, date case was closed and amount of settlement. Part of the criteria was also to only display the settlement amount once if there was a distribution of fees to multiple attorneys.... all of which I was able to due except they wanted to have the settlement amount listed with the primary attorney or manager of the case (not always the same).
So here is sample data/query:
--=============================================================
-- temp table for test data
--========================================================
IF OBJECT_ID('TempDB..#acsv','u') IS NOT NULL
DROP TABLE #acsv
CREATE TABLE #acsv
(
ukey int not null,
case_sk int not null,
case_number varchar (15) null,
closed_date datetime null,
settle_date datetime null,
settlement_amount decimal(14,2)null,
fee_portion decimal(14,2) null,
fee_percent decimal(4,1) null,
primary_attorney varchar(3) null,
fee_atty varchar(3) null,
manager varchar(3) null
)
INSERT INTO #acsv (ukey, case_sk, case_number, closed_date, settle_date, settlement_amount, fee_portion, fee_percent, primary_attorney, fee_atty, manager)
SELECT 1, 1, '111111', '2010-03-16', '2010-03-16', 5000.00, 2000.00, 100.0, 'AAA', 'EEE', 'EEE' UNION ALL
SELECT 2, 2, '222222', '2010-03-16', '2010-03-16',25000.00, 6000.00, 60.0, 'CCC', 'CCC', 'CCC' UNION ALL
SELECT 3, 2, '222222', '2010-03-16', '2010-03-16',25000.00, 4000.00, 40.0, 'CCC', 'BBB', 'CCC' UNION ALL
SELECT 4, 3, '333333', '2010-03-16', '2010-03-16',10000.00, 1500.00, 50.0, 'CCC', 'CCC', 'CCC' UNION ALL
SELECT 5, 3, '333333', '2010-03-16', '2010-03-16',10000.00, 1500.00, 50.0, 'CCC', 'AAA', 'CCC' UNION ALL
SELECT 6, 4, '444444', '2010-03-16', '2010-03-16',15000.00, 5000.00, 100.0, 'DDD', 'EEE', 'EEE' UNION ALL
SELECT 7, 5, '555555', '2010-03-16', '2010-03-16', 3000.00, 1000.00, 100.0, 'DDD', 'DDD', 'DDD' UNION ALL
SELECT 8, 6, '666666', '2010-03-16', '2010-03-16', 4000.00, 1000.00, 100.0, 'DDD', 'DDD', 'DDD'
SELECT fee_portion, fee_percent, settle_date, fee_atty, manager, primary_attorney,
[Settle Amount] =
CASE ROW_NUMBER() OVER(PARTITION BY case_sk ORDER BY fee_portion)
WHEN 1 THEN settlement_amount
ELSE 0
END
FROM #acsv
DROP TABLE #acsv
Which displays like this:
fee_portion fee_percent settle_date fee_attymanager primary_attorneySettle Amount
2000.00 100.0 2010-03-16 00:00:00.000EEE EEE AAA 5000.00
4000.00 40.0 2010-03-16 00:00:00.000BBB CCC CCC 25000.00
6000.00 60.0 2010-03-16 00:00:00.000CCC CCC CCC 0.00
1500.00 50.0 2010-03-16 00:00:00.000CCC CCC CCC 10000.00
1500.00 50.0 2010-03-16 00:00:00.000AAA CCC CCC 0.00
5000.00 100.0 2010-03-16 00:00:00.000EEE EEE DDD 15000.00
1000.00 100.0 2010-03-16 00:00:00.000DDD DDD DDD 3000.00
1000.00 100.0 2010-03-16 00:00:00.000DDD DDD DDD 4000.00
This is how it should be displayed (row 2 and 3 I changed):
fee_portion fee_percent settle_date fee_attymanager primary_attorneySettle Amount
2000.00 100.0 2010-03-16 00:00:00.000EEE EEE AAA 5000.00
4000.00 40.0 2010-03-16 00:00:00.000BBB CCC CCC 0.00
6000.00 60.0 2010-03-16 00:00:00.000CCC CCC CCC 25000.00
1500.00 50.0 2010-03-16 00:00:00.000CCC CCC CCC 10000.00
1500.00 50.0 2010-03-16 00:00:00.000AAA CCC CCC 0.00
5000.00 100.0 2010-03-16 00:00:00.000EEE EEE DDD 15000.00
1000.00 100.0 2010-03-16 00:00:00.000DDD DDD DDD 3000.00
1000.00 100.0 2010-03-16 00:00:00.000DDD DDD DDD 4000.00
So what I need is to have the settlement amount listed with the primary attorney IF he/she gets a portion of the fee or the manager of the case....
Thanks in advance for any guidance.....
February 22, 2011 at 2:30 pm
Something like this?
(Please note the rows are not ordered as your expected output since your original query doesn't include an ORDER BY)
SELECT
fee_portion, fee_percent, settle_date, fee_atty, manager, primary_attorney,
[Settle Amount] = CASE
WHEN fee_atty=manager OR fee_atty=primary_attorney
THEN settlement_amount
ELSE 0 END
FROM #acsv
February 22, 2011 at 2:33 pm
I'm guessing that when the primary attorney value equals the fee attorney value, then that's the one you want to display the number on, if it exists. Correct?
If so, in your Row_Number function, add a Case statement to the Order By portion:
Row_Number() over (partition by case_sk order by case when fee_attorney = primary_attorney then 1 else 2 end, fee_portion)
You might need to modify that to deal with manager as well, which would just be another When line in the Case statement.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 8:14 am
Thanks you both... not sure why I didn't see the solution.....
here is what I am going to use:
[Settle Amount] =
CASE
WHEN (acsv.fee_atty = acsv.manager) AND (acsv.fee_percent = 100) THEN settlement_amount
WHEN (acsv.fee_atty = primary_attorney) AND (primary_attorney = acsv.manager) then settlement_amount
WHEN (acsv.fee_atty = primary_attorney) AND (primary_attorney <> acsv.manager) AND (acsv.fee_percent > 0) then settlement_amount
ELSE 0
END,
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply