I have the following query:
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
It returns 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 in the places where I marked with >>>
there is no record on that date, example: da data 7 pula para a data 9
and da data 14 pula para a 16
, this is correct because on that day there was no movement.
What I need to know:
How many days are the dia 08 e o dia 15
that are the days that do not have movement since this period can not exceed 7 days.
Another aspect that I need is: In that same query:
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 on Sundays.
How could you proceed with these two cases?