May 26, 2011 at 7:06 am
hi all,
I have developed sql statement which i will be using later in reporting services 2008. its parameter based where user can select Year, Month, Proc, Diag to get trust activity. The SQL statement takes into account multivalue's using IN statement for @FinMonth. How do i do the same for @Proc and @Diag.
currently the data for Procedure codes and Diagnosis code is a string.
e.g x45.6,c456, r54.9 etc
My Reporting services will have a picklist to look up the codes but i would like to add more than one code... so my parameters for @Proc or Diag can accept more codes. The SQL statement only takes into acount one code. How do i change this?
SELECT S.crn, S.admission, P.Procedures, D.Diagnoses, S.DIS_DATE, S.patients_RowKey, S.ADM_TYPE_DESC, S.[DIS HOSP DESC], S.[DIS SPEC DESC],
S.DIS_DATE_FISCAL_MONTH_NAME, S.DIS_DATE_FISCAL_MONTH, S.DIS_DATE_FISCAL_YEAR
FROM inf.tbl_PS_IXP_MEDREC_PROCS_FLAT AS P INNER JOIN
inf.tbl_PS_IXP_ADMS_AND_DIS AS S ON P.Crn = S.crn AND P.admission = S.admission INNER JOIN
inf.tbl_PS_IXP_MEDREC_DIAG_FLAT AS D ON S.crn = D.Crn AND S.admission = D.admission
WHERE (S.DIS_DATE_FISCAL_YEAR = @FinYear) AND (S.DIS_DATE_FISCAL_MONTH_NAME IN (@FinMonth)) AND (P.Procedures LIKE '%' + @Proc + '%') AND
(D.Diagnoses LIKE '%' + @Diag + '%')
ORDER BY S.DIS_DATE
Basically how do i add an IN statment using LIKE %
May 26, 2011 at 7:46 am
This should do:
DECLARE @Procs TABLE (
Proc nvarchar(50)
)
DECLARE @Diags TABLE (
Diag nvarchar(50)
)
INSERT INTO @Procs
SELECT 'VALUE1'
UNION ALL
SELECT 'VALUE2'
UNION ALL
SELECT 'VALUE3'
UNION ALL
SELECT 'VALUE4'
INSERT INTO @Diags
SELECT * FROM @Procs
SELECT S.crn,
S.admission,
P.Procedures,
D.Diagnoses,
S.DIS_DATE,
S.patients_RowKey,
S.ADM_TYPE_DESC,
S.[DIS HOSP DESC],
S.[DIS SPEC DESC],
S.DIS_DATE_FISCAL_MONTH_NAME,
S.DIS_DATE_FISCAL_MONTH,
S.DIS_DATE_FISCAL_YEAR
FROM inf.tbl_PS_IXP_MEDREC_PROCS_FLAT AS P
INNER JOIN inf.tbl_PS_IXP_ADMS_AND_DIS AS S
ON P.Crn = S.crn
AND P.admission = S.admission
INNER JOIN inf.tbl_PS_IXP_MEDREC_DIAG_FLAT AS D
ON S.crn = D.Crn
AND S.admission = D.admission
WHERE (S.DIS_DATE_FISCAL_YEAR = @FinYear)
AND (S.DIS_DATE_FISCAL_MONTH_NAME IN (@FinMonth))
AND EXISTS (
SELECT 1
FROM @Procs
WHERE P.Procedures LIKE '%' + Proc + '%'
)
AND EXISTS (
SELECT 1
FROM @Diags
WHERE D.Diagnoses LIKE '%' + Diag + '%'
)
ORDER BY S.DIS_DATE
You just have to treat the multivalued input and store it into a temp table/table variable. It really depends on how you are receiving the input values.
Hope this helps
Gianluca
-- Gianluca Sartori
May 26, 2011 at 7:57 am
many thanks for replying.
the input values will be recieved using reporting services field on report. The user will type the code. Future work will use reference table. please see screenshot of report.
May 26, 2011 at 8:01 am
I suppose this is a stored procedure, isn't it?
Then you expect some kind of parameter. If it is a single char parameter, you will have to split it on commas or the like.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply