Need het on top 80 percent query

[jcw12000]

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?

 

[Qik3Coder]

Start with this. thread183-1654675

Post code when you get close, if you get stuck.

Lodlaiden

You've got questions and source code. We want both!

 

[jcw12000]

SET ANSI_WARNINGS OFF
SET ARITHABORT off
;with cte
as
(
SELECT ROW_NUMBER() OVER( PARTITION BY [OriginalVendorNumber],[ControlGroupID]ORDER BY Invoiceamount DESC) as rn,sum(Invoiceamount) over (PARTITION BY [OriginalVendorNumber],[ControlGroupID] ) as totalinvamt,*
FROM Step1_Top80Fulltest
)

select c1.* ,totalinvamt/100*80as [80Percent_Of_Total] --, Invoiceamount/100*80 as [80_Percent_of_Claims]--,totalamt
--into Step1_Top80_V2full
from cte c1
cross apply (select sum(Invoiceamount) as totalamt
from cte
where rn < = c1.rn
)c2


Results


rn totalinvamt Invoiceamount ControlGroupID OriginalVendorNumber Row Number 80Percent_Of_Total
1 209988 143605.8 221 0000000505 1 167990.4
2 209988 55882.8 221 0000000505 2 167990.4
3 209988 6630 221 0000000505 3 167990.4
4 209988 2580 221 0000000505 4 167990.4
5 209988 1289.4 221 0000000505 5 167990.4
1 47859228.27 5473523.04 231 0000001397 1 38287382.62
2 47859228.27 4801336 231 0000001397 2 38287382.62
3 47859228.27 2699392.72 231 0000001397 3 38287382.62
4 47859228.27 2433484.58 231 0000001397 4 38287382.62
5 478528.27 1913784.69 231 0000001397 5 38287382.62
6 47859228.27 1906890.25 231 0000001397 6 38287382.62
7 47859228.27 1730036.13 231 0000001397 7 38287382.62
8 47859228.27 1703884.5 231 0000001397 8 38287382.62
9 47859228.27 1652886.29 231 0000001397 9 38287382.62
10 47859228.27 1523918.2 231 0000001397 10 38287382.62



I now need something that will sum each line to as close as possible to the 80_Percent_Of_Total and only display those line per vendor or grouping

 

[Qik3Coder]

We could banter around all day about "Best fit" algorithms.
So you need to decide whether it is more important to fit as many small invoices in or as many big invoices in, and sort your result set to align with that.

Either way, without a separate programming language, you're going to wind up with while loop to sum them together. Look at the while loop in the post referenced earlier.
It walks the result set comparing the values. You will need to keep a running total and a set number You use the set number and a Max on the running total count to get you the "close to 80%" number.

Normally aggregating reporting is separate from detail reporting for this reason. It's painful trying to keep "some" of the detail.

Lodlaiden

You've got questions and source code. We want both!

 

[jcw12000]

Does someone perhaps have a script that can help me. I am not very clued up on WHILE LOOPS.

 

[Qik3Coder]

There is a full working sample in the post I linked to.
You can use it as a basis for your work with the sum problem.

If you want to pick a best fit metric, either as many small pieces as possible or as many big pieces as possible, I may have some time tomorrow to work through what the while loop would look like.

Lodlaiden

You've got questions and source code. We want both!

 

[Qik3Coder]

Give this a go:

DECLARE @_Inv AS TABLE(
CID int not null,
VID varchar(25) not null,
Amt money not null)

INSERT INTO @_Inv(Amt, CID, VID)
--YOU VALUES HERE, {internal security blocked the list}

SELECT * FROM @_Inv

--Above was prep work

Declare @_Inv_Work as table(
CID int not null,
VID varchar(25) not null,
Amt money not null,
TotAmt money not null,
CutOffAmt money not null,
RunningSum money,
RowNum int,
RowNumSub int)

--Select into work table
INSERT INTO @_Inv_Work
select i.*, i_sum.InvAmt, i_sum.PercentInvAmt, 0, row_number() over (Order By i.CID, i.VID, i.Amt) RowNum, row_number() over (partition by i.CID, i.VID order by i.CID, i.VID, i.Amt) as RowNumSub
from @_Inv i
JOIN (
	Select CID, VID, Sum(Amt) InvAmt, Sum(Amt) *.8 PercentInvAmt from @_Inv
	Group BY CID, VID
	) as i_sum ON
	i.CID = i_sum.CID
	AND i.VID = i_sum.VID
ORDER BY i.CID, i.VID

Select * from @_Inv_Work

DECLARE @MaxRowNum int, @RowNum int, @RowNumSub int, @RunningSum money
SELECT @MaxRowNum = max(RowNum), @RowNum = 0, @RunningSum = 0.0 FROM @_Inv_Work
WHILE(@RowNum < @MaxRowNum)
 BEGIN
    SELECT @RowNum = RowNum, @RunningSum = CASE WHEN RowNumSub = 1 THEN 0 ELSE @RunningSum END, @RunningSum = @RunningSum + Amt
    FROM @_Inv_Work where RowNum = @RowNum+1
    UPDATE @_Inv_Work
    SET RunningSum = @RunningSum
    WHERE RowNum = @RowNum
 END
 
Select * from @_inv_Work WHERE RunningSum < CutOffAmt

Lodlaiden

You've got questions and source code. We want both!

 

[jcw12000]

Thanks. I modified your script a bit to improve performance. The data set that I am using is a bit big. Took query time from 3 hours down to 43min


DECLARE @_Inv2 AS TABLE(
CID varchar(25) not null,
VID varchar(25) not null,
INVID nvarchar(255) not null,
Amt money not null)

INSERT INTO @_Inv2(INVID,Amt, CID, VID)
--YOU VALUES HERE, {internal security blocked the list}

SELECT [Invoiceid],Invoiceamount, ControlGroupID, OriginalVendorNumber
FROM Step1_Top80_0_14

--Above was prep work

select * from @_Inv2 order by amt desc

Declare @_Inv_Work2 as table(

CID varchar(25) not null,
VID varchar(25) not null,
INVID nvarchar(255) not null,
Amt money not null,
TotAmt money not null,
CutOffAmt money not null,
RunningSum money,
RowNum int,
RowNumSub int
)

--Select into work table
INSERT INTO @_Inv_Work2
select i.*, (select sum(Amt) from @_Inv2), (select sum(Amt)*0.8 from @_Inv2), 0, row_number() over ( order by i.Amt desc) RowNum, row_number() over ( order by i.Amt desc) as RowNumSub


from @_Inv2 i



Select * from @_Inv_Work2
order by RowNum

DECLARE @MaxRowNum int, @RowNum int, @RowNumSub int, @RunningSum money, @CutOffAmt money
SELECT @MaxRowNum = max(RowNum), @RowNum = 0, @RunningSum = 0.0 , @CutOffAmt = max(CutOffAmt) FROM @_Inv_Work2
WHILE(@RunningSum < @CutOffAmt)
BEGIN
SELECT @RowNum = RowNum, @RunningSum = CASE WHEN RowNumSub = 1 THEN 0 ELSE @RunningSum END, @RunningSum = @RunningSum + Amt
FROM @_Inv_Work2 where RowNum = @RowNum+1
UPDATE @_Inv_Work2
SET RunningSum = @RunningSum
WHERE RowNum = @RowNum
END

Select *
into Step1_Top80_V2_0_14
from @_inv_Work2
WHERE RunningSum <> 0.00

 

[jcw12000]

Ignore above post. Posted wrong query but the above query works for 80 /20 percent on total data set and not per VID 80/20 percent

 

[Qik3Coder]

So, are you all set now?

I wasn't sure if you could have the multiple CID/VID combinations, so I coded for it.
It's easier to remove restrictions than add new rules later.

You've got questions and source code. We want both!

 

[jcw12000]

Hi Guys

Can someone please look at the code and see if you can optimize it. When I run the code I use 98% of my server and it takes forever

DECLARE @_Inv2 AS TABLE(
CID varchar(25) not null,
VID varchar(25) not null,
INVID nvarchar(255) not null,
Amt money not null)

INSERT INTO @_Inv2(INVID,Amt, CID, VID)
--YOU VALUES HERE, {internal security blocked the list}

SELECT [Invoiceid],Invoiceamount, ControlGroupID, OriginalVendorNumber
FROM Step1_Top80_0_14

--Above was prep work

select * from @_Inv2 order by amt desc

Declare @_Inv_Work2 as table(

CID varchar(25) not null,
VID varchar(25) not null,
INVID nvarchar(255) not null,
Amt money not null,
TotAmt money not null,
CutOffAmt money not null,
RunningSum money,
RowNum int,
RowNumSub int
)

--Select into work table
INSERT INTO @_Inv_Work2
select i.*, (select sum(Amt) from @_Inv2), (select sum(Amt)*0.8 from @_Inv2), 0, row_number() over ( order by i.Amt desc) RowNum, row_number() over ( order by i.Amt desc) as RowNumSub


from @_Inv2 i



Select * from @_Inv_Work2
order by RowNum

DECLARE @MaxRowNum int, @RowNum int, @RowNumSub int, @RunningSum money, @CutOffAmt money
SELECT @MaxRowNum = max(RowNum), @RowNum = 0, @RunningSum = 0.0 , @CutOffAmt = max(CutOffAmt) FROM @_Inv_Work2
WHILE(@RunningSum < @CutOffAmt)
BEGIN
SELECT @RowNum = RowNum, @RunningSum = CASE WHEN RowNumSub = 1 THEN 0 ELSE @RunningSum END, @RunningSum = @RunningSum + Amt
FROM @_Inv_Work2 where RowNum = @RowNum+1
UPDATE @_Inv_Work2
SET RunningSum = @RunningSum
WHERE RowNum = @RowNum
END

Drop table Step1_Top80_V2_0_14
Select *
into Step1_Top80_V2_0_14
from @_inv_Work2
WHERE RunningSum <> 0.00

order by AMT

 

[Qik3Coder]

Have you run it with "Include Actual Execution Plan" On, so that you can ensure you're not missing an index?
This will also highlight anywhere you are using a Index Scan instead of an Index Seek, which will cause a performance hit.

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)