October 25, 2011 at 3:10 pm
Trying to get a report's parameters working to where if a zip code is entered into the text box and a start and end date are selected as well, that the results come back as the dates with that particular zip code, but if the zip code if left blank, that the results are only returned based on the dates. I can get the dates and zip to work by entering:
WHERE (CLREFER.ZIP=@ZIP) AND (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)
But when I add the following, then the ZIP parameter is ignored entirely, regardless if anything is in there or not.
WHERE ((CLREFER.ZIP=@ZIP) AND (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)) OR (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)
What I'm looking for is something along the lines of
IF @ZIP='' THEN MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE ELSE (CLREFER.ZIP=@ZIP) AND (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)
Any help would be greatly appreciated.
October 25, 2011 at 4:05 pm
Try this ...
where (CLREFER.ZIP = @ZIP or @ZIP = '')
and MWAPPTS.ADATE between @FIRSTDATE and @LASTDATE
I don't work with Reporting Services much, so I don't know if the @ZIP = '' will evaluate correctly (not sure if it varies by the datatype) or if you would need to change that portion to @ZIP is null.
October 25, 2011 at 4:05 pm
with the help of a post on this site from a few years back I found a solution, but am wondering how efficient it actually is. Critiques welcomed, actually preffered.
IF @ZIP=''
BEGIN
SELECT CLREFER.NAME2, SUM(CASE WHEN mwappts.book = 'OVER_1_871' THEN 1 ELSE 0 END) AS OVERREADS,
SUM(CASE WHEN mwappts.book = 'XR_1_871' THEN 1 ELSE 0 END) AS XRAY, SUM(CASE WHEN mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS DEXA,
SUM(CASE WHEN mwappts.book = 'MG_1_871' THEN 1 ELSE 0 END) AS MAMMO, SUM(CASE WHEN mwappts.book = 'US_1_871' THEN 1 ELSE 0 END) AS US,
SUM(CASE WHEN mwappts.book = 'CT_1_871' THEN 1 ELSE 0 END) AS CT, SUM(CASE WHEN mwappts.book = 'STAND_MRI' THEN 1 ELSE 0 END) AS UPRIGHT,
SUM(CASE WHEN mwappts.book = 'MRI_1_871' THEN 1 ELSE 0 END) AS HIFIELD, SUM(CASE WHEN mwappts.book = 'CT_1_871' OR
mwappts.book = 'MRI_1_871' OR
mwappts.book = 'XR_1_871' OR
mwappts.book = 'STAND_MRI' OR
mwappts.book = 'MG_1_871' OR
mwappts.book = 'US_1_871' OR
mwappts.book = 'OVER_1_871' OR
mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS Total
FROM MWAPPTS INNER JOIN
CLREFER ON MWAPPTS.REFERRAL = CLREFER.CODE
WHERE MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE
GROUP BY CLREFER.NAME2
END
ELSE IF @ZIP<>''
BEGIN
SELECT CLREFER.NAME2, SUM(CASE WHEN mwappts.book = 'OVER_1_871' THEN 1 ELSE 0 END) AS OVERREADS,
SUM(CASE WHEN mwappts.book = 'XR_1_871' THEN 1 ELSE 0 END) AS XRAY, SUM(CASE WHEN mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS DEXA,
SUM(CASE WHEN mwappts.book = 'MG_1_871' THEN 1 ELSE 0 END) AS MAMMO, SUM(CASE WHEN mwappts.book = 'US_1_871' THEN 1 ELSE 0 END) AS US,
SUM(CASE WHEN mwappts.book = 'CT_1_871' THEN 1 ELSE 0 END) AS CT, SUM(CASE WHEN mwappts.book = 'STAND_MRI' THEN 1 ELSE 0 END) AS UPRIGHT,
SUM(CASE WHEN mwappts.book = 'MRI_1_871' THEN 1 ELSE 0 END) AS HIFIELD, SUM(CASE WHEN mwappts.book = 'CT_1_871' OR
mwappts.book = 'MRI_1_871' OR
mwappts.book = 'XR_1_871' OR
mwappts.book = 'STAND_MRI' OR
mwappts.book = 'MG_1_871' OR
mwappts.book = 'US_1_871' OR
mwappts.book = 'OVER_1_871' OR
mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS Total
FROM MWAPPTS INNER JOIN
CLREFER ON MWAPPTS.REFERRAL = CLREFER.CODE
WHERE MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE AND CLREFER.ZIP=@ZIP
GROUP BY CLREFER.NAME2
END
October 26, 2011 at 6:15 pm
With your WHERE:
WHERE
((CLREFER.ZIP=@ZIP)
AND (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE))
OR (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)
Both statements are evaluated because of the OR, essentially negating the @Zip filter.
Need to do something like:
WHERE
CLREFER.ZIP=isNull(@ZIP,CLREFER.ZIP) --will use Zip param if available, otherwise ignored
AND MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply