Data modeling

[#1]

Hi ,

I have two tables  and  one table is having data of 1st department and  another table is having data of  2nd depatment .

there are two  field are common in both

month and conseioners name   .

and i want to combine  these two show total sale of both the depatment  month wise and conssioners wise,

Deparment 1:

how should i combine these two tables so that there is loop in this and will show perfect data model ?

Depatment1:

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

     SALE

     START_DATE as START_DATE_FF,

     END_DATE as END_DATE_FF,

     INSERT_DATE,

     SERVICE_PROVIDER as Consessioner,

Department 2:

Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

Sale,

CATEGORY as Consessioner,

[#2]

i tried to join by  conssioner then  its not giving correct data  month wise .. if i will make a key on the behalf  of conssioner and month then wat wil happen .  i dun want to concatente this tables bcoz they both have diffrent fields other dn month and conssioners

[#3]

i more thing  these two sales of depratment 1 and 2 are different . i dont want to combine them .

then what should i use . concatenate or join ??

[#4]

depatment 1:

LOAD

  Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY') as Date,

  Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month,

  //Year(Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Year,

  //Day(Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Day,

  //DATE_OF_DELIVERY,

  //FLT_NO,

    // AIRCRAFT_TYPE,

   //  TO_DEST,

     QUANTITY as Sale,

      Month &'_'& Consessioner as Key,

   //  BAY_NO,

   // DISP_POSS_TIME,

   // FUELLING_STARTED,

    //FUELLING_FINISHED,

   // DISP_DISPOSS_TIME,

   // FUELLING_TIME,

   // REMARKS,

    //Month( Date#(Left(DATE_OF_DELIVERY,2) & '/' &  mid(DATE_OF_DELIVERY,4,2) & '/' & mid(DATE_OF_DELIVERY,7,4),'DD/MM/YYYY')) as Month, &'_'& SERVICE_PROVIDER as %Key

    CATEGORY as Consessioner

     //BONDED_DUTY_PAID

   FROM

SOurce XYZ,

LOAD

MONTH_YEAR_OF_SALE,

Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY')) as Month,

SALE1,

SALE2,

     //START_DATE as START_DATE_FF,

    // END_DATE as END_DATE_FF,

     //INSERT_DATE,

     SERVICE_PROVIDER as Consessioner,

     // Month( Date#(Left(MONTH_YEAR_OF_SALE,2) & '/' &  mid(MONTH_YEAR_OF_SALE,4,2) & '/' & mid(MONTH_YEAR_OF_SALE,7,4),'DD/MM/YYYY'))  &'_'& SERVICE_PROVIDER as %Key

    Month &'_'& Consessioner as Key

FROM

Source 2.

but  its not working.. kindly help???

[#5]

the field with data should be one,

so on department 1:

replace Sale with SALE1.

QUANTITY as SALE1 //Sale