CODEWITHSUNDEEP
phpmysqlsql
Asfandyar Khan
Asfandyar Khan

Reputation: 1738

Sql query to get records that match the criteria

I want to calculate the following things for a specific company i.e is 13:

  1. Total credits
  2. Used credits
  3. used calls

I have my data like in the below image. enter image description here

I have calculated the sum of credit_amount (total credit) which is 200+100+5000+100 = 5400

Now i want to calculate the Used Credit which should be like this

200 + 100 + 5000 + 70 = 5370

The value 70 came because it is from the last package which is credit_amount 100 - credit_remaining 30 = 70.

As u can see the recharge_date if that date is greater than current date then package is active. so current active package is first one in above screenshot i have attached.

How can i find the Used credits?

Total calls used be in same scenario.. it will be

200 / 0.24 = 833.33

100 / 0.24 = 416.667

5000 / 0.20 = 25000

70 / 0.30 = 233.33

So the used calls will be the sum of above which is approximately 26483.33

Here is my query code which is not giving the values that i need:

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

Upvotes: 3

Views: 1118

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

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 works for company_id = 13. If you calculate same logic for all company_id simply remove that WHERE condition and put GROUP BY company_id. You will get calculated records for all company.

Try above query.

Here I had simply used CASE WHEN to specify which records have remaining credit.

And using that query you can finally calculate your total_sms,used_credit,total_credit .

Upvotes: 4

Vishal Tyagi
Vishal Tyagi

Reputation: 580

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;

Please see if this helps.

Upvotes: 0

Related Questions