Skip to Content

SQL Command Date Parameter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 02 at 08:12 PM

    This may be possible depending on which version of Crystal you're using, but probably not in the way that your users want it. I don't remember which version gave us optional parameters - it was either 2008 or 2011. However, there's no way to hide the date parameters if custom dates is not selected.

    You'll create a "Date Type" parameter that will have your options - Yesterday, Last Week, Last Month, Custom. Create this parameter in the Command editor. Once you have the command saved, you'll be able to edit it in the Field Explorer of the report to add the valid values.

    Based on the SQL you posted, I'm going to assume that you're using MS SQL Server - so I'll use that syntax. I'm also going to set this up so that it's not dependent on a report being run on a Monday - it will calculate the last full week and the last full month. Your Where clause will look something like this:

    Where
      ('{?Date Type}' = 'Yesterday' and  PAC.CODED_DATE = GetDate() - 1) 
      or
      ('{?Date Type}' = 'Last Week' and PAC.CODED_DATE between 
            GetDate() - DatePart(wd, GetDate()) - 6 and
            GetDate() - DatePart(wd, GetDate()) )
      or
      ('{?Date Type}' = 'Last Month' and PAC.CODED_DATE between
            DateAdd(m, -1, GetDate() - Day(GetDate()) + 1) and
            GetDate() - Day(GetDate()) )
      or
      ('{?Date Type}' = 'Custom' and PAC.CODED_DATE between {?StartDate} and {?EndDate})

    If you've never worked with parameters in a command before, please see my blog post here for more information.

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

    • I'm getting a failed to retrieve data from database error when I put the where clause above in the command and make a string parameter called Date Type as well, your help is appreciated!

  • May 04 at 05:16 PM

    {?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

    Add comment
    10|10000 characters needed characters exceeded

  • May 08 at 05:48 PM

    Hi Dell,

    Thank you so much! The above worked perfectly except for the custom date part, which in the end was not needed by the user.

    I ran the command with each parameter separately and only got the error with the custom logic. The error was that the database couldn't connect/find the data. I had the custom parameter, startdate and enddate set up as you stated above.

    thanks again!!!

    Add comment
    10|10000 characters needed characters exceeded