Sorry about 17 rows in the expected, that was a mistake should have been in current.
When I use top 15 t1.[spend] it only returns 15 rows from 2013. I would like the TOP 15 spend amounts for each media name.
TOP 15 Current
SPEND,Medianame,clientname,2013
200.00,4BH,Automotive,2013
1864.00,4BH,Dainty,2013
1870.00,4BH,Productions,2013
7776.00,4BH,Insurance,2013
8736.00,4BH,Simplicity,2013
9328.80,4BH,Taditional,2013
12549.00,4BH,Foundation,2013
19140.00,4BH,Health,2013
64175.00,4BH,Agency,2013
770.00,101.5,Caravan ,2013
2330.00,101.5,Bay,2013
900.00,104.9,Family,2013
9600.00,180,Properties,2013
1134.00,2HD,IGA,2013
1968.00,2HD,Parable,2013
select
--TOP 15 t1.[Spend]
--[Spend]
[MediaName]
,[ClientName]
,[BookingYear]
, ROW_NUMBER() OVER(ORDER BY [Spend] DESC) As Row
from(
select
lhe.LHECRELongDesc as [MediaName]
,cast(sum(spo.SPODiscountedCost) as money) as [Spend]
,cli.CLILongDesc AS ClientName
, cld.CALCLYNumber AS BookingYear
FROM dbo.LinkHierarchyExtended AS lhe WITH(NOLOCK)
INNER JOIN dbo.ScheduleSpot AS spo (NOLOCK) ON spo.SPOID = lhe.LHESPOID
INNER JOIN dbo.ClientHierarchy AS cli WITH(NOLOCK) ON cli.CLIID = lhe.LHECLIID and cli.CLIPROID = lhe.LHEPROID
INNER JOIN dbo.Calendar AS cld WITH (NOLOCK) ON spo.SPOBookingDate30CALID = cld.CALID
where spo.SPODiscountedCost != 0 and cld.CALCLYNumber in (2013,2014)
group by lhe.LHECRELongDesc, cli.CLILongDesc,cld.CALCLYNumber
) as t1
order by [BookingYear] ,[MediaName],t1.[Spend]
When I use the row_number it is adding up all the rows that make up that record, and not the row in the result set. This data is coming out of data warehouse.
MediaNameSpendClientNameBookingYearRow
4BH,200.00,Group, 2013,11016
4BH,1864.00, Entertainm,2013,9272
4BH,1870.00, Productions, 2013,9268
4BH,7776.00, Insurance,2013,6789
4BH,8736.00,Simplicity,2013,6555
4BH,9328.80,Traditional,2013,6404
4BH,12549.00, Foundation,2013,5769
4BH,19140.00,Health,2013,4902
4BH,64175.00,Pensions, 2013,2558
101.5,770.00, Industries, 2013,10243
101.5,2330.00, Corportion,2013,8951
104.9,900.00, Party,2013,10103
180,9600.00,Properties,2013,6336
2HD,1134.00,IGA, 2013,9862
2HD,1968.00,Productions, 2013,9200
2HD,2772.00,IBA, 2013,8699