// Radio Faults - Read from the Quetzalsc database.
// At the end of this SQL statement, replace with the billing period:
//     yyyy = Year (eg 2011)
//     mm = Month (eg 10)
SELECT 'Owner' =
CASE
 WHEN (configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5) AND configuration.ref1 LIKE 'HILLSIDE'
 THEN 'TNH'
 WHEN (configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5) AND configuration.ref1 LIKE 'BAYSIDE'
 THEN 'TNB'
 WHEN (configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5) AND configuration.ref1 LIKE 'FREIGHT VICTORIA'
 THEN 'FRT'
 WHEN (configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5) AND configuration.ref1 LIKE 'V/LINE PASS'
 THEN 'VPD'
 WHEN (configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5)
 THEN configuration.ref1
 WHEN configuration.configclassificationunique = 6
 THEN 'VRT'
 WHEN location.address3 IS NULL
 THEN 'No Data'
 ELSE location.address3
END, 'Maintainer' =
CASE
 WHEN configuration.configclassificationunique = 1 OR configuration.configclassificationunique = 5
 THEN configuration.ref2
 WHEN configuration.configclassificationunique = 6
 THEN 'VRT'
 ELSE ''
END, 'Bill' =
CASE
 WHEN configuration.configclassificationunique = 6
 THEN 0
 WHEN call.callsubject1 LIKE 'w-%'
 THEN 0
 WHEN call.callsubject1 LIKE 'Base Stn%' AND call.callsubject2 LIKE 'Nutr%'
 THEN 0
 ELSE 1
END, call.CallNumber, call.Ref2,
//END, call.CallNumber AS CallNumber,
logdattim AS LogDate,
call_event2.eventdatetime AS FixedDate,
call.lastname AS Lastname,
call.configid AS Configuration,
call.logon AS Location,
call.callsubject1 AS Equipment,
call.callsubject2 AS System,
call.callsubject3 AS Fault,
call.slt1 AS Priority,
call.supportgroup as ServiceCentre,
'Fee' =
CASE
 WHEN (call.slt1 LIKE 'NO CHARGE' OR call.slt1 LIKE 'QUOTE') AND isnumeric(call.ref3) = 0
 THEN 0
 WHEN call.ref3 IS NULL AND (call.slt1 LIKE 'NO CHARGE' OR call.slt1 LIKE 'QUOTE')
 THEN 0
 WHEN call.slt1 LIKE 'NO CHARGE' OR call.slt1 LIKE 'QUOTE'
 THEN CAST(call.ref3 AS REAL)
 WHEN CAST(CAST(slt.mincharge AS VARCHAR(32)) AS REAL) IS NULL
 THEN 0
 ELSE CAST(CAST(slt.mincharge AS VARCHAR(32)) AS REAL)
END
FROM call
LEFT OUTER JOIN call_event call_event1 ON call_event1.callnumber=call.callnumber AND call_event1.eventcode=9 AND call_event1.iscurrentevent=1
LEFT OUTER JOIN call_event call_event2 ON call_event2.callnumber=call.callnumber AND call_event2.eventcode=4 AND call_event2.iscurrentevent=1
LEFT OUTER JOIN call_event call_event3 ON call_event3.callnumber=call.callnumber AND call_event3.eventcode=1
LEFT OUTER JOIN location ON location.locationunique = call.locationunique
LEFT OUTER JOIN configuration ON Configuration.ConfigId=Call.ConfigId
INNER JOIN slt on slt.sltunique=call.sltunique1
WHERE call.fixstatus = 1 AND Call.CallSubject1 NOT LIKE 'C-%'AND (call.supportgroup LIKE 'RADIO DYNON%'
OR call.supportgroup LIKE 'RADIO SER. CENT%' OR call.supportgroup LIKE 'RADIO SERVICE%' OR call.supportgroup LIKE 'RADIO WORKSHOP CTRY')
AND Year(call_event2.eventdatetime) = yyyy
AND Month(call_event2.eventdatetime) = mm
ORDER BY call.CallNumber