CREATE TABLE "$CallList" AS

SELECT
_CallID,
_fkContacts,
_fkCompanies,
_fkSites,
_Priority,
_LastResult,
RAND (RANGE 999999) AS _Random,
_DateAdded,
_Done,
_IsCandidate,
J._Secretary,

IF(M._MktDesc IS NOT NULL  AND O._Description IS NOT NULL  THEN
M._MktDesc +' (' + O._Description + ')'
ELSE
M._MktDesc + O._Description) AS _MarketAndType,

IF(C._Name IS NOT NULL THEN
C._Name
ELSE
J._UnlistedCompany + ' ??') +

IF(C._Status IS NOT NULL THEN
' (' + C._Status+')'
ELSE
'')
AS _CompanyAndStatus,

IF(SG._Description IS NOT NULL  AND SC._Description IS NOT NULL THEN
SG._Description + ': ' + SC._Description
ELSE
SG._Description +  SG._Description) AS _SIC,

IF(J._MainPhone IS NOT NULL  THEN J._MainPhone
ELSE  Site._Switchboard) AS _MainPhone,

IF(T._Turnover IS NOT NULL  AND S._Staff IS NOT NULL THEN
T._Turnover +', '+S._Staff+' staff'
ELSE
IF(T._Turnover IS NOT NULL  THEN T._Turnover
ELSE
IF(S._Staff IS NOT NULL  THEN
S._Staff+' staff' ELSE
''))) AS _Size,

W._Title+' '+  W._Forename+' '+W._Surname AS _FullName,

IF(W._Status IS NOT NULL  AND J._JobTitle IS NOT NULL THEN
J._JobTitle + ' (' + W._Status+')'
ELSE
J._JobTitle + W._Status) As _JobAndStatus,

W._Forename,
W._Surname,
W._HomePhone,
W._Mobile,
W._HomeEMail,
W._Status,
IF(W._LatestCV IS NOT NULL THEN TRUE ELSE FALSE) AS _HasCV,
J._EMail,
J._fkJobCodes,
IF(J._Left IS NOT NULL THEN TRUE ELSE FALSE) AS _StillEmployed,
IF (C._Name IS NOT NULL THEN
C._Name
ELSE   J._UnlistedCompany + ' ??') AS _CompanyName,

Site._Town,
Site._County,
Site._Country,
('Site: '+
Site._SiteName +
IF(Site._Town IS NOT NULL THEN ' - ' + Site._Town ELSE '') +
IF(Site._County IS NOT NULL THEN ', ' + Site._County ELSE '') +
IF(Site._Country IS NOT NULL THEN ', ' + Site._Country ELSE '')
)AS _Location,

C._fkMarkets,
C._fkOrgType,
Site._Switchboard,
S._Staff AS _Staffing,
T._Turnover AS _Turnover,
C._fkSICGroup,
C._fkSICCode,
C._Status AS _CompanyStatus,
C._Website,
IF(P._Name IS NOT NULL THEN
'Parent: '+ P._Name
ELSE '') AS _Parent

FROM "$From".Calls Calls
LEFT OUTER JOIN "$From".Companies C ON Calls._fkCompanies = C._ID
LEFT OUTER JOIN "$From".Contacts W ON Calls._fkContacts = W._ID
LEFT OUTER JOIN "$From".Career J ON Calls._fkCareer = J._ID
LEFT OUTER JOIN "$From".SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN "$From".SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN "$From".Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN "$From".OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN "$From".SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN "$From".SICCode SC ON C._fkSICCode = SC._ID
LEFT OUTER JOIN "$From".Companies P ON C._fkCompanies_Parent = P._ID
LEFT OUTER JOIN "$From".Sites Site ON  _fkCompanies = Site._fkCompanies AND
_fkSites = Site._SiteID
WHERE
_fkProjects = :ProjectID
AND
(_SleepUntil IS NULL OR _SleepUntil <= CURRENT_DATE)
AND
(C._Name IS NOT NULL OR W._Surname IS NOT NULL)

WITH DATA