Check the difference between unlisted results and identify which day of the week is the date

Asked

Viewed 31 times

1

I have the following appointment:

SELECT 
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA 
          ORDER BY V.DATA ASC

She returns to me:

1234    JOAO DA SILVA   2016-09-02 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-03 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-04 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-05 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-06 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-07 00:00:00.000 2
>>> 1234    JOAO DA SILVA   2016-09-09 00:00:00.000 4

1234    JOAO DA SILVA   2016-09-10 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-11 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-12 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-13 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-14 00:00:00.000 4
>>> 1234    JOAO DA SILVA   2016-09-16 00:00:00.000 4

As you can see from the places I’ve marked >>> there is no record at that date, example: da data 7 pula para a data 9 and da data 14 pula para a 16 ,That’s correct because that day had no movement.

What I need to know: How many days between the dia 08 e o dia 15 which are the days that have no movement because this period can not exceed 7 days.

Another thing I need is: In the same consultation:

SELECT 
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA 
          ORDER BY V.DATA ASC

I need to identify which day of the week is the date, and list only Sundays.

How could I proceed with these two cases ?

1 answer

2


--Days between 8th and 15th

Why do you want to know this if you can see the days of the week with DATENAME (in full) and DATEPART? Please better formulate the question...

--DOMINGO

WHERE DATEPART(dw,V.DATA) = 1

  • As for Sunday, perfect, thank you. How to know Days between the 8th and the 15th and why those days would be the days off of employees, what I need to know the difference of days between an employee and another because the difference can not exceed 7 days if you exceed I need to display in the result

Browser other questions tagged

You are not signed in. Login or sign up in order to post.