Filter data that is not between dates

Asked

Viewed 37 times

0

We are making a bucket rental system for our tcc, but on the sales screen we only need to appear the available buckets in the selected period.

SELECT tabCacamba.CodCac, tabPrestadores.CodPre, tabregcac.dtini, tabregcac.dtfim
FROM tabPrestadores INNER JOIN (tabCacamba INNER JOIN tabregcac ON tabCacamba.CodCac = tabregcac.codcac
FROM tabPrestadores INNER JOIN (tabCacamba INNER JOIN tabregcac ON tabCacamba.CodCac = tabregcac.codcac) ON tabPrestadores.CodPre = tabCacamba.Prestador
WHERE (((tabCacamba.CodCac) Not In (Select tabCacamba.CodCac FROM tabCacamba WHERE [Initial Date] between tabRegCac.dtIni and tabRegCac.dtFim)));
sql ms-access

  • It seems to me that your SQL command is syntactically wrong. Another thing is that you talk about availability in the period but just test a start date.

1 answer

0

I’m not sure I understand the rules that surround your problem.

Supposing:

01) The Bucket Record Table stores the buckets and their respective periods in which they are occupied.

02) The Bucket Table records the information of the bucket and the provider associated with this bucket.

A possible solution would be:

SELECT tabelaCacamba.codigoCacamba, tabelaPrestadores.codigoPrestador, tabelaRegistroDeCacambas.dataInicio, tabelaRegistroCacambas.dataFim
FROM Cacamba tabelaCacamba 
INNER JOIN RegistroDeCacambas tabelaRegistroDeCacambas ON tabelaCacamba.codigoCacamba = tabelaRegistroDeCacambas.codigoCacamba
INNER JOIN Prestadores tabelaPrestadores ON tabelaPrestadores.codigoPrestador = tabelaCacamba.codigoPrestador
WHERE NOT(
    [dataInicialBusca] BEETWEEN tabelaRegistroCacambas.dataInicio and tabelaRegistroCacambas.dataFim
    OR [dataFinalBusca] BEETWEEN tabelaRegistroCacambas.dataInicio and tabelaRegistroCacambas.dataFim
);

Browser other questions tagged sql ms-access

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