Merging Two tables into single one

[deepakqlikview_123]

Hi All,

I have two tables as per below

EnquiryMaster1:

load

DealerID

resident OrganisationMaster;

left join(EnquiryMaster1)

LOAD

  DealerID ,

  EnquiryID,

  EnquiryNumber,

  date(EnquiryDate,'DD-MM-YYYY') as EnquiryDate,

// DealerID&'_'&(year(date(EnquiryDate,'DD-MM-YYYY'))*10000+month(date(EnquiryDate,'DD-MM-YYYY'))*100+day(date(EnquiryDate,'DD-MM-YYYY'))) as EnquiryKey,

// DealerID&'_'&(year(date(EnquiryDate,'DD-MM-YYYY'))*10000+month(date(EnquiryDate,'DD-MM-YYYY'))*100+day(date(EnquiryDate,'DD-MM-YYYY'))) as EnquiryKey2,

  year(EnquiryDate) as Year,

  month(EnquiryDate) as Month,

  day(EnquiryDate) as Day,

  'Q' & Ceil(Month(EnquiryDate)/3) as Quarter,

  //week(EnquiryDate) as week,

if(day(EnquiryDate)>=1 and day(EnquiryDate)<=7,1,

if(day(EnquiryDate)>=8 and day(EnquiryDate)<=14,2,

if(day(EnquiryDate)>=15 and day(EnquiryDate)<=21,3,4))) as Week,

  OutletID,

  EnquiryAtID,

  EnquirySourceID,

// EnquiryReference,

  ReasonForBuyingID,

// FollowupPeriodID,

  CustomerID,

  AreaID,

  SubAreaID,

  VehicleID,

  SubModelTypeID,

  SubModelID,

  ColorID,

// Quantity,

  FinancerID,

// FinancierDetails,

  EndUseID,

  SegmentID,

  TargetCustomerTypeID as EnqTargetCustomerTypeID,

  EnquiryStatusID,

  EnquiryGeneratorEmployeeID,

  EnquiryExecutorEmployeeID,

// IsSubModel,

  DownPaymentCapacityID,

// OtherDetails,

  FollowupRemarkID,

// ReasonID ,

// SubReasonID ,

// SubReasonTypeID as EnqSubReasonTypeID,

ReasonID&'-'&SubReasonID&'-'&SubReasonTypeID as ReasonKey,

  ReasonForPurchasingID,

  FinanceTypeID

  //if(FinanceTypeID=1,'Cash',

// if(FinanceTypeID=2,'Finance')) as FinanceType

// IsClosed,

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData,

// MeetingModuleid

FROM $(TransactionQvdPath)\PRE_EnquiryMaster.qvd (qvd)

where year(EnquiryDate)>=2007;

EnquiryMaster:

load

*,

'' as EM

resident EnquiryMaster1;

left join(EnquiryMaster)

LOAD

  DealerID as EmpDealerID,

     EmployeeID as EnquiryExecutorEmployeeID,

     EmployeeCode,

     FirstName,

     MiddleName,

     LastName,

     (capitalize(FirstName)&' '&capitalize(MiddleName)&' '&capitalize(LastName)) as [Executor Name],

     BirthDate,

     Sex,

     MaritalStatus,

     Nationality,

//    Address,

//     City,

//     DistrictID,

//     StateID,

     Pin,

     Phone,

     Mobile,

     EmailAddress,

     Qualification,

     Experience,

     JoiningDate,

     ProbationDuration,

     IsInsured,

     GradeCode,

     Salary,

     PVPLTraining,

     BloodGroup,

  //   OutletID,

     LeavingDate,

     DesignationId,

     RoleID,

     ReportingTo,

//     IsDeleted,

//     CreatedBy,

//     CreatedOn,

//     UpdatedBy,

//     UpdatedOn,

//     IsMigratedData,

//     IsIDMSMigratedData,

     IsExitInterviewDone,

     UserTypeID

FROM

$(QvdPath)\PRE_EmployeeMaster.qvd

(qvd);

join(EnquiryMaster)

load

ReasonID&'-'&SubReasonID&'-'&SubReasonTypeID as ReasonKey,

ReasonID,

capitalize(Reason) as Reason,

SubReasonID,

capitalize(SubReasonName) as SubReasonName,

SubReasonTypeID,

capitalize(SubReasonTypeName) as SubReasonTypeName,

     '' as jgk

resident SubReasonTypeMaster;

DROP Table SubReasonTypeMaster;

//left join(EnquiryMaster)

//

//load

//*,

//' ' as FR

//resident FollowupRegister;

drop Table EnquiryMaster1;//,FollowupRegister;

EnquiryStatus:

LOAD

  EnquiryStatusID,

     capitalize(EnquiryStatus) as [Enquiry Status]

//     IsDeleted,

//     CreatedBy,

//     CreatedOn,

//     UpdatedBy,

//     UpdatedOn,

//     IsMigratedData,

//     IsIDMSMigratedData

FROM

$(QvdPath)\PRE_EnquiryStatus.qvd

(qvd);

EnquiryAtMaster:

LOAD

  EnquiryAtID,

  capitalize(EnquiryAtValue) as EnquiryAtValue

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquiryAtMaster.qvd (qvd);

EnquirySource_EnquiryAt:

LOAD

EnquirySourceEnquiryAtID,

  EnquirySourceID

// EnquiryAtID

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquirySource_EnquiryAt.qvd (qvd);

EnquirySourceMaster:

LOAD

  EnquirySourceID,

  capitalize(EnquirySourceDescription) as EnquirySourceDescription

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

FROM $(QvdPath)\PRE_EnquirySourceMaster.qvd (qvd);

Cal:

load

FinalDealerID,

CalDate,

VehicleID,

ColorID,

monthname(CalDate) as MonthName,

year(CalDate) as Year,

month(CalDate) as Month,

'Q' & Ceil(Month(CalDate)/3) as Quarter,

//week(CalDate) as Week1,

//week(month(CalDate)) as Week2,

day(CalDate) as Day,

(interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D')) as DayDiff,

FinalDealerID&'_'&VehicleID&'_'&ColorID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as BillingKey,

//FinalDealerID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as BillingKey1,

FinalDealerID&'_'&VehicleID&'_'&ColorID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as StockKey,

//FinalDealerID&'_'&(year(CalDate)*10000+month(CalDate)*100+day(CalDate)) as StockKey1,

if(day(CalDate)>=1 and day(CalDate)<=7,1,

if(day(CalDate)>=8 and day(CalDate)<=14,2,

if(day(CalDate)>=15 and day(CalDate)<=21,3,4))) as Week,

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=0 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=10),'0-10 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=11 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=20),'11-20 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=21 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=30),'21-30 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=31 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=40),'31-40 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=41 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=50),'41-50 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=51 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=60),'51-60 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=61 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=70),'61-70 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=71 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=80),'71-80 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=81 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=90),'81-90 Days',

if(((interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))>=91 and (interval(date(today(),'DD-MM-YYYY') - date(CalDate,'DD-MM-YYYY'),'D'))<=180),'91-180 Days',

'>180 Days'

)))))))))) as DayAgeing

resident Cal1

where year(CalDate)>=2007;

drop Table Cal1;

Can you please suggest how to merge tables Enquirymster and Cal.

Thanks