web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Microsoft Dynamics GP (Archived)

Top 5 Customers by sales

(0) ShareShare
ReportReport
Posted on by 110

I need to select top5. I got bad results when i coded like select top5

SELECT

d.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,

sum(d.SALES) Yearly_Total
FROM
(SELECT
s.GLPOSTDT, s.CUSTNMBR, s.CUSTNAME,
CASE s.SOPTYPE
when 3 then s.SUBTOTAL
when 4 then s.SUBTOTAL*-1
END SALES
FROM
SOP30200 s
LEFT OUTER JOIN
RM00101 c ON s.CUSTNMBR = c.CUSTNMBR
WHERE
s.VOIDSTTS = 0
AND s.SOPTYPE IN (3, 4)
AND YEAR(s.GLPOSTDT) = '2017') d
GROUP BY
d.CUSTNMBR, d.CUSTNAME

*This post is locked for comments

I have the same question (0)
  • 26P2ER Profile Picture
    1,775 on at
    RE: Top 5 Customers by sales

    Sunny - This is a canned KPI that is available in the sales folder of the reporting services framework.

    In GP 2010 SP1 the stored procedure that drives this KPI is

    dbo.seeRMTopCustomersMetric


    Cheers,

  • SandeepReddyChaganti Profile Picture
    110 on at
    RE: Top 5 Customers by sales

    i think you don't understand my question

    i need to select top5 customers

  • 26P2ER Profile Picture
    1,775 on at
    RE: Top 5 Customers by sales

    Here you go!

    select   TOP 5 TTLSLYTD as SalesYTD,  
            TTLSLLYR as SalesLastYear,  
            CUSTNMBR as CustomerNumber
    from    RM00103 with (NOLOCK)
    order by  TTLSLYTD DESC,  TTLSLLYR

  • Verified answer
    Mariano Gomez Profile Picture
    26,225 on at
    RE: Top 5 Customers by sales

    Try this:

    SELECT TOP 5 
    	d.CUSTNMBR Customer_ID,
    	d.CUSTNAME Customer_Name,
    	sum(d.SALES) Yearly_Total
    	FROM
    	(SELECT 
    	s.GLPOSTDT, s.CUSTNMBR, s.CUSTNAME,
    	CASE s.SOPTYPE
    	when 3 then s.SUBTOTAL
    	when 4 then s.SUBTOTAL*-1
    	END SALES
    FROM    SOP30200 s
    	    LEFT OUTER JOIN RM00101 c ON s.CUSTNMBR = c.CUSTNMBR
    WHERE
    	s.VOIDSTTS = 0
    	AND s.SOPTYPE IN (3, 4)
    	AND YEAR(s.GLPOSTDT) = 2017) d
    GROUP BY d.CUSTNMBR, d.CUSTNAME
    ORDER BY sum(d.SALES) desc
    
  • SandeepReddyChaganti Profile Picture
    110 on at
    RE: Top 5 Customers by sales

    Thanks. It Works

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans