Showing Results other than a column

Question

I have a table CAIXA, where in the column TIPO can have two results, the 00 and the 99, where 00 is the opening of the terminal and 99 the closing.

I’m trying to create a query where the result is to highlight only the branches (COD_FILIAL) where they did not close the box.

That would be about it:

WHERE TIPO = 99 < TIPO = 00 and DATA between '20160101' and '20160101'

But I’m not getting this thought to SQL.

Answer 1

From what I understand you would have a box table similar to this:

| COD_FILIAL | TIPO | DATA     |
|------------|------|----------|
| 1          | 00   | 20160101 |
| 1          | 99   | 20160101 |
| 2          | 00   | 20160101 |
| 2          | 99   | 20160101 |
| 3          | 00   | 20160101 |
| 1          | 00   | 20160102 |
| 1          | 99   | 20160102 |
| 2          | 00   | 20160102 |
| 1          | 00   | 20160103 |

And you expect the following results (affiliates where there was box opening on a given day but had no closure):

| COD_FILIAL | TIPO | DATA     |
|------------|------|----------|
| 3          | 00   | 20160101 |
| 2          | 00   | 20160102 |
| 1          | 00   | 20160103 |

Considering that there should always be a box opening to have a closure, think that the boxes without closure are the difference of the subset of the closed boxes (the part highlighted in gray):

To get only the gray part, think that you have two lists: affiliates and days with open box and affiliates with closed box. By joining these lists, take out the part that intersects and you will have the days that the boxes have been open.

SELECT *
FROM (SELECT * FROM CAIXA WHERE STATUS = '00') AS abertura
LEFT JOIN (SELECT * FROM CAIXA WHERE STATUS = '99') AS fechamento ON
  abertura.cod_filial = fechamento.cod_filial AND
  abertura.data = fechamento.data
WHERE fechamento.status IS NULL;

See the query running on SQL Fiddle.

To do this query I used the concepts of subquery and joins. This query can be done in different ways using a SQL Server CVE.

I leave below some links that can help you better understand these concepts.

• What is the difference between INNER JOIN and OUTER JOIN?
• T-SQL Subqueries: Where and when to use
• WITH commom_value_expressions (CVE)