1

I have two tables, and I'd like to select a range of positions from one table for each row in the other table.

I've tried to relate the tables in order to get new information but it seems like there is no OUTER APPLY in MS Access. Here is my code:

    SELECT *
FROM [pred-predi_calculo-presi]
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta < [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta DESC
    ) AS p
  OUTER APPLY
    ( SELECT TOP 1 [snptc_histo].POSICIONBUS
      FROM [snptc_histo]
      WHERE [snptc_histo].PATENTEBUS = [pred-predi_calculo-presi].PATENTE
        AND [snptc_histo].horaConsulta >= [pred-predi_calculo-presi].'pasadaReal'
      ORDER BY [snptc_histo].horaConsulta ASC
    ) AS n ;

I thought it'd be much easier to do this action with Java code (I have the algorithm in mind) and also more optimal, but I don't have the right tools like data structures or right "commands" to work with databases through Java (I'm a mathematician) and thus would need much more help. (Although I'm open to suggestions about the Java option.) Here's a sample data:

pred-predi_calculo-presi:

Id1 ID                       Paradero'horaPrediccion''pasadaReal'PREDICCION_NUEVA   PATENTE servicio
1   2016-12-13 17:36:50_T518    PA52    17:36:51       17:36:20      1579,242       CJRW-73    518
2   2016-12-13 17:36:50_T518    PA52    17:36:51       17:36:20      761,211        BJFB-79    518
3   2016-12-13 17:11:02_T518    PC475   17:17:53       17:16:34      1382,545       BFKC-47    518
4   2016-12-13 17:11:02_T518    PC475   17:17:53       17:16:34      695,283        CJRW-74    518
5   2016-12-13 17:11:03_T418    PC206   17:16:27       17:15:50      1210,033       ZN-6742    418
6   2016-12-13 17:11:03_T418    PC206   17:16:27       17:15:50      1407,741       ZN-5814    418
7   2016-12-13 17:16:41_T516    PD208   17:17:56       17:18:02      842,634        BJFY-17    516

snptc_histo:

PATENTEBUS  POSICIONBUS FECHAGPS
WA-9114     0           17:15:52
CJRW-83     3,64        17:15:52
ZN-3967     0,26        17:15:50
BJFV-89     11,06       17:15:51
BJFS-92     32,866      17:15:51
FLXJ-30     19,74       17:15:51
BJFR-52     9,55        17:15:50
CJRW-52     24,67       17:15:50
BJFR-94     14,75       17:15:49
CJRT-63     16,02       17:15:49
Improve this question
5
  • Hi; strategy changed; now I'm using access as interface and data source, but it seems like there is no OUTER APPLY command (no results in office support)
    – Julio Maldonado Henríquez
    Feb 3, 2017 at 17:20
  • May be you could post some sample data and the expected results?
    – mustaccio
    Feb 6, 2017 at 14:43
  • You may need to merge your accounts and have enough reputation to comment and vote. dba.stackexchange.com/users/116463/…
    – atokpas
    Feb 6, 2017 at 15:19
  • @JulioMaldonadoHenríquez: ...and edit your question directly without peer review. Follow these instructions to merge your accounts.
    – Andriy M
    Feb 8, 2017 at 7:27
  • @JSapkota even when Julio Maldonado is unregistered?? I had a account when I asked but forgot to log in
    – Julio Maldonado Henríquez
    Feb 8, 2017 at 14:16

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.