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

 
 

 

and TD_WR.dt_RequestDate between {?StartDate} and {?EndDate}

 

This will add these parameters to each of your subreports.  If you need the parameters in the query in your main report, create them there, otherwise create them in the Field Explorer.  Then link to each subreport using the StartDate and EndDate parameters from the main report - in the bottom left of the Linking Expert, you can select the parameter in the subreport that you want to link the parameter from the main report to.  This will then feed the params into your subreports so that the queries can use them.

 

-Dell