Skip to Content
0
May 02, 2018 at 06:26 PM

SQL Command Date Parameter

1096 Views

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