CREATE PROCEDURE web_TopClientsByCompanyCode (

@topnum int,
@Code int,
@month int,
@sector int,
@monthend int

)

AS

SET ROWCOUNT @topnum

SELECT		Groups.CompanyName,
		Groups.ParentGroup,
		sum(Amounts.Turnover) AS Turnover,
		ClientSectors.Description,
		Groups.TAP,
		Groups.KAM
FROM

(SELECT max(companyname) as companyname, parentgroup, ClientSector, TAP, KAM
from companies group by parentgroup, ClientSector, TAP, KAM ) Groups

LEFT JOIN (

SELECT 		C.ParentGroup,
		[amtin loccur] AS Turnover
FROM 		Companies C
LEFT JOIN	zarinvreg ON zarinvreg.Customer = C.AccountCode
WHERE 		monthdata <= @month
AND		monthdata >= @monthend--@month-11 --this gives us 12 months
AND 		C.CompanyName <> '-'
AND 		C.CompanyName is not null
AND		Cocd = @Code
) Amounts ON Groups.ParentGroup = Amounts.ParentGroup

LEFT JOIN ClientSectors ON ClientSectors.ID = Groups.ClientSector

WHERE		Amounts.Turnover is not null
AND 		(ClientSectors.ID = @sector OR ISNULL(@sector ,'')='')

GROUP BY 	Groups.CompanyName,
		Groups.ParentGroup,
		ClientSectors.Description,
		Groups.TAP,
		Groups.KAM
ORDER BY 	Amounts.Turnover desc

SET ROWCOUNT 0




GO