SQl query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQl query

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%)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources