SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQl query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pvaru
Starting Member

India
5 Posts

Posted - 03/01/2013 :  05:50:49  Show Profile  Reply with Quote
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
52325 Posts

Posted - 03/01/2013 :  06:29:23  Show Profile  Reply with Quote

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/

Go to Top of Page

pvaru
Starting Member

India
5 Posts

Posted - 03/01/2013 :  06:43:37  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  06:48:16  Show Profile  Reply with Quote
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/

Go to Top of Page

pvaru
Starting Member

India
5 Posts

Posted - 03/01/2013 :  07:15:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  07:18:09  Show Profile  Reply with Quote
90% of what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pvaru
Starting Member

India
5 Posts

Posted - 03/01/2013 :  07:29:03  Show Profile  Reply with Quote
969575551.93 is the figure derived from (sum of YTD entire sale *90%)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000