Skip to Content
0

SQL Command Date Parameter

May 02 at 06:26 PM

61

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Dell Stinnett-Christy May 02 at 08:12 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Do I have to make a {?StartDate} and {?EndDate} parameters in the command edit window? Also what Value type would the parameter(s) be?

Thanks so much for your help!

0

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!

0
Dell Stinnett-Christy May 04 at 05:16 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
emily johns May 08 at 05:48 PM
0

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!!!

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Glad I could help!

-Dell

0