telecom project...............

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

SQL Server Development (2000)

telecom project...............

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 02:32:08

hi

i am working on a telecom project...
a subscriber will be on a rate scheme and he will be having minutes.. now my problem i have the usage of the subscriber based on the rate rank and i have to take out the free minutes... and i have a order..

example..

total usage 850 min
break up
Peak 450
off-peak 325
super off-peak 75

free minutes 550

now i have to write a qry which will take off the free minute
order is off-peak 550-325 = 225
peak 225-450 = 225
super off-peak + 75

total minutes to charge is 300 minutes...

hope i have done my job of explain it clear..
now it ur turn guys....

thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 02:34:59

sorry guys ...
i need the data in break up the remaining min to be charged....

ex:
peak 225
super off-peak 75

thanks

======================================
Ask to your self before u ask someone

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-05 : 03:29:46

How are the minutes stored:

Row-wise:

Type         Minutes
Peak         450
Off-Peak     325
Super-Off    75

or Column-wise:

Peak    OffPeak    SuperOffPeak
450     325        75

If they're stored row-wise you'll have to use GROUP BY with a CASE statement to subtract each appropriate billing type:

SELECT subscriberID, Sum(FreeMinutes)-
Sum(CASE billType WHEN 'Peak' THEN Minutes ELSE 0 END)-
Sum(CASE billType WHEN 'Off-Peak' THEN Minutes ELSE 0 END)-
Sum(CASE billType WHEN 'Super Off-Peak' THEN Minutes ELSE 0 END) AS TotalMinutes
FROM myTable
GROUP BY subscriberID

I don't know your table structure, but the idea is that each subscriber has an ID and each ID will be summarized by each billing type (Peak, Off-Peak, etc.) The CASE statements will allow each type to be grouped and subtracted correctly.

You'll have to provide all of your table structures if you need another solution.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 04:03:06

the data is store in rows..

structure

account_id totalusage raterank
47352 137 0
47352 130 1
47352 75 2

where 0 is off-peak , 1 is peak 2 is super off-peak

i will try out u query..
thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 04:39:49

the query look ok but a small problem is when we subtracted the
off-peak the reminder is carried to Peak is avaible and if reminder still then for super off-peak .... how can that be given

ie. free minutes- off-peak
if reminder then reminder - peak
if reminder reminder -super off-peak
if not sum the super off-peak and the left out from peak and price them induvially...........

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 20:42:40

some on guys i am wating for u suggetions and solutions...
help me out
thanks

======================================
Ask to your self before u ask someone

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-05 : 21:18:08

quote:
reminder

- do you mean remainder?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 21:23:07

reminder is the remaining value after the subtracted

order is off-peak 550-325 = 225
225 is remaining.....

peak 225-450 = 225
need to charge 225 of peak

super off-peak + 75

total of 300min to be charge

thanks

======================================
Ask to your self before u ask someone

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-05 : 21:45:54

quote:

account_id totalusage raterank
47352 137 0
47352 130 1
47352 75 2

khalik, sorry for more questions but is this how you want your result stored, or is this how you already have the data?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-05 : 21:54:09

its alright....
using a query i am able to reterive the data is following order...

account_id totalusage raterank
47352 137 0
47352 130 1
47352 75 2

the problem is taking off the free min....

thanks

======================================
Ask to your self before u ask someone

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-02-06 : 02:29:58

hmmm... I'm still not exactly sure what you want, and someone will probably see a much easier way to do this - but I'll have a go...

so you have a recordset like:
userid minutes raterank
47352 325 0
47352 450 1
47352 75 2
47533 300 0
47533 800 1
47533 100 2

I think I am halfway there with:
select Offpeak.userid, OffPeak.minutes-550,Peak.minutes -550, super.minutes - 550 FROM
(select userid, sum(minutes) as minutes from tA where raterank <1 group by userid) OffPeak
inner join
(select userid, sum(minutes) as minutes from tA where raterank <2 group by userid) Peak
on OffPeak.userid = Peak.userid
inner join
(select userid, sum(minutes) as minutes from tA where raterank <3 group by userid) Super
on Peak.userid = super.userid

which produces
userid o_peak peak sup
47352 -225 225 300
47533 -250 550 650

Is that the kind of result set you want?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-06 : 03:31:26

that great thanks a lot...
u solved it... but a small problem
the recordset what i get is based on a query which has joins...

so when i write a query like what u suggested... it gives

Server: Msg 1016, Level 15, State 2, Line 13
Outer join operators cannot be specified in a query containing joined tables.
Server: Msg 170, Level 15, State 1, Line 39

any option other than temp tables... inserting the date
cant i join all 3 .......

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-06 : 03:50:04

sorry sorry... i think u have a problem in that query the result look ok but it is not...

userid minutes raterank
47352 325 0
47352 450 1
47352 75 2
47533 300 0
47533 800 1
47533 100 2

quote:

select Offpeak.userid, OffPeak.minutes-550,Peak.minutes -550, super.minutes - 550 FROM
(select userid, sum(minutes) as minutes from tA where raterank <1 group by userid) OffPeak
inner join
(select userid, sum(minutes) as minutes from tA where raterank <2 group by userid) Peak
on OffPeak.userid = Peak.userid
inner join
(select userid, sum(minutes) as minutes from tA where raterank <3 group by userid) Super
on Peak.userid = super.userid

450-550=-100
325-550=-225
75-550=-475
which is not u result
i have total 550 free min..

logic should be like
free 0
550 - 325= 225 freemin remain

1 remaining free
450- 225 = 225 hour remain for charge

2 free
75 -0 =75

so total chargeble min are
rank min
0 0
1 225
2 75
300 min to charge

i hope i made it clear..... this time

======================================
Ask to your self before u ask someone

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-06 : 10:45:08

How is this system running?
This is a billing function - are your bills run on-line per user or are they batch jobs. i.e. is this query to be run on-line many times per user or is it run once to produce the bills?

If it run once or overnight as a batch job then I would keep the format simple as it is the sort of thing that will probably need to change (at short notice).
Even if you run it on-line per user you might do something like this as it will be resonably quick for a single user and still flexible.

something like - for your users create a table
FreeCalc
user, minutes, type, rank, FreeMins

have another table (or temp table)
FreeLeft
UserId, freeminsleft, rank

populate FreeCalc with user, minutes, type, 0, 0
then
update FreeCalc set rank = rt.rank
from ratetype rt where rt.type = FreCalc.type

insert FreeMins
select UserId, 600, min(rank)
from FreeCalc
group by UserId

while @@rowcount <> 0
begin
update FreeCalc
set FreeMins = case when FreeMinsLeft >= FreeMins then FreeMinsLeft - FreeMins else FreeMins end
from FreeLeft
where FreeCalc.UserID = FreeLeft.UserID
and FreeCalc.rank = FreeLeft.rank
and FreeLeft.freeminsleft = 0

update FreeLeft
set rank = FreeCalc.rank ,
FreeMinsLeft = FreeMinsLeft - FreeMins
from FreeeCalc
where FreeCalc.UserID = FreeLeft.UserID
FreeCalc.rank = (select min(rank) from FreeCalc fc where fc.UserID = FreeCalc.UserID and fc.FreeMins = 0)
and FreeMinsLeft <> 0)

end

You can then get the billing minutes from FreeCalc.

This will not be especially quick but it will be easy to update and you can tailor the amount of free mins to each user - hold over free mins from previous billing period, allocate different ranks to each user...

If you have more than a few hundred thousand users you shouldn't be running a single query anyway but will need to break it down into batches.
The really slow thing of course will be getting the aggreagte minutes per user.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-06 : 11:12:45

How is this system running?
This is a billing function - are your bills run on-line per user or are they batch jobs. i.e. is this query to be run on-line many times per user or is it run once to produce the bills?

If it run once or overnight as a batch job then I would keep the format simple as it is the sort of thing that will probably need to change (at short notice).

something like - for your users create a table
FreeCalc
user, minutes, type, rank, FreeMins

have another table (or temp table)
FreeLeft
UserId, freeminsleft, rank

populate FreeCalc with user, minutes, type, 0, 0
then
update FreeCalc set rank = rt.rank
from ratetype rt where rt.type = FreCalc.type

insert FreeMins
select UserId, 600, min(rank)
from FreeCalc
group by UserId

while @@rowcount <> 0
begin
update FreeCalc
set FreeMins = case when FreeMinsLeft >= FreeMins then FreeMinsLeft - FreeMins else FreeMins end
from FreeLeft
where FreeCalc.UserID = FreeLeft.UserID
and FreeCalc.rank = FreeLeft.rank
and FreeLeft.freeminsleft = 0

update FreeLeft
set rank = FreeCalc.rank ,
FreeMinsLeft = FreeMinsLeft - FreeMins
from FreeeCalc
where FreeCalc.UserID = FreeLeft.UserID
FreeCalc.rank = (select min(rank) from FreeCalc fc where fc.UserID = FreeCalc.UserID and fc.FreeMins = 0)
and FreeMinsLeft <> 0)

end

This will not be especially quick but it will be easy to update and you can tailor the amount of free mins to each user - hold over free mins from previous billing period, allocate different ranks to each user...

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-06 : 11:12:48

How is this system running?
This is a billing function - are your bills run on-line per user or are they batch jobs. i.e. is this query to be run on-line many times per user or is it run once to produce the bills?

If it run once or overnight as a batch job then I would keep the format simple as it is the sort of thing that will probably need to change (at short notice).
Even if you run it on-line per user you might do something like this as it will be resonably quick for a single user and still flexible.

something like - for your users create a table
FreeCalc
user, minutes, type, rank, FreeMins

have another table (or temp table)
FreeLeft
UserId, freeminsleft, rank

populate FreeCalc with user, minutes, type, 0, 0
then
update FreeCalc set rank = rt.rank
from ratetype rt where rt.type = FreCalc.type

insert FreeMins
select UserId, 600, min(rank)
from FreeCalc
group by UserId

while @@rowcount <> 0
begin
update FreeCalc
set FreeMins = case when FreeMinsLeft >= FreeMins then FreeMinsLeft - FreeMins else FreeMins end
from FreeLeft
where FreeCalc.UserID = FreeLeft.UserID
and FreeCalc.rank = FreeLeft.rank
and FreeLeft.freeminsleft = 0

update FreeLeft
set rank = FreeCalc.rank ,
FreeMinsLeft = FreeMinsLeft - FreeMins
from FreeeCalc
where FreeCalc.UserID = FreeLeft.UserID
FreeCalc.rank = (select min(rank) from FreeCalc fc where fc.UserID = FreeCalc.UserID and fc.FreeMins = 0)
and FreeMinsLeft <> 0)

end

You can then get the billing minutes from FreeCalc.

This will not be especially quick but it will be easy to update and you can tailor the amount of free mins to each user - hold over free mins from previous billing period, allocate different ranks to each user...

If you have more than a few hundred thousand users you shouldn't be running a single query anyway but will need to break it down into batches.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-06 : 20:17:24

thanks nr.... i will try out u solution..
i am not doing billing... my job is to recommand a better rateplan to the subscriber... so i have to try with diffrent rateplans and recommand one so i need to do what if analyisis... if billing i can run a batch job... but this is not

this system will be a online and user do the what if analysis and select a better rate plan..........

thanks for u help ...

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-02-07 : 00:04:07

thanks a lot ToddV (tvangilder@harrisinteractive.com)

my friend was not able to post in fourms so he mail me the solutions.. and it solves my problem...
thanks a lot toddV it really good so nice of u..

the solution by ToddV..

Create table #Usage (account_id INT,
totalusage INT,
raterank INT)

Create Table #FreeMinutes(
account_id INT,
FreeMinutes INT)

Insert #usage VALUES(47352, 137, 0)
Insert #usage VALUES(47352, 130, 1)
Insert #usage VALUES(47352, 75, 2)

Insert #usage VALUES(47353, 291, 0)
Insert #usage VALUES(47353, 130, 1)
Insert #usage VALUES(47353, 75, 2)

Insert #usage VALUES(47354, 285, 0)
Insert #usage VALUES(47354, 130, 1)
Insert #usage VALUES(47354, 75, 2)

INSERT #FreeMinutes Values(47352,290)
INSERT #FreeMinutes Values(47353,290)
INSERT #FreeMinutes Values(47354,290)

SELECT A.Account_ID,
CASE WHEN FreeMinutes - PeakMinutes < 0 THEN ABS(FreeMinutes -
PeakMinutes) ELSE 0 END
AS PeakCharge,
CASE WHEN FreeMinutes - PeakMinutes < 0 THEN OffPeakMinutes
ELSE (CASE WHEN FreeMinutes - PeakMinutes - OffPeakMinutes < 0
THEN ABS(FreeMinutes - PeakMinutes- OffPeakMinutes) ELSE 0 END)
END
AS OffPeakCharge,
CASE WHEN FreeMinutes - PeakMinutes - OffPeakMinutes < 0 THEN
SuperOffPeakMinutes
ELSE (CASE WHEN FreeMinutes - PeakMinutes - OffPeakMinutes -
SuperOffPeakMinutes < 0
THEN ABS(FreeMinutes - PeakMinutes- OffPeakMinutes -
SuperOffPeakMinutes) ELSE 0 END)
END
AS SupperOffPeakCharge
FROM (SELECT account_id,
Sum(CASE raterank WHEN 0 THEN totalusage ELSE 0 END) AS PeakMinutes,
Sum(CASE raterank WHEN 1 THEN totalusage ELSE 0 END) AS
OffPeakMinutes,
Sum(CASE raterank WHEN 2 THEN totalusage ELSE 0 END) AS
SuperOffPeakMinutes
FROM #Usage
GROUP BY account_id) AS A
JOIN #FreeMinutes B ON A.Account_ID = B.Account_ID

bye
great about u.....

======================================
Ask to your self before u ask someone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources