// Quetzal Faults - Read from the Quetzalsc database.
// In the body of SQL (4 times), replace with the billingperiod:
//     yyyy = Year (eg 2011)
//     mm = Month (eg 10)
SELECT (CASE
  WHEN CallSubject1 = 'RADIO BASE EQ' AND (Owner1 IN ('FRT', 'OSD', 'IMD', 'RBD', 'RBV')) THEN Owner1
  WHEN CallSubject1 = 'RADIO BASE EQ' THEN 'MEF'
  WHEN ((TrainMaintainer IS NOT NULL) AND (LTRIM(TrainMaintainer) NOT LIKE '')) THEN TrainMaintainer
  WHEN Owner1 = 'ZCA' THEN 'ZMV'
  WHEN Owner1 = 'ZCC' THEN 'ZMV'
  WHEN Owner1 = 'ZCD' THEN 'ZMV'
  WHEN Owner1 = 'ZCE' THEN 'ZMV'
  WHEN Owner1 = 'ZCF' THEN 'ZMV'
  WHEN Owner1 = 'ZCH' THEN 'ZMV'
  WHEN Owner1 = 'ZME' THEN 'ZMD'
  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 TELEPHONE' THEN 0
  WHEN CallSubject1 LIKE 'PABX VOICEMAIL' 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 'POST 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 'PCM 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 'ZCF'
  WHEN 'BAYSIDE' THEN 'ZCF'
  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) = yyyy AND Month(Call.LogDatTim) = mm
AND Call.ConfigID IS NOT NULL
AND (Call.CallSubject1 NOT LIKE 'W-%' AND Call.CallSubject1 NOT LIKE 'R-%' AND Call.CallSubject1 NOT LIKE 'C-%' AND Call.CallSubject1 NOT LIKE 'LASP%')
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) = yyyy AND Month(Call.LogDatTim) = mm) 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) = yyyy AND Month(Call.LogDatTim) = mm
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) = yyyy AND Month(Call.LogDatTim) = mm
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