Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
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 ??
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???
the field with data should be one,
so on department 1:
replace Sale with SALE1.
QUANTITY as SALE1 //Sale