SQL Query export to excel

Question

SQL Query export to excel

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)

3

Good,

I have some difficulties in the result of a query. For you to fit it is as follows, I have a registration application of "unavailable." and I need the following:

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

Display all "outages" that are in the "Open" state. (Here I have to present)
Show all "unavailable" that have been opened on that day (eg 15/09/2014)
Show all "unavailable" that have been closed on that day (eg 15/09/2014), however at this point the following may happen:

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

The queries 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 (09/16/2014) and what the dataFim field is at.

I also tried:

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

However it does me export also of all that have already been closed during the day today (09/16/2014).

Any ideas?

sql-server c# query razor

asked by anonymous 16.09.2014 / 14:56

3 answers

1

I think you need to split the filters by placing parentheses and ANDs.

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

16.09.2014 / 15:17

1

I believe it to be this:

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 make reading easier and your own understanding. Think that tomorrow or later, it may be necessary to make a change in the query, and it may not be you that will do it.

16.09.2014 / 15:25

Query Return - Generic Repository Entity Framework "Convert" an HTML list to MySQL with PHP

score 2 · Accepted Answer

In reality, the condition "OPEN" must fall on all others. That is:

// 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);";