August 5, 2014 at 6:27 am
Hi,
My table structure having bellow format :
StateProductIDSales
ApP1 80
App2 10
App3 10
TnP1 25
Tnp2 50
Tnp3 25
Mpp1 20
Mpp2 70
Mpp3 10
Required output is :
State ProductID sales
Ap p1 80
Tn p2 50
Mp p3 70
Regards,
Narendra
August 6, 2014 at 4:05 pm
Looks like your example data is wrong. Without an explanation of the rules, this is as close as I could get:
SELECT x.*, y.productID
FROM
(SELECT xState
, MAX(Sales) AS MaxSales
FROM salesData
GROUP BY xState) X
INNER JOIN salesData Y ON X.xState=Y.xState
AND X.MaxSales=Y.Sales
Here's the result I got:
xStateMaxSalesproductID
Ap80P1
Mp70p2
Tn50p2
August 8, 2014 at 4:11 am
Hi,
I required output in mdx not in sql server . Any how i got the solution FYI
WITH SET Top10Cust AS
[DimGeographyBuyer].[State].[State]
set topP as
Generate( {Top10Cust}, CrossJoin( {[DimGeographyBuyer].[State].CurrentMember},
TopCount([DimProduct].[MaterialId].[MaterialId].Members, 1, ([Measures].[DistributorSalesSum]))))
SELECT
{([Measures].[DistributorSalesSum]) } ON COLUMNS ,
non empty {topP}
ON ROWS
FROM (SELECT ( { [MarketYear].[MarketYearHierarchy].[MARKETYEAR].&[2014] } ) ON COLUMNS
FROM
[MyCube])
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply