HI
I have the following data
Invoiceamount ControlGroupID OriginalVendorNumber RowNumber
143605.80 221 0000000505 1
55882.80 221 0000000505 2
6630.00 221 0000000505 3
2580.00 221 0000000505 4
1289.40 221 0000000505 5
5473523.04 231 0000001397 1
4801336.00 231 0000001397 2
2699392.72 231 0000001397 3
2433484.58 231 0000001397 4
1913784.69 231 0000001397 5
1906890.25 231 0000001397 6
1730036.13 231 0000001397 7
1703884.50 231 0000001397 8
1652886.29 231 0000001397 9
1523918.20 231 0000001397 10
Above is just a few sample lines. I used this to get the results:
SELECT Invoiceamount,[ControlGroupID],
[OriginalVendorNumber]
,ROW_NUMBER() OVER( PARTITION BY [OriginalVendorNumber],[ControlGroupID] ORDER BY Invoiceamount DESC) AS 'Row Number'
--INTO Step1_Top80Fulltest
FROM [01_InvTable_VAT_Transactions_With_Rank_Buckets_Outstanding_Work_Full_VEndors_Count_MAX10]
GROUP BY Invoiceamount,[ControlGroupID]
,[OriginalVendorNumber]
What I need to do is to get all the transactions/lines that make up 80% (as close to 80% as possible)of the specific OriginalVendorNumbers transactions with the invoiceamount desc.
Results should be the following
Invoiceamount ControlGroupID OriginalVendorNumber RowNumber
143605.80 221 0000000505 1
5473523.04 231 0000001397 1
4801336.00 231 0000001397 2
2699392.72 231 0000001397 3
2433484.58 231 0000001397 4
1913784.69 231 0000001397 5
1906890.25 231 0000001397 6
Any ideas?
I have the following data
Invoiceamount ControlGroupID OriginalVendorNumber RowNumber
143605.80 221 0000000505 1
55882.80 221 0000000505 2
6630.00 221 0000000505 3
2580.00 221 0000000505 4
1289.40 221 0000000505 5
5473523.04 231 0000001397 1
4801336.00 231 0000001397 2
2699392.72 231 0000001397 3
2433484.58 231 0000001397 4
1913784.69 231 0000001397 5
1906890.25 231 0000001397 6
1730036.13 231 0000001397 7
1703884.50 231 0000001397 8
1652886.29 231 0000001397 9
1523918.20 231 0000001397 10
Above is just a few sample lines. I used this to get the results:
SELECT Invoiceamount,[ControlGroupID],
[OriginalVendorNumber]
,ROW_NUMBER() OVER( PARTITION BY [OriginalVendorNumber],[ControlGroupID] ORDER BY Invoiceamount DESC) AS 'Row Number'
--INTO Step1_Top80Fulltest
FROM [01_InvTable_VAT_Transactions_With_Rank_Buckets_Outstanding_Work_Full_VEndors_Count_MAX10]
GROUP BY Invoiceamount,[ControlGroupID]
,[OriginalVendorNumber]
What I need to do is to get all the transactions/lines that make up 80% (as close to 80% as possible)of the specific OriginalVendorNumbers transactions with the invoiceamount desc.
Results should be the following
Invoiceamount ControlGroupID OriginalVendorNumber RowNumber
143605.80 221 0000000505 1
5473523.04 231 0000001397 1
4801336.00 231 0000001397 2
2699392.72 231 0000001397 3
2433484.58 231 0000001397 4
1913784.69 231 0000001397 5
1906890.25 231 0000001397 6
Any ideas?