Hi All,
I need the experts' help!
I originally created this script to run a report every Monday for the previous Sun-Sat.
Now the request is that the report can be run for yesterday(Sunday), last week(Sunday-Saturday) and Last Month(previous last full month), and custom date ranges.
This is my original command, I know I need to add parameters in Crystal modify command window but that is where I get lost. Users want to see the above parameter options in a dropdown menu for start and end dates (t-1, t-7, mb-1, me-1, custom dates).
Hoping this is possible!
SELECT
COALESCE(MAX(CONVERT(VARCHAR,EMP.NAME)), '*UNKNOWN CODER ID') "CODER USER ID"
,PAC.TAR_ID "TAR ID"
,PAC.CODED_DATE "CODED DATE"
,COUNT(PAC.CHARGE_LINE) "LINE COUNT"
,COALESCE(MAX(CONVERT(VARCHAR,PAC.TX_ID)), '*UNKNOWN TX ID') "TX ID"
,COALESCE(MAX(CONVERT(VARCHAR,PAC.OLD_RETRO_ETR_ID)), '*UNKNOWN TX ID') "ETR ID"
FROM
PRE_AR_CHG PAC
LEFT OUTER JOIN
CLARITY_EMP EMP
ON PAC.CODER_USER_ID = EMP.USER_ID
WHERE
PAC.CODED_DATE BETWEEN GETDATE() - 9 AND GETDATE()-2
GROUP BY
PAC.CODED_DATE
,EMP.USER_ID
,PAC.TAR_ID