SQL query return total of days in a month on two dates

Question

I have the following tables:

staff:

• id_funcionario
• other information...

historical:

• id_funcionario
• data_input
• dating

I tried some querys but I couldn’t get what I wanted. What I need is, after the user has chosen a month, May for example, I need to return the amount of days that that employee work only in May.

For example, if the employee John has worked from 25/05/2014 to 03/06/2014 (the database is in the right format) and the user has chosen to view the employee history John in the month of May, appear: 5 days. Someone could help with this query?

SELECT DATEDIFF
(
    (SELECT historico.entrada FROM historico WHERE funcionario_id = 12) ,
    (SELECT historico.saida FROM historico WHERE funcionario_id = 12)
)

What I’m struggling with is how to return just a month, in which place I would put the WHERE month(campo) = 05?

Answer 1

With the change of strategy, I made the SQL edition to calculate so:

• If the months are different it makes two calculation of the month of entry and the month of exit.

• If the months have differences of more months type month 05 until month 07 you could then take the middle and make a calculation in PHP code to find out type month 6 has 30 days equal is in SQL 1, 3, 5, 7, 8, 10, 12 has 31 days, month 2 depending on the year may have 28 or 29 days (take the ano % 2 == 0 if true, the expression has 29 days if not 28 days), and the other months have 30 days.

• If the month reported belongs to the entry take the field DIFERENCA_MENTRADA if the month given belongs to the month of departure take DIFERENCA_MSAIDA.

---

SELECT IF(mentrada <> msaida, 
       (
            CASE mentrada 
            WHEN (1 OR 3 OR 5 OR 7 OR 8 OR 10 OR 12) THEN
                DATEDIFF(date_format(concat(yentrada,'-', mentrada,'-', 31), '%Y-%m-%d'), data_entrada)             
            WHEN (2) THEN
                DATEDIFF(date_format(concat(yentrada,'-', mentrada,'-', IF(yentrada % 4 = 0,29,28)), '%Y-%m-%d'), data_entrada)
            ELSE
                DATEDIFF(date_format(concat(yentrada,'-', mentrada,'-', 30), '%Y-%m-%d'), data_entrada)             
            END         
        ), DATEDIFF(data_saida, data_entrada)) as diferenca_mentrada, 
        IF (mentrada <> msaida,
            DATEDIFF(data_saida, date_add(date_format(concat(ysaida,'-', msaida,'-1'), '%Y-%m-%d'), INTERVAL -1 DAY)),0) as diferenca_msaida,   
        id_funcionario, 
        data_entrada, 
        data_saida,
        mentrada,yentrada,
        msaida,ysaida
FROM  (
SELECT 
    id_funcionario,
    data_entrada, 
    data_saida, 
    month(data_entrada) mentrada, 
    year(data_entrada) yentrada,
    month(data_saida) msaida,
    year(data_saida) ysaida
FROM historico
) as historico WHERE (mentrada = 6 or msaida = 6) and yentrada = 2014 AND id_funcionario = 1

Online Example: Sqlfiddle

Month 5: Then take the difference

Month 6: Then take the difference

Answer 2

Well, I’m not sure I understand your doubt, but come on...

_QUERY

SELECT DATEDIFF( '2014-06-03' , '2014-05-25' ) AS DIAS , ID FROM MYTABLE WHERE IDFUN = 1

_RESULT

array( 'DIAS' => 9 , 'ID' => 1 )

Counted 9 days as it was the period between 2014-06-03 and 2014-05-25.
It is necessary to format the final date.

---

If the employee has worked from 25/05/2014 to 03/06/2014 and the user has chosen to view the employee’s history in the month of May, appears: 5 days.

You cannot accept the end date in the format 03/06/2014.
I recommend format for the last day of the month of the start date to have the query exactly by the selected month: Inicio: 25/05/2014 | Final: 31/05/2014

---

Playing the end date to the last day of the month for the start date:

echo date( "t-m-Y" , strtotime( '2014-08-15' ) );

_RESULT

31-08-2014

Just use the formatted date in your QUERY

Answer 3

I believe the problem lies in the data structure adopted. If you want a truly functional and realistic work history, you should have a record per day, where next to the record would have the time of entry and the time of departure.

Storing the history for periods as you presented in your question will give you work and many problems.

But one possible solution is the following

SELECT 
    DATEDIFF(
        IF( `saida` <= '2014-05-31', `saida`, '2014-05-31' ),
        IF( `entrada` >= '2014-05-01', `entrada`, '2014-05-01' )
    )
FROM `historico`
WHERE 
    `saida` <= '2014-05-31'
OR  `entrada` >= '2014-05-01'
WHERE `funcionario_id` = "1";

Avoid Mysql functions to calculate the dates used in filters, such as NOW(), CURDATE(), etc, because using them you will have a loss of performance. These functions make it impossible for Mysql to cache queries.

As quoted in the @papa-charlie reply, to get the last day of a given month, use in your PHP code:

date( "t-m-Y" , strtotime( '2014-05-25' ) );

If the structure was in the form I mentioned above, to get the result you want, just filter the desired period and count the number of records.

SELECT COUNT( `date` )
FROM historico
WHERE `date` between '2014-08-01' AND '2014-10-01'

In your current structure, you may experience frequent problems with this calculation of dates, whether for annual, monthly or daily reports. Consider changing your system structure now if it is still in the early stages.

Answer 4

I arrived at the expected result by checking whether the data_saida is in the same month of the date entered through a IF of LAST_DAY combined with a DATE_DIFF:

SELECT *, 
last_day(data_entrada) as fim_mes,
DATEDIFF(IF(data_saida > last_day(data_entrada), last_day(data_entrada),data_saida), data_entrada) AS dias_trabalhados_mes 
FROM historico;

With this, just match at the end a WHERE with the id of the employee and something like month(data_entrada). As mentioned by marcusagm, replace month(data_entrada) by a BETWEEN might be a good idea if there are indices defined in the table.

Example in Sqlfiddle