SELECT
TD_ASSET.tx_AssetNumber,
case
  when TD_WR_TYPE.tx_WRTypeDesc = 'Preventive' then
   'Preventive'
  when TD_WR_TYPE.tx_WRTypeDesc = 'Corrective' then
   'Corrective'
  when TD_WR_TYPE.tx_WRTypeDesc = 'Call-In' then
   'Call-In'
   when TD_WR_TYPE.tx_WRTypeDesc = 'Hazard Alert / Recall' then
   'Hazard Alert / Recall'
 else
   'Other Work Orders'
 end "WO_Type",
 TD_WR.tx_RequestNumber,
 case
   when isis.spacezonename(TD_WR.in_BuildingSpaceID) in ('Evanston Hospital - Zone', 'Off-Site Bldgs - Zone') then
   ('Evanston Hospital / Off-Site Bldgs - Zone')
  else
    isis.spacezonename(TD_WR.in_BuildingSpaceID)
  end "Zone",
 isnull(emp.tx_NameFirst, ' ') + ' ' + isnull(emp.tx_NameLast, ' ') "Emp_name",
 isnull(emp.tx_NameLast, ' ') + ' ' + isnull(emp.tx_NameFirst, ' ') "Emp_Name_alpha",
 TD_STATUS.tx_StatusDesc,
 case
  when TD_ASSET.in_AssetTierLookupID = '540' and f.in_RFFunctionValue > 8 then
   'Tier 1 LS'
  when TD_ASSET.in_AssetTierLookupID = '540' and f.in_RFFunctionValue <= 8 then
   'Tier 1 Non-LS'
    when TD_ASSET.in_AssetTierLookupID = '541' then
   'Tier 2'
  when TD_ASSET.in_AssetTierLookupID = '542' then
   'Tier 3'
  else
   'Unknown Tier' end "Tier ",
  datepart(yy,TD_WR.dt_statusDate) "Year",
  datepart(mm,TD_WR.dt_statusDate) "Month",
  datepart(ww,TD_WR.dt_statusDate) "Week",
 TD_WR.dt_RequestDate,
 TD_WR.dt_StatusDate,
 DATEDIFF(d,TD_WR.dt_RequestDate,TD_WR.dt_StatusDate) --Get the number of days between start and end dates
  - DATEDIFF(wk,TD_WR.dt_RequestDate,TD_WR.dt_StatusDate) * 2 -- for each week, subtract 2 days (by default a week occurs between sat and sunday on sql server)
   - CASE
   WHEN DATENAME(dw, TD_WR.dt_RequestDate) <> 'Saturday' AND DATENAME(dw, TD_WR.dt_StatusDate) = 'Saturday'  THEN
      1 --subtract 1 day if the end date falls on a saturday and the startdate is a weekday, or sunday --written by pflangan haha
   WHEN DATENAME(dw,TD_WR.dt_RequestDate) = 'Saturday' AND DATENAME(dw, TD_WR.dt_StatusDate) <> 'Saturday' THEN
     -1 --add 1 if the start date is a saturday and the end date is any other day
 ELSE
      0
  end "Work_Days_Diff"
FROM 
 ISIS.TD_WR TD_WR
 join isis.TD_WR_ATTACHMENT TD_WR_ATTACHMENT on TD_WR_ATTACHMENT.in_WRID = TD_WR.in_WRID
 join ISIS.TD_WR_TYPE TD_WR_TYPE on TD_WR.in_WRTypeID = TD_WR_TYPE.in_WRTypeID
 left outer join ISIS.TD_STATUS TD_STATUS on TD_WR.in_StatusID = TD_STATUS.in_StatusID
 left outer join ISIS.TD_EMPLOYEE emp on TD_WR.in_EmployeeID = emp.in_EmployeeID
 left outer join ISIS.TD_ASSET TD_ASSET on TD_WR_ATTACHMENT.in_AssetID = TD_ASSET.in_AssetID
 left outer join ISIS.TL_RF_FUNCTION rf on TD_ASSET.in_RFFunctionID = rf.in_RFFunctionID
 left outer join ISIS.TL_RF_FUNCTION f on TD_ASSET.in_RFFunctionID = f.in_RFFunctionID
 
WHERE 
 TD_WR.in_ServiceTypeID=4 AND
 TD_WR.tf_Deleted=0