I have a report that has 2 user prompts. The first one asks users to enter a code, and the second one asks users to specify how many records they want to see. I want to make this report automated so that users are not prompted for anything: ideally, the report will bring in all of the codes and limit the results to 10 records per code. I have pasted the code below. Can anyone help me with the necessary changes?
with hstfldata as (select a.rmsfilenum, a.hstoldvalu as prerecallsts, a.hstnewvalu as recallsts, a.rmstrandte as recalldate, a.rmsbalance as recallbal,
b.HSTNEWVALU as AGCYRESP, b.rmsbalance as respbal, b.rmstrandte as RESPDATE, c.hstnewvalu as agency, MISDDESC,
RAND(int(REPLACE(CHAR(CURRENT DATE, ISO),'-',''))) AS RANDNUMGEN, C.RMSTRANDTE as plcdate
from reporting.rhistfl as a
left join reporting.rhistfl as b on a.rmsfilenum = b.rmsfilenum and a.hstnewvalu = b.hstoldvalu and b.rmstrandte >= a.rmstrandte
left join reporting.rhistfl as c on a.rmsfilenum = c.rmsfilenum and c.rmstrandte <= a.rmstrandte
left join reporting.RSASRECV as s on c.hstnewvalu = s.RMSRECVRCD
where a.RMSTRANCDE = 'MT' And a.RMSFLDIMPL = 'RMSSTATUS' And
a.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and a.HSTNEWVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',
'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',
'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'
) and
b.RMSTRANCDE = 'MT' And b.RMSFLDIMPL = 'RMSSTATUS' And
b.rmstrandte > Replace(Char(Current Date - 90 days, ISO),'-','') and b.HSTOLDVALU in ('A14','A15','A24','A26','A44','A45','U01','U02','U03','U04','U05','U06','U07','U08','U09','U10',
'U11','U12','U13','U14','U15','U17','U18','U19','U20','U21','U22','U23','U24','U25','U29','U70',
'U71','U72','U73','U74','U75','U76','U77','U78','UU1','UU2','UU3','UU4'
) and
c.RMSTRANCDE = 'MR' AND c.RMSFLDIMPL = 'ATTRNYCODE' and c.hstnewvalu in ({?AGCY_CODE})
Order By RandNumGen
Fetch First {?#_of_Accts} Rows Only),
MaxValue as (select rmsfilenum, max (plcdate) as maxplacedate
From hstfldata group by rmsfilenum),
FinalData as (select a.* from hstfldata a join MaxValue b on a.rmsfilenum = b.rmsfilenum and a.plcdate = b.maxplacedate)
select RMSSTATECD As "State Code", MISDDESC, agency, rmsacctnum, CASE
WHEN RMSCUSTTYP = 'I'
THEN
CASE
WHEN
Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM)
) <> ','
THEN
Trim(TRIM(RMSLASTNM) || ', ' || TRIM(RMSFIRSTNM))
ELSE
''
END
ELSE
RMSCMPNAME
END AS "DEBTOR NAME", recallbal, RECALLSTS, DATE ( SUBSTR ( DIGITS (recalldate) , 1 , 4 ) || '-' ||
SUBSTR ( DIGITS (recalldate) , 5 , 2 ) || '-' ||
SUBSTR ( DIGITS (recalldate) , 7 , 2 ) ) as recalldte, AGCYRESP, DATE ( SUBSTR ( DIGITS (RESPDATE) , 1 , 4 ) || '-' ||
SUBSTR ( DIGITS (RESPDATE) , 5 , 2 ) || '-' ||
SUBSTR ( DIGITS (RESPDATE) , 7 , 2 ) ) as RESPDTE
from FinalData as a
left join reporting.rmaster as b on a.rmsfilenum = b.rmsfilenum
left join reporting.rcomker as c on a.rmsfilenum = c.rmsfilenum
WHERE
recordtype = 'D'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.