Passing Date/DateTime Parameter to SQL Command

[briktal]

I want to pass a Date (or DateTime, whichever will work) parameter to an SQL command. The Database has a datetime field and I want to compare the value in that field to the parameter. All of the datetimes in the Database should be at 00:00:00 for that day.

Previously, I used V.Date>={ts 'YYYY-MM-DD 00:00:00'} AND V.Date<{ts 'YYYY-MM-DD+1 00:00:00') (where DD+1 is the next day). However, this was generated by Crystal Reports using the record selection formula. With the needs of this new command, I can't use that, but I want to have a similar comparison using the parameter.

What are my options?

 

[IanWaterman]

Not sure which version you have but in CR10, after you create your command. You can still add a Crystal date pararmeter.

You can then create a select formula based on that parameter eg

V.Date>={?dateparam} AND V.Date<{?dateparam}+1

Ian

 

[briktal]

Oh sorry I forgot to mention the version. This report is in Crystal 10. I have tried using the date parameter, and while I get no error messages, I don't get the expected results back. Or rather, I get no results.

Also, when you use a parameter in an SQL command, is there a way to see the actual SQL query being used? The Show SQL Query option still has the parameter name in there and not the values.

 

[IanWaterman]

I think because its a command it brings back the whole dataset, and the data is then filtered in the report. So it could potentially get quite slow to run if the dataset is quite big.

Test your formula by creating a formula that returns 1 when true. place datefield and formula on details and make sure data is as you expect without filter.

I am not a big user of commands so there maybe a better way to achieve what you want, hopefully someone else will chip in.

Ian

 

[briktal]

It works if I use, for example, "V.Date>={ts '2007-10-11 00:00:00'} AND V.Date<{ts '2007-10-12 00:00:00'}". That part of the query is copied from an older version of the report (done in a different way) where Crystal Reports generated the whole SQL query itself.

In case it helps, here's what I want to do:
I have a table of drawers, and a view that pulls some performance totals. A row in the view might have: date, drawer number, starting amount, amount in, amount out, username. I need to be able to pull the information for a given day from the view.

However, I also need to show something about the unused drawers for that day. This new query I'm developing does the old query on the view, filtering out the rows on the wrong date, then I do an outer join with the drawer table and the previous result, so I have the usual data for active drawers and nulls for the inactive drawers.

I cannot just do the join at the start because the drawers that are inactive today may have been active in the past.

 

[IanWaterman]

In your view why not just have a list of all valid drawers, and then join that list with a left outer to your data.

You can then filter the data as

(isnull(V.Date) or
(V.Date>={ts '2007-10-11 00:00:00'} AND V.Date<{ts '2007-10-12 00:00:00'}))

Ian

 

[briktal]

The view gets the data mostly from a table of activity, where a row is added whenever a username on a drawer does something.

The problem is that the drawers are inactive on a day-to-day basis, and the only way to tell that the drawer is inactive for a day is to see that there is no activity for that day.

The best I have right now is a list of all the drawers. However, when you do a left outer join to the view, there might be entries from yesterday for a drawer that is inactive today which would mean there is neither a null entry nor an entry from today and so it would get filtered out.

 

[IanWaterman]

That's not what I meant, you need a list of Drawers irrespective of activity. Or you could create a view of your active drawers over the last 12 months (or whatever time period would be representative of active drawers) query would be something like

Select distinct (Drawer) from activity_table
where draw date >= sysdate-365.

Join this view with a l/o to you data and filter as I suggested before.

Ian

 

[briktal]

Ok, I think I have it working now. The query setup was fine, and you helped with putting the parameter into the command. In the end, it was just Crystal Reports doing something weird with parameters and changing things. I started over again working on it and it appears to be doing what it is supposed to be.