How to create complex queries directly via select statement instead of creating functions

Question

Below is the method which is working fine, but how do I convert it and use it directly inside select statement to get the desired results.

SET GLOBAL log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS DayCount;
DELIMITER |

CREATE FUNCTION DayCount( d1 DATE, d2 DATE, daynum SMALLINT ) RETURNS INT
BEGIN
 DECLARE days INT DEFAULT 0;
  IF D1 IS NOT NULL AND D2 IS NOT NULL THEN
    WHILE D1 <= d2 DO
      BEGIN
        IF DAYOFWEEK(d1) = daynum THEN
          SET days=days+1;
        END IF;
        SET d1 = ADDDATE(d1, INTERVAL 1 DAY);
      END;
END WHILE;
END IF;
RETURN days;
END;
|
DELIMITER;

SELECT (daycount('2017-02-05','2017-02-20',7)) + (daycount('2017-02-05','2017-02-20',1)) AS 'Weekends';

For above query, I need a way to pass 7 & 1 dynamically to be able to pass non-workdays as well and remove those days from main query to calculate resolution and response time, but no function at all, direct select statement way of doing it.

UPDATED:

I have a query below, where I'm calculating time between closeDate and edate where I need to exclude non-workdays, the non-workday list is dynamic, it could be from 1 to 7 (Sunday to Saturday), my requirement is to exclude non-workdays and calculate average via mysql query itself instead of manipulating things later on via PHP:

SELECT `complains`.`id`,
DATE (edate) AS _date,
AVG((UNIX_TIMESTAMP(closeDate) - UNIX_TIMESTAMP(edate))) AS _seconds,
COUNT(*) AS totalCases
FROM `complains`
WHERE (
    (`complains`.`govt_id` = '22')
    AND (
        `complains`.`edate` BETWEEN DATE ('2016-01-01')
            AND DATE ('2017-01-01')
        )
    )
AND (`complains`.`status` = 2)
GROUP BY `_date`
ORDER BY `_date`

So workdays must be excluded between different fields i.e closeDate and edate, where edate is entrydate of a case. I'm basically calculating average resolution time of cases.

SQL Fiddle link to sql fiddle

Answer 1

you can achieve what you want with an aggregation function, in particular count :

SELECT count(*) AS dayscount
FROM table
WHERE (date BETWEEN d1 AND d2) AND DAYOFWEEK(date)=daynum

Pay attention that a function is something to be used inside select statements like the DAYOFWEEK function above, they're not meant to replace select statements or vice-versa, that's because functions don't require any table, in the select statement above I had to put FROM table but in your function there is no specific table.

I would agree that it may be better to not have a function for what you want and define them when we want to transform some column(s) into something(like parsing a string column into integer or getting the difference between two dates etc).

By the way count is a function , in particular an aggregation function just like AVG,SUM,MAX,etc, your function is just a count with condition.

EDIT

regarding your update, the short answer is : you can't do it without declaring a function.

Replacing a function with a select means that data is present in some table , if I want to exclude workdays from edate to closedate then those workdays must be stored but where are they ? in what table ? for this a function is a must.

But your function doesn't have to do every thing, the query can be written like this :

SELECT id,
DATE (edate) AS _date,
AVG((NONWORKDAYSCOUNT(edate,closedate,workdaysnum))) AS _seconds,
COUNT(*) AS totalCases
FROM complains
WHERE (
    (govt_id = '22')
    AND (
        edate BETWEEN DATE ('2016-01-01')
            AND DATE ('2017-01-01')
        )
    )
AND (status = 2)
GROUP BY _date
ORDER BY _date

I'll leave NONWORKDAYSCOUNT to you , it's almost the same as DayCount you have but you need to pass daynum as "array-like" type(mysql doesn't have arrays) for which you can see this question, in particular the FIND_IN_SET is the function you need, also DayCount include the days passed in daynum not excludes them(this is either correct or wrong depending on whether you interpret daynum as workdays or non-workdays), it also needs to convert its result into seconds.

By the way you mentioned that you want to do it in mysql instead of php for performance, while your case is right, in general we don't prefer to put the CPU load on our databases and you really should measure before judging the performance.

P.S: you don't need to mention the table name everytime you want to mention a column unless you want to give a different name to your table or you join two(or more) tables which have shared columns.

You also don't need to wrap column names inside "``"