In table DimensionAttributeValueGroupCombination , Ordinal field value 1,2,3,4 is version of advance account structure and account structure. First time it will be 1 and then it will be 2,3....
Select MA.MAINACCOUNTID,DAVGC.ORDINAL,
DALV.DIMENSIONATTRIBUTEVALUEGROUP ,DALV.DISPLAYVALUE, DALV.ORDINAL,
ROW_NUMBER()
Over (Partition
By
DALV.DimensionAttributevaluegroup
Order
by
DALV.Ordinal
asc) as
'For Sorting'
from
DimensionAttributeValueCombination
DAVC
inner
join
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
DAVGC
on
DAVC.RecID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTEVALUEGROUP
DAVG
on
DAVG.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION
inner
join
DIMENSIONATTRIBUTELEVELVALUE
DALV
on
DAVG.RECID = DALV.DIMENSIONATTRIBUTEVALUEGROUP
join MAINACCOUNT MA
on MA.RECID = DAVC.MAINACCOUNT
--where DAVG.DIMENSIONHIERARCHY = 22565421207
where DAVGC.ORDINAL not in (1)
Group
by
DALV.DIMENSIONATTRIBUTEVALUEGROUP, DALV.DISPLAYVALUE, DALV.ORDINAL,MA.MAINACCOUNTID,DAVGC.ORDINAL
