Tables

[deepakqlikview_123]

Hi all,

Below are my two tables

Cal:

load

DealerID,

CalDate,

VehicleID,

ColorID,

monthname(CalDate) as MonthName,

year(CalDate) as Billing_Year,

month(CalDate) as Billing_Month,

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

//week(CalDate) as Week1,

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

day(CalDate) as Billing_Day,

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

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

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

DealerID&'_'&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 Billing_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',

'>90 Days'

))))))))) as DayAgeing

resident Cal1

where year(CalDate)>=2007;

drop Table Cal1;

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)\VESPA_PRE_EmployeeMaster.qvd

(qvd);

//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)\VESPA_PRE_EnquiryStatus.qvd

(qvd);

EnquiryAtMaster:

LOAD

  EnquiryAtID,

  capitalize(EnquiryAtValue) as EnquiryAtValue

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

Can you please suggest how shall i proceed.It gives synthetic key

there are 4-5 fields comman.

Shall I use join or concatenate.

If so what will be syntax for that.

Thanks,

Deepak

// IsMigratedData,

// IsIDMSMigratedData

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

EnquirySource_EnquiryAt:

LOAD

EnquirySourceEnquiryAtID,

  EnquirySourceID

// EnquiryAtID

// IsDeleted,

// CreatedBy,

// CreatedOn,

// UpdatedBy,

// UpdatedOn,

// IsMigratedData,

// IsIDMSMigratedData

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