SQL Query export for excel

Asked

Viewed 230 times

3

Good,

I’m having some difficulties in the result of a query. To fit you is the following, I have a registration application of "unavailability." and I need the following:

Every day I have to extract a report from the previous day, but that report must have the following conditions:

  • Display all "unavailability" in the "Open" state. (Here I have to present)

  • Display all "unavailability" that have been opened on that day (e.g.: 15/09/2014)

  • Display all "unavailability" that have been closed on that day (e.g.: 15/09/2014), however at this point the following may happen:

    An "unavailability" may have been closed yesterday (15/09/2014) but have been opened day (20/03/2014) but this point the query I have is working well. My difficulty is happening when one of the "unavailability" has not yet been closed and the "dataFim" field is null.

The querys I tried were:

var query = "SELECT * FROM indisponibilidades WHERE estadoIndisponibilidade = 'ABERTO' OR dataInicio = '" + dataPedido + "' OR dataFim = '" + dataFim + "' ";

This works but is also extracting results that have been entered today (16/09/2014) and what the field dataFim is null.

I tried too:

var query = "SELECT * FROM indisponibilidades WHERE estadoIndisponibilidade = 'ABERTO' OR dataInicio = '" + dataPedido + "' OR dataFim = '" + dataFim + "' dataFim is not null; ";

However it makes me export also of all that have been closed today (16/09/2014).

Any idea ?

c# sql-server query razor

  • I was left with one question: You need all the ailments that are open and all that have been opened on the day and all that have been closed on the day or else you need all the ailments that have been opened on the day and that are still open and all that have been closed on the day?

  • I need all the ailments that are open and all that were opened on the day and all that were closed on the day

3 answers

2


In reality the condition "OPEN" should fall on all others. That is to say:

// Apresentar todas as "indisponibilidades" que se encontrem no estado "Aberto". (Aqui tenho de apresentar)
var query = "SELECT * FROM indisponibilidades
WHERE estadoIndisponibilidade = 'ABERTO';"; // independente de data de início e fim

// Apresentar todas as "indisponibilidades" que tenham sido abertas nesse dia (por ex: 15/09/2014)
var query = "SELECT * FROM indisponibilidades
WHERE estadoIndisponibilidade = 'ABERTO'
AND (dataInicio = '" + dataPedido + "' OR dataInicio IS NULL);";

// Apresentar todas as "indisponibilidades" que tenham sido fechadas nesse dia (por ex: 15/09/2014) ou que dataFim esteja NULL:
var query = "SELECT * FROM indisponibilidades 
WHERE estadoIndisponibilidade = 'ABERTO'
AND (dataFim = '" + dataFim + "' OR dataFim IS NULL);";

// Se você quiser utilizar a mesma query para todas as possibilidades precisará de algo como:
var query = "SELECT * FROM indisponibilidades WHERE 
estadoIndisponibilidade = 'ABERTO'
AND (dataInicio = '" + dataPedido + "' OR " + dataPedido + " = 0) -- note que aqui o parâmetro vem "0", ou seja, não informei uma data de pedido
AND (dataFim = '" + dataFim + "' OR dataFim IS NULL OR " + dataFim + " = 0);";

1

I believe you need to split the filters by parentheses and AND.

var query = "SELECT * FROM indisponibilidades WHERE 
            estadoIndisponibilidade = 'ABERTO' OR 
            (dataInicio = '" + dataPedido + "' AND dataFim = '" + dataFim + "') OR
            (dataInicio = '" + dataPedido + "' AND dataFim is not null); ";

1

I believe that’s it:

var query = "SELECT * FROM indisponibilidades WHERE 
        estadoIndisponibilidade = 'ABERTO'  /* todas em aberto*/
        OR  (dataInicio = '" + dataPedido "')   /* todas que foram abertas no dia */
        OR  (dataFim = '" + dataFim ); "; /* todas que foram fechadas no dia */

As @Luidy said, it’s always good to use parentheses to facilitate reading and your own understanding. Think that tomorrow or the next day, you may need to make a change to the query, and it may not be you who will.

Browser other questions tagged c# sql-server query razor

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