Join File Loaded from Excel with SQL

[#1]

Hello,

I need to join data loaded from an excel spreadsheet to data loaded from a SQL server.  The Caller SID and the EMP_ID (SID) are the common fields.  I have tried several variations of a join and have been unsuccessful.  The below data loads without issue until I add a join.  Thanks, Seth

Directory;
ImplementationRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
[Time Worked (minutes)] / 100 AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID],
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
   [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
   [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
   [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
   DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
   [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Implementation' AS Team
FROM
[Implementation RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);

ODBC CONNECT32 TO DailyDatamart;

CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;

[prieper]

Try to preceed the SQL-extraction with QlikView-Command LOAD:

CallerCoverage:

JOIN LOAD * SQL SELECT

EMP_ID AS SID,

....

Peter

[vishsaggi]

Try this may be:

ImplementationRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
[Time Worked (minutes)] / 100 AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID]                AS SID,
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
  [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
  [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
  [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
  DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
  [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Implementation' AS Team
FROM
[Implementation RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);

ODBC CONNECT32 TO DailyDatamart;

CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;

There will be an autojoin happens between two tables with SID as Joining. Just do Ctrl+T and see the data model.

[#4]

That worked.  My one issue is I only want the SQL fields where the SID is present in the excel data.  Otherwise I get several million records that I don't need. Do I need to update the WHERE clause in the SQL script?  Or do I need to do something else?  That is the main reason I was trying to do a join.  Sorry I didn't state that in my original question.  Thanks

[vishsaggi]

Try like this may be:

ImplementationRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
[Time Worked (minutes)] / 100 AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID]                AS SID,
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
  [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
  [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
  [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
  DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
  [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Implementation' AS Team
FROM
[Implementation RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);

ODBC CONNECT32 TO DailyDatamart;
INNER JOIN (ImplementationRTT)
CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;

[vishsaggi]

OR a

LEFT JOIN(ImplementationRTT)

CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;

[#7]

This worked.  I assumed I could create two joins on that table the exact same way but when I add the second join I get an error.  "Table not found".  If I remove one of the joins the data loads fine but I need it joined to both spreadsheets to prevent unnecessary records from being loaded.  This is the load getting an error.

Directory;
ImplementationRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
Interval([Time Worked (minutes)]/24/60) AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID] AS [SID],
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
   [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
   [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
   [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
   DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
   [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Implementation' AS Team
FROM
[Implementation RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);


Directory;
InvestorRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
Interval([Time Worked (minutes)]/24/60) AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID] AS [SID],
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
   [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
   [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
   [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
   DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
   [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Investor' AS Team
FROM
[Investor RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);


ODBC CONNECT32 TO DailyDatamart;

INNER JOIN (InvestorRTT)
INNER JOIN (ImplementationRTT)
CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;

[vishsaggi]

You dont need two inner joins here.

Just use one Inner Join. like

ImplementationRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
Interval([Time Worked (minutes)]/24/60) AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID] AS [SID],
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
   [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
   [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
   [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
   DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
   [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Implementation' AS Team
FROM
[Implementation RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);

InvestorRTT:
LOAD [Ticket ID],
Description,
Priority,
Platform,
Queue,
Class,
Status,
Application,
Function,
[Root Cause],
[Time Worked (minutes)],
Interval([Time Worked (minutes)]/24/60) AS [Time Worked (h:mm)],
Assignee,
[Assignee Ctry],
Caller,
[Caller SID] AS [SID],
[Caller Ctry],
DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Creation Date],
YEAR([Creation Date]) AS [Creation Date Year],
MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Month],
DAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Day],
WEEKDAY(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week Day],
WEEK(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY')) AS [Creation Date Week],
'Q' & CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter],
CEIL(MONTH(DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY'))/3) AS [Creation Date Quarter No],
Today() AS [Current Date],
YEAR(Today()) AS [Current Year],
(YEAR(Today())-1) AS [Previous Year],
MONTH(Today()) AS [Current Month],
DAY(Today()) AS [Current Day],
WEEKDAY(Today()) AS [Current Week Day],
WEEK(Today()) AS [Current Week],
'Q' & ceil(month(Today())/3) AS [Current Quarter],
ceil(month(Today())/3) AS [Current Quarter No],
Date(MonthStart(Today()), 'MMM-YYYY') AS [Current Month Year],
'Q' & ceil(month(Today())/3)&' '&YEAR(Today()) AS [Current Quarter Year],
inyeartodate(Today(),Today(),0) * -1 AS [Current YTD],
inyeartodate(Today(),Today(),-1) * -1 AS [Previous YTD],
Today() - DATE(Date#([Creation Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Day Count],
[Creation Time],
[Creation Month],
DATE(Date#([Last Updated Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Last Updated Date],
//[Last Updated Date],
   [Last Updated Time],
[Last Updated Month],
DATE(Date#([Resolution Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Resolution Date],
//[Resolution Date],
   [Resolution Time],
[Resolution Month],
[Resolved By],
DATE(Date#([Closure Date],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Closure Date],
//[Closure Date],
   [Closure Time],
[Closure Month],
[Closed By],
[Age (Creation to Now)],
[Age (Creation to Closure)],
DATE(Date#([Request Start],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request Start],
//[Request Start],
   DATE(Date#([Request End],'DD/MM/YYYY'),'MM/DD/YYYY') AS [Request End],
//[Request End],
   [Resolution Code],
[External ID],
[PostRelease Issue],
[Peregrine ID],
[Priority Number],
'Investor' AS Team
FROM
[Investor RTT.csv]
(txt, codepage is 437, embedded labels, delimiter is ',', msq);


ODBC CONNECT32 TO DailyDatamart;

INNER JOIN (ImplementationRTT)
CallerCoverage:
SQL SELECT
EMP_ID AS SID,
HCS_LOB_NODE06_DESC_TXT AS LOB,
HCS_LOB_NODE07_DESC_TXT AS Region,
HCS_LOB_NODE08_DESC_TXT AS Market
FROM vw_hr_phone_book;