0

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 places marked with >>> there is no record that date, eg the date jumps 7 to 9 date and the date 14 jumps to 16, it ta correct because that day had no change.

What do I need to know: How many ente day on 08 and 15 which are the days that has no movement because this period can not exceed 7 days.

Another vestment I need is: At the 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 what day of the week is the date, and list only Sunday.

How could proceed with these two cases?

Note: I'm sorry if you have any clerical error and because I am not good at English and used a translator.

1 Answer 1

1

Check out my answer to the following question on StackOverflow

https://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values/3111441#3111441

You could easily apply this method to your problem, and return data for all the days between a range, including those with no data content.

Dave

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.