SQL Command Date Parameter

[Former Member]

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

[DellSC]

{?Start Date} and {?End Date} are also parameters with a "Date" type that you need to create in the Command Editor.

What is the specific error number that you get?

What type of database are you connecting to? I assumed MS SQL Server, but the syntax will probably be different if it's not. Do you have other filters in your Where clause? If yes, then you'll need to put a pair of parentheses around everything under the where clause above.

For troubleshooting, you can try a couple of things:

1. Move your query to a query tool like SQL Server Management Studio or Toad. Change the syntax on the parameters to that of the database. So, for example, if you're using SQL Server, you'll put something like this at the top of your query:

declare @DateType varchar(10)
declare @StartDate date
declare @EndDate date
set @DateType = 'Yesterday'
set @StartDate = GetDate() - 7
set @EndDate = GetDate()
Select...
Where ...

You'll replace '{?Date Type}' with @DateType, {?Start Date} with @StartDate, etc. Get your query working there will all of the options. Once it's working, remove the declare and set statements that you added above the query and change the parameters back to Crystal's syntax. Don't forget to add the single-quotes around {?Date Type} or else it won't work! Paste this back into the report.

2. In Crystal, comment out from the first 'OR' through the end of the condition so that you're just testing "Yesterday". Make sure that's working correctly. Then add back the first OR and the statement under it and make sure that "Last Week" is working correctly. Then add the last part of the condition back.

-Dell