Calculating Total sum after giving Discounts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating Total sum after giving Discounts

Author

Topic

nivaskhan
Starting Member

17 Posts

Posted - 2002-07-10 : 01:17:30

Dear all,
I have a table as follows.
I want to get the sum of points for each login after giving some discount of say 500 points for each.But the discount will be given based on the rank of individual Login.

Login Rank Points
AA 1 50
AA 3 150
AA 5 25
BB 1 50
BB 2 150
BB 3 550
BB 4 325
BB 5 275
CC 3 250
CC 4 500
CC 5 350

Suppose BB has a total of 1350 points i have to give him a discount of 500 points,it will be given based on the rank he has obtained

BB 1 50 - fully discounted
BB 2 150 - fully discounted
BB 3 550 - partially discounted(300 points disconnted & 250 points kept for accounting)
BB 4 325 - kept for accounting
BB 5 275 - kept for accounting

Login TotalPoints(Accounting)
BB 850

So,in total BB has 850 points to be kept for accounting.And i want this points(850) to be split based on rank .Such as

Login TotalPoints(Accounting) Rank < 3 Rank >= 4
BB 850 250 600

I need to get the individual points and their split up grouped by rank for all the logins as

Login TOTAL POINTS Rank < 3 Rank >=4
AA 0 0 0
BB 850 250 600
CC 600 0 600

Thanks in advance
Nivas

Regards,
Nivas

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-10 : 05:58:16

This may help:

declare @tblA table(Login varchar(10), Rank int, Points int)
insert @tblA values('AA', 1, 50 )
insert @tblA values('AA', 3 ,150 )
insert @tblA values('AA', 5 ,25 )
insert @tblA values('BB', 1 ,50 )
insert @tblA values('BB', 2 ,150 )
insert @tblA values('BB', 3 ,550 )
insert @tblA values('BB', 4 ,325 )
insert @tblA values('BB', 5 ,275 )
insert @tblA values('CC', 3 ,250 )
insert @tblA values('CC', 4 ,500 )
insert @tblA values('CC', 5, 350 )

select Login, TotalPoints = sum(RankedPoints) ,
RankLessThan3 = (SELECT sum(points) FROM @tblA WHERE RANK <3 AND Login = tA.Login),
RankGreatThanEqual4 = (SELECT sum(points) FROM @tblA WHERE Login = tA.Login AND RANK >=4)
from
(
select Login, rank, RankedPoints = CASE Rank
WHEN 1 THEN 0
WHEN 2 THEN 0
WHEN 3 THEN sum(points) - 300
WHEN 4 THEN sum(Points)
WHEN 5 THEN sum(Points)
end
from @tblA
group by Login , rank
) as tA
group by Login

nivaskhan
Starting Member

17 Posts

Posted - 2002-07-10 : 06:56:08

Thanks for your response.

But your output is
Login TotalPoints RankLessThan3 RankGreatThanEqual4
---------- ----------- ------------- -------------------
AA -125 50 25
BB 850 200 600
CC 800 NULL 850

But What i need is
Login TotalPoints RankLessThan3 RankGreatThanEqual4
---------- ----------- ------------- -------------------
AA 0 0 0
BB 850 250 600
CC 600 0 600

And moreover ur substracting 300 points againt rank 3.I have to subtract 300 points when the total sum of points reaches 300 and have the remaining points grouped into rank.

I dont know when the sum of points reaches 300 mark and then get the remaining sum.

Regards,
Nivas

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-10 : 07:55:15

How about breaking it into CASE statement with 2 parts?

CASE (total-discount)
CASE > 0, then = total-discount
CASE <= 0, then = 0
END CASE

The format of the statement isn't right...(I'm rushing here)....but you should be able to see what I'm at....

the sample given by yellowbug also is hard-coded for a particular score (300) and rank of 3 / 4.....whereas the solution i think you are after is something more flexible....one that would cope with any discount amount and any rank....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources