Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pvaru
Starting Member
5 Posts |
Posted - 2013-03-01 : 05:50:49
|
distcode YTD (Desc order) D101 4000 D102 2000 D103 500
6500*90%=5850
I want to find out for 5850 count(distcode) contribution
result count(Distcode)=2
is it possible write sql statement
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 06:29:23
|
[code] SELECT COUNT(DISTINCT distcode) FROM Table t OUTER APPLY (SELECT SUM(YTD) AS Prev WHERE distcode< t.distcode )t1 WHERE COALESCE(Prev,0) <= 5850 AND COALESCE(Prev,0) + YTD >5850 [/code]
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
pvaru
Starting Member
5 Posts |
Posted - 2013-03-01 : 06:43:37
|
quote: Originally posted by visakh16
SELECT COUNT(DISTINCT distcode) FROM Table t OUTER APPLY (SELECT SUM(YTD) AS Prev WHERE distcode< t.distcode )t1 WHERE COALESCE(Prev,0) <= 5850 AND COALESCE(Prev,0) + YTD >5850
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
SELECT COUNT(DISTINCT distcode) FROM ytddata t OUTER APPLY (SELECT SUM(YTD) AS Prev WHERE distcode< t.distcode )t1 WHERE COALESCE(Prev,0) <= 969575551.93 AND COALESCE(Prev,0) + YTD >969575551.93
Msg 4101, Level 15, State 1, Line 1 Aggregates on the right side of an APPLY cannot reference columns from the left side. |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 06:48:16
|
sorry there was a typo
SELECT COUNT(DISTINCT distcode) FROM ytddata t OUTER APPLY (SELECT SUM(YTD) AS Prev FROM ytddata WHERE distcode< t.distcode )t1 WHERE COALESCE(Prev,0) <= 969575551.93 AND COALESCE(Prev,0) + YTD >969575551.93
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
pvaru
Starting Member
5 Posts |
Posted - 2013-03-01 : 07:15:23
|
it is not giving proper count
my query is table having Distcode, YTD (YTD will be desc order)
from that find out Count(distcode)ie., No of distributor, their sum(YTD) contributing
to near to (969575551.93) 90%
that count i have to find |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 07:18:09
|
90% of what?
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
|
 |
|
pvaru
Starting Member
5 Posts |
Posted - 2013-03-01 : 07:29:03
|
969575551.93 is the figure derived from (sum of YTD entire sale *90%) |
 |
|
|
|
|