I am new to Crystal reports and trying to build a report with optional date range parameters. I was needing a step by step answer on how to accomplish this task. I have attached the SQL that I was using.
select r.* from ( SELECT null header, 'MANUFACTURING' mod, cmi.worksite_id || CASE when cmi.sublocation_id is not null THEN '-' ELSE NULL end || cmi.sublocation_id worksite, cmi.unit_no, cmi.component_group, cmi.comp_product_code, cmi.comp_man_status_cd status_cd, cmsd.literal status_literal, cmi.expiration_datetime, cmi.create_datetime, dd.draw_start_datetime draw_datetime FROM component_manufacture cmi inner join cd_comp_man_status cmsd ON cmi.comp_man_status_cd = cmsd.comp_man_status_cd AND cmsd.final_status_cd = 'N' left outer join donor_draw dd on cmi.draw_id = dd.draw_id WHERE TRUNC(cmi.expiration_datetime) < TRUNC(sysdate) --{?COMP_GROUP} --and cmi.component_group in ('RBC') --{?MAN_DRAW_DATE} --and trunc(dd.draw_start_datetime) between trunc(sysdate-100) and trunc(sysdate) --{?PROD_CODE} --and cmi.comp_product_code in ('E0336') --{?WORKSITE} --and cmi.worksite_id ||CASE when cmi.sublocation_id is not null THEN '-' ELSE NULL end|| cmi.sublocation_id in ('KC') union all SELECT null header,1$ 'INVENTORY' mod, cmi.worksite_id || CASE when cmi.sublocation_id is not null THEN '-' ELSE NULL end || cmi.sublocation_id worksite, cmi.unit_no, cmi.component_group, cmi.comp_product_code, cmi.comp_inv_status_cd status_code, ccis.literal status_code, cmi.expiration_datetime, cmi.create_datetime, cmi.draw_datetime draw_datetime from component_inventory cmi inner join cd_component_inv_status ccis on cmi.comp_inv_status_cd = ccis.comp_inv_status_cd and ccis.final_status_cd = 'N' where trunc(cmi.expiration_datetime) < trunc(sysdate) --{?COMP_GROUP} --and cmi.component_group in ('RBC') --{?INV_DRAW_DATE} --and trunc(cmi.draw_datetime) between trunc(sysdate-100) and trunc(sysdate) --{?PROD_CODE} --and cmi.comp_product_code in ('E0336') --{?WORKSITE} --and cmi.worksite_id ||CASE when cmi.sublocation_id is not null THEN '-' ELSE NULL end|| cmi.sublocation_id in ('KC') )r where r.header is null --MOD Paramter order by r.mod desc, r.worksite, r.expiration_datetime, r.create_datetime;