question

Askckoriya avatar image
Askckoriya asked

Rank amount distribution

DECLARE @tblRank As TABLE  (
	SrNo INT
	,Rank INT
)


DECLARE @AmtDistributionRange AS TABLE  (
	AutoID			INT IDENTITY
	,FromRank		INT
	,ToRank			INT
	,AmtPerHead		NUMERIC(18,2)
)


INSERT INTO @AmtDistributionRange (
	FromRank
	,ToRank
	,AmtPerHead
)
SELECT 1 ,10 ,100 UNION
SELECT 11 ,15 ,50


INSERT INTO @tblRank (
	SrNo
	,Rank
)
SELECT 1,1 UNION
SELECT 2,2 UNION
SELECT 3,3 UNION
SELECT 4,4 UNION
SELECT 5,5 UNION
SELECT 6,6 UNION
SELECT 7,7 UNION
SELECT 8,7 UNION
SELECT 9,9 UNION
SELECT 10,10 UNION
SELECT 11,10 


;With Cte AS (
SELECT SrNo
	,Rank
	,COUNT(Rank) OVER (PARTITION BY Rank ORDER BY Rank) Cnt
	,COUNT(SrNo) OVER() TotalCnt
 FROM @tblRank
 )


 SELECT 
	s.SrNo
	,s.Rank
	,TotalCnt
	,cpd.AmtPerHead
	,CONVERT(NUMERIC(18, 2), SUM(cpd.AmtPerHead) OVER() / s.TotalCnt) AssignedAmt
FROM Cte s
INNER JOIN @AmtDistributionRange cpd ON s.SrNo BETWEEN cpd.FromRank
		AND cpd.ToRank


this is current result and i need result like below image please help me.



tsqlrank
needresult.png (6.8 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

I think what you are tying to acheive is this?

SELECT 
  s.SrNo
  ,s.Rank
  ,TotalCnt
  ,cpd.AmtPerHead
  ,CONVERT(NUMERIC(18, 2), sum(cpd.AmtPerHead) OVER(partition by Rank) / count(*)over(partition by Rank) ) AssignedAmt
FROM Cte s
INNER JOIN @AmtDistributionRange cpd ON s.SrNo BETWEEN cpd.FromRank
		AND cpd.ToRank

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.