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:




      Order Nr   Ord.Date CampaignCode 

      100          01-02-12     PROMO

      101          15-04-12     PROMO



      CampaignCode  Order nr.  other infor concerning Campaign    

      PROMO               100        other data

      PROMO               101        ....




      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




        • 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 !!



                • Re: Help Sql select

                  Good Morning Stefan,


                  Here you are



                  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

                  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.




              • Re: Help Sql select
                Pradip Sen

                Hi peronist,


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


                Hope it helps you.