July 31, 2014 at 12:43 am
Hi,
I will like to either return only 15 clients with the highest 'spend' amount by Media or all the rows if there is less than 15. I can't think of a way to do this, so i thought of using Row_number then having <=15 on the row count.
Current results
Medianame,Spend,clientname,bookingyear
4BH,200, aaaa, 2013
4BH,1864,sdsdsds,2013
4BH,1870,bbbbbb,2013
4BH,7776,nnnnnn,2013
4BH,8736,dfdfdf,2013
4BH,9328,aasss,2013
4BH,12549,mmmnnn,2013
2HD,12121,mmmmm,2013
2HD,12111,swsws,2013
2HD,1671,shhhj,2013
2HD,4545,uiuiuuu,2013
2HD,121,ddfdf,2013
2HD,1221,hgh,2013
2HD,12181,mm,2013
2HD,82171,mbn,2013
2HD,62121,bb,2013
2HD,42121,kjhg,2013
2HD,32121,bfv,2013
2HD,2121,sssds,2013
2HD,2721,vfcdf,2013
2HD,121,bghjum,2013
2HD,210,kikujuj,2013
2HD,721,dcdff,2013
Expected results
Medianame,Spend,clientname,bookingyear,rowcount
4BH,200, aaaa, 2013,1
4BH,1864,sdsdsds,2013,2
4BH,1870,bbbbbb,2013,3
4BH,7776,nnnnnn,2013,4
4BH,8736,dfdfdf,2013,5
4BH,9328,aasss,2013,6
4BH,12,mmmnnn,2013,1
2HD,121,mmmmm,2013,2
2HD,191,swsws,2013,3
2HD,1671,shhhj,2013,4
2HD,4545,uiuiuuu,2013,5
2HD,5521,ddfdf,2013,6
2HD,6221,hgh,2013,7
2HD,12181,mm,2013,8
2HD,82171,mbn,2013,9
2HD,92121,bb,2013,10
2HD,42121,kjhg,2013,11
2HD,32121,bfv,2013,12
2HD,2121,sssds,2013,13
2HD,2721,vfcdf,2013,14
2HD,121,bghjum,2013,15
2HD,210,kikujuj,2013,16 (would only appear if the spend value was in highest 15 for that media name)
2HD,721,dcdff,2013,17 (would only appear if the spend value was in highest 15 for that media name)
select
[MediaName]
,[Spend]
,[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],Spend
Hope this makes sense and If anyone can help thanks a lot.
July 31, 2014 at 12:56 am
Your expected results have more than 15 rows. How come?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2014 at 1:45 am
Do you mean something like this?
😎
select * from (
select
[MediaName]
,[Spend]
,[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 ) as x where x.Row <= 15
order by [BookingYear] ,[MediaName],Spend
Quick question, why the NOLOCK hints? Do you have any stats proving that they are needed?
July 31, 2014 at 1:46 am
Eirikur Eiriksson (7/31/2014)
Do you mean something like this?😎
select * from (select
[MediaName]
,[Spend]
,[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 ) as x where x.Row <= 15
order by [BookingYear] ,[MediaName],Spend
Quick question, why the NOLOCK hints? Do you have any stats proving that they are needed?
If it's that simple, why not just order by Spend and use TOP 15?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2014 at 6:18 pm
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
July 31, 2014 at 10:48 pm
Thanks for your help, I got it.
select
--TOP 15 t1.[Spend]
[MediaName]
,[Spend]
,[ClientName]
,[BookingYear]
, ROW_NUMBER() OVER(PArtition by [MediaName] 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)
group by lhe.LHECRELongDesc, cli.CLILongDesc,cld.CALCLYNumber
) as t1
order by [BookingYear] ,[MediaName],t1.[Spend]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply