Sitansu Sitansu - 1 year ago 92
MySQL Question

How to calculate the percentage in Mysql?

I have table LabOrder with column Account, OrderNo, Createddate, this table one Account contains multiple orderno. But my requirement is I want to fetch records for that particular account based on CreatedDate of comparing current week with one previous week.

Let's suppose account created 100 orders in previous week and created orders in current week is 50 then we want to notify that this particular account current week order is less than or equal to previous week orders. I our business requirement we send email to that particular account whose current week order count is less that 50% to previous week orders. We have to notify that account.

Calculate the difference with current week orders count or previous week orders ?

How to achieve this requirement which one better to fetch records.

  1. Using simple query

  2. Create view

  3. Create stored procedure

I have following table data :

Table Name : LabOrder

Account orderNo CreatedDate
101 13 2016-08-2
102 56 2016-08-9
103 79 2016-08-24

I want data like below. The stored procedure will compare the orders for the current week and previous week and if that 50 % less then it will gives the account with has 50 % less volumes.

Account CurrentWeekOrder PreviousWeekOrder Difference(%)
101 50 10 -40
102 60 180 120 -> This has to be notified
103 30 25 5 -> No Need to notified

How to create a stored procedure for that above details? Please help me I'm a Java developer and I didn't create any stored procedure in MySql before. This is my first attempt.

I'm trying to create the stored procedure but I'm facing lot of issues like how to store if the query returns multiple records.:

If anyone give me the idea how to create that stored procedure if any suggestion.


Answer Source

I think stored procedure is not a good choice for your case because every time the user insert a record, the database has to update the previous records of the same week. I suggest you to use view to achieve your target. Here is my solution for you:

  1. create a view with the following SQL

        count(*) as weekorder,
        week(createDate) as createWeek
        week(createDate) >= week(now()) - 1 ----last week and current week
    group by 
        account, week(createDate)
  2. suppose the view's name is view1, now you can query what you need:

        a.weekorder as CurrentWeekOrder,
        b.weekorder as PreviousWeekOrder,
        a.weekorder / b.weekorder * 100 as Difference
        view1 as a 
    left join 
        view1 as b on a.account = b.account and a.createWeek = b.createWeek - 1
        a.createWeek = week(now()) and
        a.weekorder / b.weekorder * 100 < 50

Hope the method above can help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download