3 Replies Latest reply: Jul 28, 2011 6:41 AM by Leni Balakrishnan RSS

    SQL LEFT JOIN using between operator

      Hi,

      I tried to mimic the following SQL statement in QlikView, but without success. Can anyone help to inspire something for me?

      Select .....

      From....

       

       

      Left

       

      join ...

      ON (Date BETWEEN DateStart AND DateEnd) and ...

       

      PTT





        • SQL LEFT JOIN using between operator
          John Witherspoon

          I think you're looking for an INTERVALMATCH. Here's an example joining on a date range and on two additional fields.

          [Contracts]:
          LOAD * INLINE [
          Contract, Client, Item, Start, End, Price
          1, Client A, Item A, 01/01/08, 12/31/08, 50
          2, Client A, Item A, 01/01/09, 12/31/09, 60
          3, Client A, Item B, 01/01/08, 06/30/09, 70
          4, Client A, Item B, 07/01/09, 12/31/09, 75
          5, Client B, Item A, 01/01/08, 06/30/08, 40
          6, Client B, Item A, 07/01/08, 12/31/09, 50
          ];

          [Sales]:
          LOAD * INLINE [
          Sale, Client, Item, Date
          1, Client A, Item A, 02/15/08
          2, Client A, Item A, 04/15/09
          3, Client A, Item B, 03/20/09
          4, Client A, Item B, 11/30/09
          5, Client B, Item A, 02/15/08
          6, Client B, Item A, 07/31/08
          ];

          LEFT JOIN ([Sales])
          INTERVALMATCH (Date, Client, Item)
          LOAD
          Start
          ,End
          ,Client
          ,Item
          RESIDENT [Contracts]
          ;
          LEFT JOIN ([Sales])
          LOAD *
          RESIDENT [Contracts]
          ;
          DROP TABLE [Contracts]
          ;

          • SQL LEFT JOIN using between operator
            Leni Balakrishnan

            Try to replace the keyword "Between" as below

             

            join ...

             

            ON (Date >= DateStart AND Date <= DateEnd) and ...