Hello Team -

I'm new to T-SQL.

I have a set of sales reps and have accounts assigned to them. I want to be able to take out only those accounts for each sales rep whose REVMIX won't exceed more than 10% (summing up the total revmix and capping it to <=10%). Not sure how to get to it.
Kindly help. Below is how the data looks like.

SALES REP	ACCOUNT_ID	REVENUE	TOTALREVENUE	REVMIX
23626	123456791	100	$44,100	0.2%
23626	123456793	300	$44,100	0.7%
23626	123456795	500	$44,100	1.1%
23626	123456797	700	$44,100	1.6%
23626	123456799	900	$44,100	2.0%
23626	123456801	1100	$44,100	2.5%
23626	123456803	1300	$44,100	2.9%
23626	123456805	1500	$44,100	3.4%
23626	123456807	1700	$44,100	3.9%
23626	123456809	1900	$44,100	4.3%
23626	123456811	2100	$44,100	4.8%
23626	123456813	2300	$44,100	5.2%
23626	123456815	2500	$44,100	5.7%
23626	123456817	2700	$44,100	6.1%
23626	123456819	2900	$44,100	6.6%
23626	123456821	3100	$44,100	7.0%
23626	123456823	3300	$44,100	7.5%
23626	123456825	3500	$44,100	7.9%
23626	123456827	3700	$44,100	8.4%
23626	123456829	3900	$44,100	8.84%
23626	123456831	4100	$44,100	9.30%
236267	123456802	4300	167500	2.57%
236267	123456814	4500	167500	2.69%
236267	123456826	4700	167500	2.81%
236267	123456838	4900	167500	2.93%
236267	123456850	5100	167500	3.04%
236267	123456862	5300	167500	3.16%
236267	123456874	5500	167500	3.28%
236267	123456886	5700	167500	3.40%
236267	123456898	5900	167500	3.52%
236267	123456910	6100	167500	3.64%
236267	123456922	6300	167500	3.76%
236267	123456934	6500	167500	3.88%
236267	123456946	6700	167500	4.00%
236267	123456958	6900	167500	4.12%
236267	123456970	7100	167500	4.24%
236267	123456982	7300	167500	4.36%
236267	123456994	7500	167500	4.48%
236267	123457006	7700	167500	4.60%
236267	123457018	7900	167500	4.72%
236267	123457030	8100	167500	4.84%
236267	123457042	8300	167500	4.96%
236267	123457054	8500	167500	5.07%
236267	123457066	8700	167500	5.19%
236267	123457078	8900	167500	5.31%
236267	123457090	9100	167500	5.43%
236268	123457102	9300	136500	6.81%
236268	123457114	9500	136500	6.96%
236268	123457126	9700	136500	7.11%
236268	123457138	9900	136500	7.25%
236268	123457150	10100	136500	7.40%
236268	123457162	10300	136500	7.55%
236268	123457174	10500	136500	7.69%
236268	123457186	10700	136500	7.84%
236268	123457198	10900	136500	7.99%
236268	123457210	11100	136500	8.13%
236268	123457222	11300	136500	8.28%
236268	123457234	11500	136500	8.42%
236268	123457246	11700	136500	8.57%

• Edited by Syed Subhahan 17 hours 4 minutes ago

select Account_ID,Sales_REP
from mytable
group by account_ID,sales_Rep
having sum(revmix)<=10

create table #sales 
(

SALES_REP int not null,
	ACCOUNT_ID int not null,
		REVENUE float not null,
)

insert into #sales values(23626,123456791,100)
insert into #sales values(23626,123456793,300)
insert into #sales values(23626,123456795,500)
insert into #sales values(23626,123456797,700)
insert into #sales values(23626,123456799,900)
insert into #sales values(23626,123456801,1100)
insert into #sales values(23626,123456803,1300)
insert into #sales values(23626,123456805,1500)
insert into #sales values(23626,123456807,1700)
insert into #sales values(23626,123456809,1900)
insert into #sales values(23626,123456811,2100)
insert into #sales values(23626,123456813,2300)
insert into #sales values(23626,123456815,2500)
insert into #sales values(23626,123456817,2700)
insert into #sales values(23626,123456819,2900)
insert into #sales values(23626,123456821,3100)
insert into #sales values(23626,123456823,3300)
insert into #sales values(23626,123456825,3500)
insert into #sales values(23626,123456827,3700)
insert into #sales values(23626,123456829,3900)
insert into #sales values(23626,123456831,4100)

insert into #sales values(236267,123456802,4300)
insert into #sales values(236267,123456814,4500)
insert into #sales values(236267,123456826,4700)
insert into #sales values(236267,123456838,4900)
insert into #sales values(236267,123456850,5100)
insert into #sales values(236267,123456862,5300)
insert into #sales values(236267,123456874,5500)
insert into #sales values(236267,123456886,5700)
insert into #sales values(236267,123456898,5900)
insert into #sales values(236267,123456910,6100)
insert into #sales values(236267,123456922,6300)
insert into #sales values(236267,123456934,6500)
insert into #sales values(236267,123456946,6700)
insert into #sales values(236267,123456958,6900)
insert into #sales values(236267,123456970,7100)
insert into #sales values(236267,123456982,7300)
insert into #sales values(236267,123456994,7500)
insert into #sales values(236267,123457006,7700)
insert into #sales values(236267,123457018,7900)
insert into #sales values(236267,123457030,8100)
insert into #sales values(236267,123457042,8300)
insert into #sales values(236267,123457054,8500)
insert into #sales values(236267,123457066,8700)
insert into #sales values(236267,123457078,8900)
insert into #sales values(236267,123457090,9100)
insert into #sales values(236268,123457102,9300)
insert into #sales values(236268,123457114,9500)
insert into #sales values(236268,123457126,9700)
insert into #sales values(236268,123457138,9900)
insert into #sales values(236268,123457150,10100)
insert into #sales values(236268,123457162,10300)
insert into #sales values(236268,123457174,10500)
insert into #sales values(236268,123457186,10700)
insert into #sales values(236268,123457198,10900)
insert into #sales values(236268,123457210,11100)
insert into #sales values(236268,123457222,11300)
insert into #sales values(236268,123457234,11500)
insert into #sales values(236268,123457246,11700)


select sales_rep,sum(revenue) as tot_revenue
into #totals
from #sales
group by SALES_REP


select *,row_number() over (partition by sales_rep order by revenue) as rn
into #temp
from #sales


select a.sales_rep,a.ACCOUNT_ID,a.revenue ,a.rn,sum(b.revenue),sum(b.revenue)/c.tot_revenue
from #temp a
join #totals c on c.SALES_REP = a.SALES_REP
join #temp b on a.rn >= b.rn and a.sales_rep = b.sales_rep 

group by  a.sales_rep,a.ACCOUNT_ID,a.revenue ,a.rn,c.tot_revenue
having sum(b.revenue)/c.tot_revenue <0.10

do you mean this?

None of those accounts have more than one row. Either your example data is doing a poor job of demonstrating your problem, or you need to explain your requirement more clearly.

My apologies for the poor. Let me put this in a very simple way.

SalesRep = A

A = 10 Accounts

and Revenue = 1000

I have 3 accounts whose contribution is less than or equal to 10% in revenue.

My output should be:

Salesrep Account TotalRev AccRev  RevMix

   A            123          $1000         $10     1%

   A            456          $1000         $30     3%

   A            789          $1000         $60     6%

I want only those accounts per each salesrep whose accounts and their revmix adds upto <=10%

Hope this helps

Thanks a ton for helping me out. :)

please run this and if this not the output you are excepting.. please tell us the sample output based the input in the table

declare @accounts table(Salesrep char(1),Account int,TotalRev money,AccRev money,RevMix decimal(5,2))

insert into @accounts
values 
('A',123,1000,10,1),
('A',456,1000,30,3),
('A',789,1000,60,6),
('A',456,1000,300,30),
('A' ,789,1000,600,60)


select Account,SalesrEP
from @accounts
group by account,salesRep
having sum(revmix)<=10

This didn't work. Since, in my final output, I want only those rows whose RevMix <=10%. I'm not sure why I'm not able to get the final output.

This didn't work. Since, in my final output, I want only those rows whose RevMix <=10%. I'm not sure why I'm not able to get the final output.

ok.  provide the output you are excepting for above input..

it appears, you just want revmix<=10, which can be achieved through a simple where clause.. not sure if i am following what are you excepting.. it will easy if you can provide the sample output for my above input..

declare @accounts table(Salesrep char(1),Account int,TotalRev money,AccRev money,RevMix decimal(5,2))

insert into @accounts
values 
('A',123,1000,10,1),
('A',456,1000,30,3),
('A',789,1000,60,6),
('A',456,1000,300,30),
('A' ,789,1000,600,60)


--is this what you want
select Account,SalesrEP
from @accounts
where Revmix<=10

This is close to a live data. There are about 23 rows here from the table. Sum(REVENUE) for the 10 rows would add up to 2380114.397. I will get 10% MIX if I divide it with RUNNINGTOTAL_REVENUE. For some reason when I'm applying where condition it's not returning those first 10 rows. Not sure what the problem is. :(

SALESREP	ACCOUNT_ID	REVENUE	RUNNINGTOTAL_REVENUE	MIX
236300	ACCOUNT1	17917.83	23451885.59	0%
236300	ACCOUNT2	67402.07	23451885.59	0%
236300	ACCOUNT3	69576.646	23451885.59	0%
236300	ACCOUNT4	82828.84	23451885.59	0%
236300	ACCOUNT5	130955.866	23451885.59	1%
236300	ACCOUNT6	153865.04	23451885.59	1%
236300	ACCOUNT7	316329.34	23451885.59	1%
236300	ACCOUNT8	476030	23451885.59	2%
236300	ACCOUNT9	483660.01	23451885.59	2%
236300	ACCOUNT10	581548.755	23451885.59	2%
236300	ACCOUNT11	669007.05	23451885.59	3%
236300	ACCOUNT12	683942.12	23451885.59	3%
236300	ACCOUNT13	748481.98	23451885.59	3%
236300	ACCOUNT14	878047.8	23451885.59	4%
236300	ACCOUNT15	1127457.05	23451885.59	5%
236300	ACCOUNT16	1221103.91	23451885.59	5%
236300	ACCOUNT17	1422627.56	23451885.59	6%
236300	ACCOUNT18	1492686.19	23451885.59	6%
236300	ACCOUNT19	1698136.33	23451885.59	7%
236300	ACCOUNT20	2536279.246	23451885.59	11%
236300	ACCOUNT21	2618973.05	23451885.59	11%
236300	ACCOUNT22	2860847.283	23451885.59	12%
236300	ACCOUNT23	3114181.627	23451885.59	13%

Please post DDL , I do not think people would really want to take the pain in creating  ddl scripts from what you provided. it only helps you, because you can get answer lot faster instead of going back and forth.

based on what you said above and per my understanding on it, it looks you want to sum the Revmix % for each row  with previous rows and return the all the rows that have revmix total less than 10.

what version of sql are using??? if sql 2012 and above, this should work, if my understanding in correct.. try this...

declare @accounts table(Salesrep char(1),Account int,TotalRev money,AccRev money,RevMix decimal(5,2))

insert into @accounts
values 
('A',123,1000,10,1),
('A',456,1000,30,3),
('A',789,1000,60,6),
('A',789,1000,90,9),
('A',456,1000,300,30),
('A' ,789,1000,600,60),
('B',123,1000,20,2),
('B',456,1000,60,6),
('B',789,1000,60,6),
('B',789,1000,90,9),
('B',456,1000,300,30),
('B' ,789,1000,600,60)

;With CTE as (select *,sum(RevMix) OVER (Partition by SalesRep  order by RevMIx Rows between unbounded preceding and current row) as TotalRevMix from @accounts)

Select * from CTE where TotalRevMix<=10

SELECT 
SALESREP
,ACCOUNT_ID
,REVENUE
,RUNNINGTOTAL_REVENUE
,(REVENUE/RUNNINGTOTAL_REVENUE) AS REVMIX
FROM
(
SELECT  
DAP.SALESREP
,ACCOUNT_ID
,REVENUE
            ,SUM(REVENUE) OVER(PARTITION BY DAP.SALESREP ORDER BY DAP.SALESREP) RUNNINGTOTAL_REVENUE

    FROM DAVINCI_USA..FACTACCOUNT FA
INNER JOIN DAVINCI_USA..[DIMACCOUNTPERSON] DAP ON FA.ACCOUNT_ID = DAP.[PARTY_ID] AND DAP.SALESREP IS NOT NULL
WHERE DAP.SALESREP = 236300

) TB1

WHERE (REVENUE/RUNNINGTOTAL_REVENUE) <= 0.1
ORDER BY REVENUE ASC

You're still not being clear... all the rows up until 19 are less than 10%...

If you want the FIRST 10 rows that total no more than 10% you'll need to specify an order...

I get a 10% sum of Revmix when I'm trying it in Excel manually on first 10 rows. For first 19 rows, I get 53%. Let me explain you mathematically.

Sum of Revenue on 1st 10 rows: 2380114.397

Calculating RevMix on first 10 rows: 2380114.397/23451885.593(RunningTotal_Revenue) = 10%

Sum of Revenue on 1st 19 rows: 12321604.39

Calculating RevMix on first 10 rows: 12321604.39/23451885.593(RunningTotal_Revenue) = 53%

Hope this helps clarify where I'm struck.

Yeah, like I said, if you want the first 10 rows, you'll need to define an order. Is it ordered by account number? Revenue? What?

It's ordered by SalesRep. Since the whole exercise is to find out by each salesrep how many accounts that sum upto <=10% in revmix.

You can't order by salesrep... that's a constant... 

Oh my bad. It has to be ordered by Revenue.

• Marked as answer by Syed Subhahan 10 hours 0 minutes ago
• Unmarked as answer by Syed Subhahan 9 hours 59 minutes ago

Try this:

DECLARE @sales TABLE (SALESREP INT,	ACCOUNT_ID INT,	REVENUE MONEY, TOTALREVENUE MONEY, REVMIX FLOAT)
INSERT INTO @sales (SALESREP, ACCOUNT_ID, REVENUE, TOTALREVENUE, REVMIX) VALUES
(23626 ,	123456791,	100	 ,	44100 ,	0.2 ),(23626 ,	123456793,	300	 ,	44100 ,	0.7 ),(23626 ,	123456795,	500	 ,	44100 ,	1.1 ),(23626 ,	123456797,	700	 ,	44100 ,	1.6 ),(23626 ,	123456799,	900	 ,	44100 ,	2.0 ),(23626 ,	123456801,	1100 ,	44100 ,	2.5 ),
(23626 ,	123456803,	1300 ,	44100 ,	2.9 ),(23626 ,	123456805,	1500 ,	44100 ,	3.4 ),(23626 ,	123456807,	1700 ,	44100 ,	3.9 ),(23626 ,	123456809,	1900 ,	44100 ,	4.3 ),(23626 ,	123456811,	2100 ,	44100 ,	4.8 ),(23626 ,	123456813,	2300 ,	44100 ,	5.2 ),
(23626 ,	123456815,	2500 ,	44100 ,	5.7 ),(23626 ,	123456817,	2700 ,	44100 ,	6.1 ),(23626 ,	123456819,	2900 ,	44100 ,	6.6 ),(23626 ,	123456821,	3100 ,	44100 ,	7.0 ),(23626 ,	123456823,	3300 ,	44100 ,	7.5 ),(23626 ,	123456825,	3500 ,	44100 ,	7.9 ),
(23626 ,	123456827,	3700 ,	44100 ,	8.4 ),(23626 ,	123456829,	3900 ,	44100 ,	8.84),(23626 ,	123456831,	4100 ,	44100 ,	9.30),(236267,	123456802,	4300 ,	167500,	2.57),(236267,	123456814,	4500 ,	167500,	2.69),(236267,	123456826,	4700 ,	167500,	2.81),
(236267,	123456838,	4900 ,	167500,	2.93),(236267,	123456850,	5100 ,	167500,	3.04),(236267,	123456862,	5300 ,	167500,	3.16),(236267,	123456874,	5500 ,	167500,	3.28),(236267,	123456886,	5700 ,	167500,	3.40),(236267,	123456898,	5900 ,	167500,	3.52),
(236267,	123456910,	6100 ,	167500,	3.64),(236267,	123456922,	6300 ,	167500,	3.76),(236267,	123456934,	6500 ,	167500,	3.88),(236267,	123456946,	6700 ,	167500,	4.00),(236267,	123456958,	6900 ,	167500,	4.12),(236267,	123456970,	7100 ,	167500,	4.24),
(236267,	123456982,	7300 ,	167500,	4.36),(236267,	123456994,	7500 ,	167500,	4.48),(236267,	123457006,	7700 ,	167500,	4.60),(236267,	123457018,	7900 ,	167500,	4.72),(236267,	123457030,	8100 ,	167500,	4.84),(236267,	123457042,	8300 ,	167500,	4.96),
(236267,	123457054,	8500 ,	167500,	5.07),(236267,	123457066,	8700 ,	167500,	5.19),(236267,	123457078,	8900 ,	167500,	5.31),(236267,	123457090,	9100 ,	167500,	5.43),(236268,	123457102,	9300 ,	136500,	6.81),(236268,	123457114,	9500 ,	136500,	6.96),
(236268,	123457126,	9700 ,	136500,	7.11),(236268,	123457138,	9900 ,	136500,	7.25),(236268,	123457150,	10100,	136500,	7.40),(236268,	123457162,	10300,	136500,	7.55),(236268,	123457174,	10500,	136500,	7.69),(236268,	123457186,	10700,	136500,	7.84),
(236268,	123457198,	10900,	136500,	7.99),(236268,	123457210,	11100,	136500,	8.13),(236268,	123457222,	11300,	136500,	8.28),(236268,	123457234,	11500,	136500,	8.42),(236268,	123457246,	11700,	136500,	8.57)

SELECT *, ((SELECT SUM(REVENUE) FROM @sales WHERE SALESREP = s.SALESREP AND REVENUE <= s.REVENUE) / TOTALREVENUE) * 100
  FROM @sales s
 WHERE (SELECT SUM(REVENUE) FROM @sales WHERE SALESREP = s.SALESREP AND REVENUE < s.REVENUE) / TOTALREVENUE <= .10

This is working just like I needed it. Thanks mil for your help @patrick & @Stan. One thing I noticed from the execution is that, query is consuming time to execute the results. Not sure if that can be solved. Anyways, just wanted to bring this up.

Thanks again for your help.

You'll want to inspect the execution plan, and index accordingly.

Okie. I'll take a stab at the execution plan. Thank you.