/* Alternate entries for "Cust_ID" if that below is ineffective 
 Replace Lines 2 & 5 with the following, and Delete ", tTrain.TrainMaintainer"
 from the Select statement below. */
  WHEN Owner1 + CallSubject1 = 'FRTRADIO BASE EQ' THEN 'FRT'
  WHEN Owner1 = 'IMD' THEN 'OSD'
  WHEN Owner1 = 'RBD' THEN 'OSD'
  WHEN Owner1 = 'RBV' THEN 'OSD'





/* Amended Quetzal Faults statement. Blank lines for clarity - separates the functions in the statement */
SELECT (CASE
  WHEN CallSubject1 = 'RADIO BASE EQ' AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV')) THEN Owner1
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEG'
  WHEN ((TrainMaintainer IS NOT NULL) AND (TrainMaintainer NOT LIKE '')) THEN TrainMaintainer
  WHEN Owner1 = 'NXB' THEN 'MEG'
  WHEN Owner1 = 'CGE' THEN 'MEG'
  WHEN Owner1 = 'CME' THEN 'MEG'
  WHEN Owner1 = 'CDF' THEN 'MEG'
  WHEN Owner1 = 'CAA' THEN 'MEG'
  WHEN Owner1 = 'CDM' THEN 'MEG'
  WHEN Owner1 = 'CCM' THEN 'MEG'
  WHEN Owner1 = 'CJD' THEN 'MEG'
  WHEN Owner1 = 'CTT' THEN 'MEG'
  ELSE Owner1
 END) AS "Cust_ID", (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 'PABX EXCHANGE' 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 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'TTB-LOCO FAULT') AND (CallSubject2 LIKE 'NUTR TTBR')) THEN 0
  WHEN ((CallSubject1 LIKE 'FLEX COMM TERM') AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'OST PHONE') AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'MDC CONSOLE') AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'SHUNT RADIO') AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'CM LINK') AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV'))) THEN 0
  WHEN ((CallSubject1 LIKE 'ASW - COMMS') AND (Owner1 LIKE 'FRT')) THEN 0
  WHEN ((CallSubject1 LIKE 'BASE STN EQUIP') AND (Owner1 IN ('FRT', 'ART', 'OSD', 'IMD', 'RBD', 'RBV'))) 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) AS "Bill", qry_all.*
FROM (SELECT (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) AS "Owner1", QuetzalFaults.*
FROM (SELECT (CASE
  WHEN LEN(RTRIM(tTrain.TrainOwner)) > 0 THEN tTrain.TrainOwner
  WHEN LEN(RTRIM(tLocation.LocOwner)) > 0 THEN tLocation.LocOwner
  ELSE tName.Owner
 END) AS "Assign", tName.*, tTrain.TrainMaintainer
FROM (

(SELECT Name2.*, Client.Ref3 AS Owner
FROM (SELECT Call.ClientCode, Call.CallNumber, Call.LastName, Call.Logon, Call.CallSubject3,
 Call.SupportGroup, Call.LogDatTim, 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, Configuration.Ref2 AS TrainMaintainer
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