I have a CAIXA table, where TIPO can have two results, 00 and 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 show me only the branches ( COD_FILIAL ) where they did not close the box.
It would look something like this:
WHERE TIPO = 99 < TIPO = 00 and DATA between '20160101' and '20160101'
But I can not pass on this thinking to SQL.
So I understand you would have a table box that looks like 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 (the threads where there was a cash opening on a particular day but no closing):
| COD_FILIAL | TIPO | DATA |
|------------|------|----------|
| 3 | 00 | 20160101 |
| 2 | 00 | 20160102 |
| 1 | 00 | 20160103 |
Considering that there must always be a box opening to have a closure, think that boxes without closure are the difference of the subset of closed boxes (the gray part):
Togetonlythegraypart,thinkthatyouhavetwolists:thedaysanddayswithopenboxandthethreadswithclosedbox.Whenyoujointheselists,removetheintersectingpartandyouwillhavethedaystheboxeswereleftopen.
SELECT*FROM(SELECT*FROMCAIXAWHERESTATUS='00')ASaberturaLEFTJOIN(SELECT*FROMCAIXAWHERESTATUS='99')ASfechamentoONabertura.cod_filial=fechamento.cod_filialANDabertura.data=fechamento.dataWHEREfechamento.statusISNULL;Seethequeryrunningon SQL Fiddle .
To do this query I used the concepts of subquery and joins. This query can be done in different ways by using a SQL Server CVE.
Here are some links that can help you better understand these concepts.