Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Data Connectivity
Message Icon Topic: SQL Command Parameter Post Reply Post New Topic
Author Message
johnkret
Newbie
Newbie
Avatar

Joined: 14 Nov 2008
Location: United States
Online Status: Offline
Posts: 12
Quote johnkret Replybullet Topic: SQL Command Parameter
    Posted: 21 Jun 2011 at 4:28am
How do I pass a date parameter parameter to the following sql statemnet? The same statement is used in multiple sub-reports and I would like to pass the date range that is used in all the sub-reports into the SQL statement to speed up processing.
 
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
 
  Confused
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 30 Jun 2011 at 4:33am
You have to create the parameters in  the Command editor, NOT in the Field Explorer in the sub-report - Crystal handles parameters for commands are a little differently than parameters in the report.  Assuming you call the parameters StartDate and EndDate, you'll then put this in the where clause in your query (assuming you're going to filter on dt_RequestDate):
 
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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.