SQL query that returns dates that do not exist in the result of the query itself

Question

SQL query that returns dates that do not exist in the result of the query itself

Navigation

#1 by (1 votes)

0

I am working with a database in Informix. I access it from a web application developed with C #.

In a table called MC_turnosAgenda I have several records, of which I want to obtain only the date field, using DISTINCT to obtain only once each value, fulfilling the following condition:

State other than 0.

To do so, I execute the following query:

SELECT DISTINCT(turno_fecha) fecha
FROM MC_turnosAgenda
WHERE turno_estado <> 0 AND
 (turno_fecha NOT IN (SELECT turno_fecha FROM MC_turnosAgenda WHERE 
 turno_estado = 0))

Until then I have resolved it. The query returns a list of dates, eg: 07/20/2017, 24/07/2017, 01/08/2017, 03/08/2017, etc. These dates are passed to a calendar control so that they appear as NOT AVAILABLE.

What I need to do: besides the dates I get with the previous query, I need to include in this list all the dates that DO NOT appear in that query. That is, if the query returns the dates 02/08/2017 and 04/08/2017, and there is no record with date, for example, 03/08/2017, include that date as well. I clarify that there is a lower date and a maximum date to search.

I do not know if there is a way to do it or if instead of doing it by SQL it is convenient to find some way of doing it from the C # code of the application.

sql c# informix

asked by Willy616 10.07.2017 в 20:07

source

1 answer

How to catch the value of an ul / ul with ANGULARJS? Save file with QLineEdit data using getSaveFileName

score 1 · Accepted Answer

The best solution has already been said to you in the comments, if you have a calendar-like interface, simply checking all the days by default as available, and only checking those not available with your query, would be the most optimal.

But if you have some kind of restriction and you have no other way out than to build every day of a certain period, obviously to your query you should add another one to get the additional days. As you mentioned that you do not have all the dates in the table, the implicit problem in your question is:

How to generate a data stream dynamically.

There are multiple possibilities to do this, the one you might think quickly is a WHILE LOOP and an insertion in a temporary table of all date records from FechaDesde to FechaHasta however it is advisable to avoid these cycles for performance issues, other than that there are several options, I recommend reading this article that although it applies to SQL server many tricks could be applied to Informix .

The trick we are going to use is to work with dynamic tables of 10 values that we will "multiply" using CROSS JOIN a few times to build an incremental sequence of numbers, this number adding it in days to our fechadesde will give us each one of the dates of the requested period. I attached this Sqlfiddle to SQL Server as an example. Keep in mind that the example manages to "map" up to a maximum of 10,000 days, it can be increased eventually adding more sentences CROSS JOIN .

In informix it would be more or less like this, bear in mind that for many years I have not seen a Script in this SQL dialect, so I could have made some mistake:

DECLARE  v_FechaDesde   DATE
DECLARE  v_FechaHasta   DATE

SELECT  v_FechaDesde    = '20170101'
SELECT  v_FechaHasta    = '20170630'

SELECT  v_FechaDesde + ((A.A*1000) + (B.A*100) + (C.A*10) + D.A) UNITS DAY
    FROM (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS A
        CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS B
        CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS C
        CROSS JOIN (SELECT 0 AS A UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS D
    WHERE v_FechaDesde + ((A.A*1000) + (B.A*100) + (C.A*10) + D.A) UNITS DAY <= v_FechaDesde
ORDER BY 1

What you should do once this sequence is generated is the following:

Filter from the sequence of dates, those that are already in your consult with a simple NOT IN (<tu consulta>)

Join both queries using a UNION

I hope it's useful for you.