| Author |
 Topic  |
|
|
pvaru
Starting Member
India
5 Posts |
 Posted - 03/01/2013 : 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
India
47189 Posts |
Posted - 03/01/2013 : 06:29:23
|
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/
|
 |
|
|
pvaru
Starting Member
India
5 Posts |
Posted - 03/01/2013 : 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
India
47189 Posts |
Posted - 03/01/2013 : 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
India
5 Posts |
Posted - 03/01/2013 : 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
India
47189 Posts |
Posted - 03/01/2013 : 07:18:09
|
90% of what?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
pvaru
Starting Member
India
5 Posts |
Posted - 03/01/2013 : 07:29:03
|
| 969575551.93 is the figure derived from (sum of YTD entire sale *90%) |
|
|
| |
Topic |
|
SQl query
