Here is the code in text:
Visit:
Load *;
SELECT
LTRIM( RTRIM(LOCATIONS.LOCATION_CODE))||'-'||LTRIM( RTRIM( ROUTES.ROUTE_CODE)) as Location_Route_Code,
BO_CALL_PLAN.Route_Key,
--LTRIM( RTRIM(LOCATIONS.LOCATION_CODE))||' '||LOCATIONS.LOCATION_NAME,
--LTRIM( RTRIM( AREA.AREA_CODE))||' '||AREA.AREA_NAME,
--LTRIM( RTRIM( ROUTES.ROUTE_CODE))||' '||ROUTES.ROUTE_DESC,
VISITED,
CALL_PLAN,
--'W '||LTRIM(TO_CHAR(CUSTOMER_CALENDAR.ALMARAI_YEAR, '9999'))||'/'||LTRIM(TO_CHAR(CUSTOMER_CALENDAR.ALMARAI_WEEK, '09')),
to_char(CUSTOMER_CALENDAR.END_DATE,'DD/MM/YYYY') as TransactionDate
FROM
LOCATIONS,
AREA,
ROUTES,
BO_CALL_PLAN,
CUSTOMER_CALENDAR
WHERE
( ROUTES.AREA_CODE=AREA.AREA_CODE and ROUTES.LOCATION_KEY=AREA.LOCATION_KEY )
AND ( LOCATIONS.LOCATION_KEY=AREA.LOCATION_KEY )
AND ( CUSTOMER_CALENDAR.TIME_KEY=BO_CALL_PLAN.TIME_KEY )
AND ( ROUTES.ROUTE_KEY=BO_CALL_PLAN.ROUTE_KEY )
AND
CUSTOMER_CALENDAR.ALMARAI_YEAR IN ( $(vCYear), $(vLYear));
CustCount:
Load *;
select distinct count(customer_key) as customer_count,
location_code, to_char(current_date,'DD/MM/YYYY') as TransactionDate,
LTRIM( RTRIM(L.LOCATION_CODE))||'-'||LTRIM( RTRIM(R.ROUTE_CODE)) as Location_Route_Code,
c.Route_Key
from locations l, customers c , routes r, area a
where l.location_key = c.location_key
and R.AREA_CODE = a.area_code
and l.location_key = r.location_key
and r.location_key = a.location_key
and c.route_key = r.route_key
and c.active='Y'
group by location_code, to_char(current_date,'DD/MM/YYYY'),
LTRIM( RTRIM(L.LOCATION_CODE))||'-'||LTRIM( RTRIM(R.ROUTE_CODE)), c.Route_Key;
