I'm attempting to make a drop down list of choices for my report using 2 parameters. One is for when jodrtg.fdescnum <> inmastx.fbin1
and the other is for when fcomp_date<=GETDATE()-2
. There's supposed to be 3 options for the filters/parameters in the drop down list the first parameter, second parameter, and one for ALL RECORDS ignoring both of the parameters. I want only one of these options to be selected at once.I have the first parameter jodrtg.fdescnum <> inmastx.fbin1
working in my dataset as well as the option to show ALL RECORDS. My problem is getting fcomp_date<=GETDATE()-2
to work as an option in the same parameter drop down list.
SELECT
R,
Stack,
RTRIM(MoldNo) as MoldNo,
Press,
JobNo,
Status,
RelDate,
StartByDate,
Priority,
RTRIM(WH) as WH,
MoldLoc,
foperno,
flschedule,
RTRIM(WC) as WC,
fcomp_date,
flastlab,
RTRIM(MoldDesc) as MoldDesc,
Cavitation,
MoldDims,
Type,
fddDueDate,
fDueDate,
foperqty,
fnqty_comp,
fnqty_togo,
fnqty_move
FROM
(
SELECT
jodbom.fbompart as MoldNo,
jodrtg.fdescnum AS Press,
jomast.fjobno as JobNo,
jomast.fstatus as Status,
jomast.frel_dt as RelDate,
jomast.ftduedate as StartByDate,
jomast.fschdprior as Priority,
inmastx.flocate1 AS WH,
inmastx.fbin1 AS MoldLoc,
jodrtg.foperno as foperno,
jodrtg.flschedule as flschedule,
jodrtg.fpro_id as WC,
jodrtg.fcomp_date as fcomp_date,
jomast.flastlab as flastlab ,
inmastx.fdescript AS MoldDesc,
inmastx.fcusrchr2 AS Cavitation,
inmastx.fcusrchr3 AS MoldDims,
jomast.ftype as Type,
jomast.fddue_date as fddDueDate,
sorels.fduedate as fDueDate,
jodrtg.foperqty as foperqty,
jodrtg.fnqty_comp as fnqty_comp ,
jodrtg.fnqty_togo as fnqty_togo,
jodrtg.fnqty_move as fnqty_move,
ROW_NUMBER() OVER (PARTITION BY jodbom.fbompart ORDER BY jomast.frel_dt,jomast.fschdprior,jomast.fjobno) R,
ROW_NUMBER() OVER (PARTITION BY jodrtg.fdescnum ORDER BY jodrtg.fdescnum,jomast.frel_dt,jomast.fschdprior,jomast.fjobno) Stack
FROM jodbom
INNER JOIN jomast ON jodbom.fjobno = jomast.fjobno
INNER JOIN jodrtg ON jomast.fjobno = jodrtg.fjobno
INNER JOIN inmastx ON jodbom.fbompart = inmastx.fpartno
LEFT JOIN sorels ON jomast.fsono+jomast.fkey = sorels.fsono+sorels.finumber+sorels.frelease
WHERE Left(jomast.fstatus,1) <> 'C' AND jomast.flisapl = 1 AND jodbom.fltooling = 1 AND jodrtg.fpro_id <> '09' AND flschedule=1
and (jodrtg.fdescnum <> inmastx.fbin1
OR @Setup is null)
) Q
WHERE R = 1
Answered with help from the comments.
(
(jodrtg.fcomp_date <= DATEADD(dd,-2,GETDATE())
And @Setup=1)
Or
(@Setup is null)
Or
(jodrtg.fdescnum <> inmastx.fbin1
AND @Setup =2)
)