SQL query returns total days in a month on two dates

With the change of strategy, I did the editing of SQL so that it calculates like this:

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

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

• If the entered month belongs to the entry, enter the DIFERENCA_MENTRADA field if the month entered belongs to the month of the output and get 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

SQLFiddle SQLFiddle

Month 5: Then take the difference

Month6:Thengetthedifference_msaida

Well, I'm not sure if I understood your doubt, but let's ...

QUERY

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

RESULT

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

It counted 9 days because it was the period between 2014-06-03 and 2014-05-25. You must format the end date.

  

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

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

Throwing the end date for the last day of the month relating to the start date:

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

RESULT

31-08-2014

Just use the date formatted in your QUERY

I think the problem is in the data structure you have adopted. If you want a really functional and realistic work history, you should have one record per day, where the record would have the time of entry and the time of exit.

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

But a possible solution is as follows

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 dates used in filters, such as NOW() , CURDATE() , etc, because using them will have a loss of performance. These functions make it impossible to use MySQL query caching.

As quoted in @ papa-charlie's response, 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 achieve the result you want, it would suffice to 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 have frequent problems with this date calculation, whether for annual, monthly, or daily reports. Consider making the system structure change now if you are still in the initial stage.

I have arrived at the expected result by verifying that the data_saida is in the same month of the date entered through a IF of the 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, you just have to match a WHERE with the id of the employee and something like month(data_entrada) . As mentioned by marcusagm, replacing month(data_entrada) with a BETWEEN might be a good idea if indexes are defined in the table.

Example in SQLFiddle