4 Replies Latest reply: Aug 28, 2012 1:24 AM by Claudia Zwick RSS

    Help Sql select

    Stefano Peroni

      Dear all,

      I'm facing the follwing problem:

       

      Table1                                               

                                                                                

      Order Nr   Ord.Date CampaignCode 

      100          01-02-12     PROMO

      101          15-04-12     PROMO

       

      Table2  

      CampaignCode  Order nr.  other infor concerning Campaign    

      PROMO               100        other data

      PROMO               101        ....

       

       

      Table3

      Campaigncode      Customer     Start / End Date

      PROMO               A            01-01-12  31-03-12

      PROMO               A            01-01-12  31-03-12

      PROMO               B            01-01-12  30-04-12

       

       

      I need to get all the records where Order date is included into the Campaign start/end date, in  this case the first and last line.

       

      How can I solve it by an sql select ?

       

      Help please

       

      Thanks in advance for your help

       

      Gr.

      Stefan

        • Re: Help Sql select

          Hi Stefan,

           

          The easiest way is joining the three tables to one and the loading all dates with the where-condition Ord.Date >= Startdate AND Ord.Date <= Enddate.

           

          Or you use the IntervalMatch.

           

          Both you cann see in the attached file.

           

          Regards Vicky

            • Re: Help Sql select
              Stefano Peroni

              Hi Vicky,

              I'm working with the personal edition and I'm not able to open your file. Could you put your script here, please ?

               

              Thanks very much !!

               

              Gr.

                • Re: Help Sql select

                  Good Morning Stefan,

                   

                  Here you are

                   

                  Table1:

                  LOAD * INLINE [
                      Campaign, Costumer, Startdate, Enddate
                      PROMO, A, 01.01.2012, 31.03.2012
                      PROMO, A, 01.03.2012, 31.03.2012
                      PROMO, B, 01.01.2012, 30.04.2012
                      PROMO, C, 01.06.2012, 31.08.2012
                      PROMO, D, 01.09.2012, 31.10.2012
                      PROMO, D, 01.03.2012, 30.06.2012
                      PROMO, E, 01.05.2012, 31.07.2012
                  ]
                  ;

                  Table2:
                  LOAD * INLINE [
                      Order Nr, Ord.Date, Campaign
                      100, 01.02.2012, PROMO
                      101, 15.04.2012, PROMO
                      102, 02.03.2012, PROMO
                      103, 06.06.2012, PROMO
                      104, 05.09.2012, PROMO
                      105, 07.10.2012, PROMO
                      106, 05.04.2012, PROMO
                  ]
                  ;
                  left join IntervalMatch ([Ord.Date]) LOAD Startdate, Enddate resident Table1;

                  I hope this is want you want.

                   

                  Regards

                  Vicky

              • Re: Help Sql select
                Pradip Sen

                Hi peronist,

                 

                you can use condition like Ord.Date >= Startdate and Ord.Date <= Enddate

                 

                Hope it helps you.

                 

                Regards

                Pradip