qryStringList.Add('SELECT ''Cust_ID'' = CASE');
  qryStringList.Add('  WHEN Owner1 + CallSubject1 = ''FRTRADIO BASE EQ'' THEN ''FRT''');
  qryStringList.Add('  WHEN CallSubject1 = ''RADIO BASE EQ'' THEN ''MEG''');
  qryStringList.Add('  WHEN Owner1 = ''NXB'' THEN ''MEG''');
  qryStringList.Add('  WHEN Owner1 = ''CGE'' THEN ''MEG''');
  qryStringList.Add('  WHEN Owner1 = ''CME'' THEN ''MEG''');
  qryStringList.Add('  ELSE Owner1');
  qryStringList.Add(' END, ''Bill'' = CASE');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''CENTRACOM'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''SECURITY'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''TELMAX 21'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''TELMAX'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''FLEXCOMMS'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''QUETZAL'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''PHONE T/T'' THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''EXCHANGE'') AND '+
    '(CallSubject2 LIKE ''PABX SYSTEM'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''LOCO-LTR FAULT'') AND '+
    '(CallSubject2 LIKE ''LOCAL T/RADIO'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''RADIO PORTABLE/FIXED'') AND '+
    '(CallSubject2 LIKE ''TWO WAY RADIO'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''SEPAC%'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''TTB-LOCO FAULT'') AND '+
    '(CallSubject2 LIKE ''NUTR TTBR'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''FLEX COMM TERM'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''POST PHONE'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''MDC CONSOLE'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''SHUNT RADIO'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''PCM LINK'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''ASW - COMMS'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''BASE STN EQUIP'') AND '+
    '(Owner1 LIKE ''FRT'')) THEN 0');
  qryStringList.Add('  WHEN ((CallSubject1 LIKE ''BASE STN EQUIP'') AND '+
    '(Owner1 LIKE ''ART'')) THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''VOICEMAIL'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''PHONE D-TERM'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''PHONE-IQUARTZ'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''PHONE EXICOM'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''PHONE QMASTER'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''SECURITY'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''ETAS'' THEN 0');
  qryStringList.Add('  WHEN CallSubject2 LIKE ''LTR RADIO (End to End)'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''SMR RADIO'' THEN 0');
  qryStringList.Add('  WHEN Owner1 LIKE ''VRT'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''%LOCO%'' THEN 0');
  qryStringList.Add('  WHEN CallSubject1 LIKE ''RADIO DYNON'' THEN 0');
  qryStringList.Add('  ELSE 1');
  qryStringList.Add(' END, qry_all.*');
  qryStringList.Add('FROM (SELECT ''Owner1'' = CASE Assign');
  qryStringList.Add('  WHEN ''HILLSIDE'' THEN ''CME''');
  qryStringList.Add('  WHEN ''BAYSIDE'' THEN ''CME''');
  qryStringList.Add('  WHEN ''FREIGHT VICTORIA'' THEN ''FRT''');
  qryStringList.Add('  WHEN ''FREIGHT AUSTRALIA'' THEN ''FRT''');
  qryStringList.Add('  WHEN ''V/LINE PASS'' THEN ''VLP''');
  qryStringList.Add('  WHEN ''ARTC'' THEN ''ART''');
  qryStringList.Add('  ELSE Assign');
  qryStringList.Add(' END, QuetzalFaults.*');
  qryStringList.Add('FROM (SELECT');
  qryStringList.Add('''Assign'' = CASE');
  qryStringList.Add('  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 '+
    'THEN tTrain.TrainOwner');
  qryStringList.Add('  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 '+
    'THEN tLocation.LocOwner');
  qryStringList.Add('  ELSE tName.Owner');
  qryStringList.Add(' END, tName.*');
//  qryStringList.Add(' END, tTrain.TrainOwner, tLocation.LocOwner, tName.*');
  qryStringList.Add('FROM ((SELECT Name2.*, Client.Ref3 AS Owner');
  qryStringList.Add('FROM (SELECT Call.ClientCode, Call.CallNumber, '+
    'Call.LastName, Call.Logon,');
  qryStringList.Add(' Call.CallSubject3, Call.SupportGroup, Name1.LogDatTim, '+
    'Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2');
  qryStringList.Add('FROM (SELECT Call.LogDatTim, Call.ConfigId, '+
    'Call.CallSubject1, Call.CallSubject2');
  qryStringList.Add('FROM Call');
  qryStringList.Add('WHERE Call.LogDatTim > '''+sStartDate+''' AND '+
    'Call.LogDatTim < '''+sEndDate+'''');
  qryStringList.Add('AND (Call.CallSubject1 NOT LIKE ''W-%'' AND '+
    'Call.CallSubject1 NOT LIKE ''R-%'' AND Call.CallSubject1 NOT LIKE ''C-%'')');
  qryStringList.Add('GROUP BY Call.LogDatTim, Call.ConfigID, '+
    'Call.CallSubject1, Call.CallSubject2) AS Name1');
  qryStringList.Add('LEFT JOIN Call ON Name1.LogDatTim = Call.LogDatTim) '+
    'AS Name2');
  qryStringList.Add('INNER JOIN Client ON Name2.ClientCode = Client.ClientCode)'+
    ' AS tName');
//  qryStringList.Add('FROM ((SELECT Client.Ref3 AS Owner, Client.Ref3 AS DIV, '+
//    'Call.CallNumber, Call.LogDatTim, Call.LastName,');
//  qryStringList.Add(' Call.ConfigId, Call.Logon, Call.CallSubject1, '+
//    'Call.CallSubject2, Call.CallSubject3, Call.SupportGroup');
//  qryStringList.Add('FROM Call INNER JOIN Client '+
//    'ON Call.ClientCode = Client.ClientCode');
//  qryStringList.Add('WHERE Call.LogDatTim > '''+sStartDate+''' AND '+
//    'Call.LogDatTim < '''+sEndDate+'''');
//  qryStringList.Add('AND (Call.CallSubject1 NOT LIKE ''W-%'' AND '+
//    'Call.CallSubject1 NOT LIKE ''R-%'' AND Call.CallSubject1 '+
//    'NOT LIKE ''C-%'')) AS tName');
  qryStringList.Add('LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber');
  qryStringList.Add('FROM (Call LEFT JOIN Logon '+
    'ON Call.LogonCode = Logon.LogonCode)');
  qryStringList.Add('LEFT JOIN Configuration '+
    'ON Call.ConfigCode = Configuration.ConfigCode');
  qryStringList.Add('WHERE Call.LogDatTim > '''+sStartDate+''' AND '+
    'Call.LogDatTim < '''+sEndDate+'''');
  qryStringList.Add('AND (Configuration.ConfigClassificationUnique <> 1)) '+
    'AS tLocation ON tName.CallNumber = tLocation.CallNumber)');
  qryStringList.Add('LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, '+
    'Call.CallNumber');
  qryStringList.Add('FROM (Call LEFT JOIN Configuration '+
    'ON Call.ConfigCode = Configuration.ConfigCode)');
  qryStringList.Add('WHERE Call.LogDatTim > '''+sStartDate+''' AND '+
    'Call.LogDatTim < '''+sEndDate+'''');
  qryStringList.Add('AND (Configuration.Ref1 NOT LIKE ''Z%'' AND '+
    'Configuration.Ref1 NOT LIKE ''E:%'')');
  qryStringList.Add('AND ((Configuration.ConfigClassificationUnique = 1) OR '+
    '(Configuration.ConfigClassificationUnique = 4))) AS tTrain');
  qryStringList.Add('ON tName.CallNumber = tTrain.CallNumber) AS QuetzalFaults)'+
    ' AS qry_all');
  qryStringList.Add('ORDER BY LogDatTim');






















SELECT 'Cust_ID' = CASE
  WHEN Owner1 + CallSubject1 = 'FRTRADIO BASE EQ' THEN 'FRT'
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEG'
  WHEN Owner1 = 'NXB' THEN 'MEG'
  WHEN Owner1 = 'CGE' THEN 'MEG'
  WHEN Owner1 = 'CME' THEN 'MEG'
  ELSE Owner1
 END, 'Bill' = CASE
  WHEN CallSubject1 LIKE 'CENTRACOM' THEN 0
  WHEN CallSubject1 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX 21' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX' THEN 0
  WHEN CallSubject2 LIKE 'FLEXCOMMS' THEN 0
  WHEN CallSubject2 LIKE 'QUETZAL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE T/T' THEN 0
  WHEN ((CallSubject1 LIKE 'EXCHANGE') AND (CallSubject2 LIKE 'PABX SYSTEM')) THEN 0
  WHEN ((CallSubject1 LIKE 'LOCO-LTR FAULT') AND (CallSubject2 LIKE 'LOCAL T/RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'RADIO PORTABLE/FIXED') AND (CallSubject2 LIKE 'TWO WAY RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'SEPAC%') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'TTB-LOCO FAULT') AND (CallSubject2 LIKE 'NUTR TTBR')) THEN 0
  WHEN ((CallSubject1 LIKE 'FLEX COMM TERM') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'POST PHONE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'MDC CONSOLE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'SHUNT RADIO') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'PCM LINK') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'ASW - COMMS') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'ART')) THEN 0
  WHEN CallSubject1 LIKE 'VOICEMAIL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE D-TERM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE-IQUARTZ' THEN 0
  WHEN CallSubject1 LIKE 'PHONE EXICOM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE QMASTER' THEN 0
  WHEN CallSubject2 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'ETAS' THEN 0
  WHEN CallSubject2 LIKE 'LTR RADIO (End to End)' THEN 0
  WHEN CallSubject1 LIKE 'SMR RADIO' THEN 0
  WHEN Owner1 LIKE 'VRT' THEN 0
  WHEN CallSubject1 LIKE '%LOCO%' THEN 0
  WHEN CallSubject1 LIKE 'RADIO DYNON' THEN 0
  ELSE 1
 END, qry_all.*
FROM (SELECT 'Owner1' = CASE Assign
  WHEN 'HILLSIDE' THEN 'CME'
  WHEN 'BAYSIDE' THEN 'CME'
  WHEN 'FREIGHT VICTORIA' THEN 'FRT'
  WHEN 'FREIGHT AUSTRALIA' THEN 'FRT'
  WHEN 'V/LINE PASS' THEN 'VLP'
  WHEN 'ARTC' THEN 'ART'
  ELSE Assign
 END, QuetzalFaults.*
FROM (SELECT
'Assign' = CASE
  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 THEN tTrain.TrainOwner
  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 THEN tLocation.LocOwner
  ELSE tName.Owner
 END, tName.*

// END, tTrain.TrainOwner, tLocation.LocOwner, tName.*

FROM ((SELECT Name2.*, Client.Ref3 AS Owner
FROM (SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDatTim, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (SELECT Call.LogDatTim, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Call.LogDatTim > '20040731' AND Call.LogDatTim < '20040901'
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY Call.LogDatTim, Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1
LEFT JOIN Call ON Name1.LogDatTim = Call.LogDatTim) AS Name2
INNER JOIN Client ON Name2.ClientCode = Client.ClientCode) AS tName

//FROM ((SELECT Client.Ref3 AS Owner, Client.Ref3 AS DIV, Call.CallNumber, Call.LogDatTim, Call.LastName,
// Call.ConfigId, Call.Logon, Call.CallSubject1, Call.CallSubject2, Call.CallSubject3, Call.SupportGroup
//FROM Call INNER JOIN Client ON Call.ClientCode = Client.ClientCode
//WHERE Call.LogDatTim > '20040731' AND Call.LogDatTim < '20040901'
//AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')) AS tName

LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber
FROM (Call LEFT JOIN Logon ON Call.LogonCode = Logon.LogonCode)
LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode
WHERE Call.LogDatTim > '20040731' AND Call.LogDatTim < '20040901'
AND (Configuration.ConfigClassificationUnique <> 1)) AS tLocation ON tName.CallNumber = tLocation.CallNumber)
LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, Call.CallNumber
FROM (Call LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode)
WHERE Call.LogDatTim > '20040731' AND Call.LogDatTim < '20040901'
AND (Configuration.Ref1 NOT LIKE 'Z%' AND Configuration.Ref1 NOT LIKE 'E:%')
AND ((Configuration.ConfigClassificationUnique = 1) OR (Configuration.ConfigClassificationUnique = 4))) AS tTrain
ON tName.CallNumber = tTrain.CallNumber) AS QuetzalFaults) AS qry_all
ORDER BY LogDatTim














SELECT 'Cust_ID' = CASE
  WHEN Owner1 + CallSubject1 = 'FRTRADIO BASE EQ' THEN 'FRT'
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEG'
  WHEN Owner1 = 'NXB' THEN 'MEG'
  WHEN Owner1 = 'CGE' THEN 'MEG'
  WHEN Owner1 = 'CME' THEN 'MEG'
  ELSE Owner1
 END, 'Bill' = CASE
  WHEN CallSubject1 LIKE 'CENTRACOM' THEN 0
  WHEN CallSubject1 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX 21' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX' THEN 0
  WHEN CallSubject2 LIKE 'FLEXCOMMS' THEN 0
  WHEN CallSubject2 LIKE 'QUETZAL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE T/T' THEN 0
  WHEN ((CallSubject1 LIKE 'EXCHANGE') AND (CallSubject2 LIKE 'PABX SYSTEM')) THEN 0
  WHEN ((CallSubject1 LIKE 'LOCO-LTR FAULT') AND (CallSubject2 LIKE 'LOCAL T/RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'RADIO PORTABLE/FIXED') AND (CallSubject2 LIKE 'TWO WAY RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'SEPAC%') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'TTB-LOCO FAULT') AND (CallSubject2 LIKE 'NUTR TTBR')) THEN 0
  WHEN ((CallSubject1 LIKE 'FLEX COMM TERM') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'POST PHONE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'MDC CONSOLE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'SHUNT RADIO') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'PCM LINK') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'ASW - COMMS') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'ART')) THEN 0
  WHEN CallSubject1 LIKE 'VOICEMAIL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE D-TERM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE-IQUARTZ' THEN 0
  WHEN CallSubject1 LIKE 'PHONE EXICOM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE QMASTER' THEN 0
  WHEN CallSubject2 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'ETAS' THEN 0
  WHEN CallSubject2 LIKE 'LTR RADIO (End to End)' THEN 0
  WHEN CallSubject1 LIKE 'SMR RADIO' THEN 0
  WHEN Owner1 LIKE 'VRT' THEN 0
  WHEN CallSubject1 LIKE '%LOCO%' THEN 0
  WHEN CallSubject1 LIKE 'RADIO DYNON' THEN 0
  ELSE 1
 END, qry_all.*
FROM (SELECT 'Owner1' = CASE Assign
  WHEN 'HILLSIDE' THEN 'CME'
  WHEN 'BAYSIDE' THEN 'CME'
  WHEN 'FREIGHT VICTORIA' THEN 'FRT'
  WHEN 'FREIGHT AUSTRALIA' THEN 'FRT'
  WHEN 'V/LINE PASS' THEN 'VLP'
  WHEN 'ARTC' THEN 'ART'
  ELSE Assign
 END, QuetzalFaults.*
FROM (SELECT
'Assign' = CASE
  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 THEN tTrain.TrainOwner
  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 THEN tLocation.LocOwner
  ELSE tName.Owner
 END, tName.*

// END, tTrain.TrainOwner, tLocation.LocOwner, tName.*

FROM ((SELECT Name2.*, Client.Ref3 AS Owner
FROM (SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDatTim, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (SELECT Call.LogDatTim, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY Call.LogDatTim, Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1
LEFT JOIN Call ON Name1.LogDatTim = Call.LogDatTim) AS Name2
INNER JOIN Client ON Name2.ClientCode = Client.ClientCode) AS tName

//FROM ((SELECT Client.Ref3 AS Owner, Client.Ref3 AS DIV, Call.CallNumber, Call.LogDatTim, Call.LastName,
// Call.ConfigId, Call.Logon, Call.CallSubject1, Call.CallSubject2, Call.CallSubject3, Call.SupportGroup
//FROM Call INNER JOIN Client ON Call.ClientCode = Client.ClientCode
//WHERE Call.LogDatTim > '20040731' AND Call.LogDatTim < '20040901'
//AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')) AS tName

LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber
FROM (Call LEFT JOIN Logon ON Call.LogonCode = Logon.LogonCode)
LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.ConfigClassificationUnique <> 1)) AS tLocation ON tName.CallNumber = tLocation.CallNumber)
LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, Call.CallNumber
FROM (Call LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode)
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.Ref1 NOT LIKE 'Z%' AND Configuration.Ref1 NOT LIKE 'E:%')
AND ((Configuration.ConfigClassificationUnique = 1) OR (Configuration.ConfigClassificationUnique = 4))) AS tTrain
ON tName.CallNumber = tTrain.CallNumber) AS QuetzalFaults) AS qry_all
ORDER BY LogDatTim
















SELECT 'Cust_ID' = CASE
  WHEN Owner1 + CallSubject1 = 'FRTRADIO BASE EQ' THEN 'FRT'
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEG'
  WHEN Owner1 = 'NXB' THEN 'MEG'
  WHEN Owner1 = 'CGE' THEN 'MEG'
  WHEN Owner1 = 'CME' THEN 'MEG'
  ELSE Owner1
 END, 'Bill' = CASE
  WHEN CallSubject1 LIKE 'CENTRACOM' THEN 0
  WHEN CallSubject1 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX 21' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX' THEN 0
  WHEN CallSubject2 LIKE 'FLEXCOMMS' THEN 0
  WHEN CallSubject2 LIKE 'QUETZAL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE T/T' THEN 0
  WHEN ((CallSubject1 LIKE 'EXCHANGE') AND (CallSubject2 LIKE 'PABX SYSTEM')) THEN 0
  WHEN ((CallSubject1 LIKE 'LOCO-LTR FAULT') AND (CallSubject2 LIKE 'LOCAL T/RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'RADIO PORTABLE/FIXED') AND (CallSubject2 LIKE 'TWO WAY RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'SEPAC%') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'TTB-LOCO FAULT') AND (CallSubject2 LIKE 'NUTR TTBR')) THEN 0
  WHEN ((CallSubject1 LIKE 'FLEX COMM TERM') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'POST PHONE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'MDC CONSOLE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'SHUNT RADIO') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'PCM LINK') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'ASW - COMMS') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'ART')) THEN 0
  WHEN CallSubject1 LIKE 'VOICEMAIL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE D-TERM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE-IQUARTZ' THEN 0
  WHEN CallSubject1 LIKE 'PHONE EXICOM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE QMASTER' THEN 0
  WHEN CallSubject2 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'ETAS' THEN 0
  WHEN CallSubject2 LIKE 'LTR RADIO (End to End)' THEN 0
  WHEN CallSubject1 LIKE 'SMR RADIO' THEN 0
  WHEN Owner1 LIKE 'VRT' THEN 0
  WHEN CallSubject1 LIKE '%LOCO%' THEN 0
  WHEN CallSubject1 LIKE 'RADIO DYNON' THEN 0
  ELSE 1
 END, qry_all.*
FROM (SELECT 'Owner1' = CASE Assign
  WHEN 'HILLSIDE' THEN 'CME'
  WHEN 'BAYSIDE' THEN 'CME'
  WHEN 'FREIGHT VICTORIA' THEN 'FRT'
  WHEN 'FREIGHT AUSTRALIA' THEN 'FRT'
  WHEN 'V/LINE PASS' THEN 'VLP'
  WHEN 'ARTC' THEN 'ART'
  ELSE Assign
 END, QuetzalFaults.*
FROM (SELECT
'Assign' = CASE
  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 THEN tTrain.TrainOwner
  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 THEN tLocation.LocOwner
  ELSE tName.Owner
 END, tName.*
FROM ((SELECT Name2.*, Client.Ref3 AS Owner
FROM (

SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDate, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY CONVERT(Char(10),Call.LogDatTim,103), Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1
LEFT JOIN Call ON ((Name1.LogDate = CONVERT(Char(10),Call.LogDatTim,103))
AND (Name1.ConfigID = Call.ConfigID)
AND (Name1.CallSubject1 = Call.CallSubject1)
AND (Name1.CallSubject2 = Call.CallSubject2))
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8

) AS Name2

INNER JOIN Client ON Name2.ClientCode = Client.ClientCode) AS tName
LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber
FROM (Call LEFT JOIN Logon ON Call.LogonCode = Logon.LogonCode)
LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.ConfigClassificationUnique <> 1)) AS tLocation ON tName.CallNumber = tLocation.CallNumber)
LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, Call.CallNumber
FROM (Call LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode)
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.Ref1 NOT LIKE 'Z%' AND Configuration.Ref1 NOT LIKE 'E:%')
AND ((Configuration.ConfigClassificationUnique = 1) OR (Configuration.ConfigClassificationUnique = 4))) AS tTrain
ON tName.CallNumber = tTrain.CallNumber) AS QuetzalFaults) AS qry_all
ORDER BY LogDate

// ORDER BY LogDatTim




/* Old NAME2 */
SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDatTim, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (SELECT Call.LogDatTim, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY Call.LogDatTim, Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1
LEFT JOIN Call ON Name1.LogDatTim = Call.LogDatTim



/* testing format: */
SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDatTim, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (

SELECT Call.LogDatTim, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY Call.LogDatTim, Call.ConfigID, Call.CallSubject1, Call.CallSubject2

) AS Name1
LEFT JOIN Call ON Name1.LogDatTim = Call.LogDatTim

/* New NAME2 */
SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDate, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY CONVERT(Char(10),Call.LogDatTim,103), Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1
LEFT JOIN Call ON ((Name1.LogDate = CONVERT(Char(10),Call.LogDatTim,103))
AND (Name1.ConfigID = Call.ConfigID)
AND (Name1.CallSubject1 = Call.CallSubject1)
AND (Name1.CallSubject2 = Call.CallSubject2))
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8





/* final trial */
SELECT 'Cust_ID' = CASE
  WHEN Owner1 + CallSubject1 = 'FRTRADIO BASE EQ' THEN 'FRT'
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEG'
  WHEN Owner1 = 'NXB' THEN 'MEG'
  WHEN Owner1 = 'CGE' THEN 'MEG'
  WHEN Owner1 = 'CME' THEN 'MEG'
  ELSE Owner1
 END, 'Bill' = CASE
  WHEN CallSubject1 LIKE 'CENTRACOM' THEN 0
  WHEN CallSubject1 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX 21' THEN 0
  WHEN CallSubject2 LIKE 'TELMAX' THEN 0
  WHEN CallSubject2 LIKE 'FLEXCOMMS' THEN 0
  WHEN CallSubject2 LIKE 'QUETZAL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE T/T' THEN 0
  WHEN ((CallSubject1 LIKE 'EXCHANGE') AND (CallSubject2 LIKE 'PABX SYSTEM')) THEN 0
  WHEN ((CallSubject1 LIKE 'LOCO-LTR FAULT') AND (CallSubject2 LIKE 'LOCAL T/RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'RADIO PORTABLE/FIXED') AND (CallSubject2 LIKE 'TWO WAY RADIO')) THEN 0
  WHEN ((CallSubject1 LIKE 'SEPAC%') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'TTB-LOCO FAULT') AND (CallSubject2 LIKE 'NUTR TTBR')) THEN 0
  WHEN ((CallSubject1 LIKE 'FLEX COMM TERM') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'POST PHONE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'MDC CONSOLE') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'SHUNT RADIO') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'PCM LINK') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'ASW - COMMS') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 LIKE 'ART')) THEN 0
  WHEN CallSubject1 LIKE 'VOICEMAIL' THEN 0
  WHEN CallSubject1 LIKE 'PHONE D-TERM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE-IQUARTZ' THEN 0
  WHEN CallSubject1 LIKE 'PHONE EXICOM' THEN 0
  WHEN CallSubject1 LIKE 'PHONE QMASTER' THEN 0
  WHEN CallSubject2 LIKE 'SECURITY' THEN 0
  WHEN CallSubject2 LIKE 'ETAS' THEN 0
  WHEN CallSubject2 LIKE 'LTR RADIO (End to End)' THEN 0
  WHEN CallSubject1 LIKE 'SMR RADIO' THEN 0
  WHEN Owner1 LIKE 'VRT' THEN 0
  WHEN CallSubject1 LIKE '%LOCO%' THEN 0
  WHEN CallSubject1 LIKE 'RADIO DYNON' THEN 0
  ELSE 1
 END, qry_all.*
FROM (SELECT 'Owner1' = CASE Assign
  WHEN 'HILLSIDE' THEN 'CME'
  WHEN 'BAYSIDE' THEN 'CME'
  WHEN 'FREIGHT VICTORIA' THEN 'FRT'
  WHEN 'FREIGHT AUSTRALIA' THEN 'FRT'
  WHEN 'V/LINE PASS' THEN 'VLP'
  WHEN 'ARTC' THEN 'ART'
  ELSE Assign
 END, QuetzalFaults.*
FROM (

SELECT
'Assign' = CASE
  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 THEN tTrain.TrainOwner
  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 THEN tLocation.LocOwner
  ELSE tName.Owner
 END, tName.*
FROM ((SELECT Name2.*, Client.Ref3 AS Owner
FROM (

SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDate, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (

SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND Call.ConfigID IS NOT NULL
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY CONVERT(Char(10),Call.LogDatTim,103), Call.ConfigID, Call.CallSubject1, Call.CallSubject2

) AS Name1
LEFT JOIN Call ON ((Name1.LogDate = CONVERT(Char(10),Call.LogDatTim,103))
AND (Name1.ConfigID = Call.ConfigID)
AND (Name1.CallSubject1 = Call.CallSubject1)
AND (Name1.CallSubject2 = Call.CallSubject2))
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8

) AS Name2
INNER JOIN Client ON Name2.ClientCode = Client.ClientCode) AS tName
LEFT JOIN (SELECT Logon.Ref3 AS LocOwner, Call.CallNumber
FROM (Call LEFT JOIN Logon ON Call.LogonCode = Logon.LogonCode)
LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.ConfigClassificationUnique <> 1)) AS tLocation ON tName.CallNumber = tLocation.CallNumber)
LEFT JOIN (SELECT Configuration.Ref1 AS TrainOwner, Call.CallNumber
FROM (Call LEFT JOIN Configuration ON Call.ConfigCode = Configuration.ConfigCode)
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND (Configuration.Ref1 NOT LIKE 'Z%' AND Configuration.Ref1 NOT LIKE 'E:%')
AND ((Configuration.ConfigClassificationUnique = 1) OR (Configuration.ConfigClassificationUnique = 4))) AS tTrain
ON tName.CallNumber = tTrain.CallNumber

) AS QuetzalFaults
) AS qry_all
ORDER BY LogDate




/* Full blown condition tests: */
FROM (SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND Call.ConfigID IS NOT NULL
AND Call.CallSubject1 IS NOT NULL
AND Call.CallSubject2 IS NOT NULL
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY CONVERT(Char(10),Call.LogDatTim,103), Call.ConfigID, Call.CallSubject1, Call.CallSubject2) AS Name1




/* Test major join - want 1233 records (maximum) but above returns 1269 records (damn!!!!!!!!!) */
SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup, Name1.LogDate, Name1.ConfigId, Name1.CallSubject1, Name1.CallSubject2
FROM (

SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND Call.ConfigID IS NOT NULL
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
GROUP BY CONVERT(Char(10),Call.LogDatTim,103), Call.ConfigID, Call.CallSubject1, Call.CallSubject2

) AS Name1
LEFT JOIN 

(
SELECT CONVERT(Char(10),Call.LogDatTim,103) AS LogDate, Call.ConfigId, Call.CallSubject1, Call.CallSubject2,
 Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon,
 Call.CallSubject3, Call.SupportGroup
FROM Call
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8
AND Call.ConfigID IS NOT NULL
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%')
) AS Call_1

 ON ((Name1.LogDate = Call_1.LogDate)
AND (Name1.ConfigID = Call_1.ConfigID)
AND (Name1.CallSubject1 = Call_1.CallSubject1)
AND (Name1.CallSubject2 = Call_1.CallSubject2))
WHERE Year(Call.LogDatTim) = 2004 AND Month(Call.LogDatTim) = 8



/* Remove duplicated entries: */
SELECT tDataCollection.TxnDate, tDataCollection.Var05, tDataCollection.Var02, tDataCollection.Var03
FROM tDataCollection
WHERE tDataCollection.BatchID = 50008
ORDER BY tDataCollection.TxnDate, tDataCollection.Var05, tDataCollection.Var02, tDataCollection.Var03