Sql query to get records matching criteria
I want to calculate the following things for a specific company i.e. 13
:
- Total credits
- Loans used
- used calls
I have data like in the picture below.
I have calculated the sum of credit_amount ( total credit
) which is200+100+5000+100 = 5400
Now I want to calculate Used Credit
which should be like this
200 + 100 + 5000 + 70 = 5370
The value 70
happened because it is from the last packet which is credit_amount
100 - credit_remaining
30 = 70.
As u can see recharge_date
if this date is greater than the current date, then the package is active. so the currently active package is the first one in the screenshot I added.
How can I find used credits?
The common calls used must be in the same script .. this will
200 / 0.24 = 833.33
100 / 0.24 = 416.667
5000 / 0.20 = 25000
70 / 0.30 = 233.33
So, the used calls
sum will be above, which is approximately equal to26483.33
Here is my request code that doesn't give the values I want:
SELECT (SELECT Sum(credit_amount)
FROM `company_credit`
WHERE `company_id` = 13) AS total_credit,
Sum(credit_amount / rate) AS used_calls,
Sum(credit_amount) AS used_credit
FROM `company_credit`
WHERE `company_id` = 13
AND recharge_date < Now()
ORDER BY `id` DESC
source to share
SELECT Sum(credit_amount) AS total_credit,
Sum(CASE WHEN recharge_date < Now() THEN (credit_amount / rate) ELSE ((credit_amount-credit_reamining) / rate) END ) AS total_sms,
Sum(CASE WHEN recharge_date < Now() THEN credit_amount ELSE (credit_amount-credit_reamining) END ) AS used_credit
FROM `company_credit`
WHERE `company_id` = 13
ORDER BY `id` DESC
this query will work for . If you are calculating the same logic for the entire company_id, then just remove that condition and put . You will receive calculated data for the entire company. company_id = 13
WHERE
GROUP BY company_id
Try to execute the request.
Here I just used to indicate which records have credit. CASE WHEN
And using this query, you can finally compute . total_sms,used_credit,total_credit
source to share
SELECT SUM(credt_amount) ,
SUM(
CASE
WHEN cr_date >
(SELECT cur_date FROM tbl_eod_bod_stat
)
THEN credt_amount - credt_rem
ELSE credt_amount
END ) AS Used_Credit ,
SUM(
CASE
WHEN cr_date >
(SELECT cur_date FROM tbl_eod_bod_stat
)
THEN (credt_amount - credt_rem)/rate
ELSE ( credt_amount / rate)
END) AS rateDiv
FROM tbl_company_2;
See if this helps.
source to share