Printing last date ordered when there are multiple dates ordered

[zcalebz]

I'm building a report that shows all of our inventory that HAS NOT!! been ordered in the last (user input date range). For example I have product 138012 it's been ordered on 10/2/01, 1/03/02, 7/14/02, 6/03/03 etc... I've figured out that putting the field in the group footer it'll return 6/03/03. My problem is that when I input a date range such as 01/01/03 - today (records I don't want) IT'll not include the 06/03/03 (which is great), BUT it returns the 7/14/02 (which I don't want). Basically, if the product has not been ordered in the last (user input) I don't want that product showing up at all.

 

[rosemaryl]

Dear Zcalebz,

If you supply your Selection Criteria and do a Database/Show SQL and copy and paste that, that would be helpful if I am off base here.

In Crystal the following will work in the Select Expert:

datediff(&quot;yyyy&quot;,{Incident.Open Date & Time}, Currentdatetime) < 1

However, that will not pass to the database in the SQL Query negatively affecting performance.

I like doing SQL Expressions. They increase performance and will pass to the SQL Query.

For example, I use SQL Server so I would create the expression:

%DateCK (Put just the expression below and replace the Incident.&quot;Open Date & Time&quot; with your products Order Date.)

(DATEDIFF([Year], Incident.&quot;Open Date & Time&quot;, GETDATE()) )

Change my Selection Critieria to:

{%Dateck} < 1

Which now passes to the SQL Query:

WHERE
((DATEDIFF([Year], Incident.&quot;Open Date & Time&quot;, GETDATE()) )) < 1

I hope the above was helpful,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[lbass]

First group on ProductID. Allow your record select statement to include the maximum range of dates that would be included in any parameter select. Create date parameters for start and end dates, and then create a formula {@inperiod} and place it in the details section and suppress, if you like:

if {table.salesdate} in {?start} to {?end} then 1 else 0

Then go to report->edit selection formula->group and enter:

sum({@inperiod},{table.prodID}) = 0

This will return only those products which have no sales during the parameter period.

If you want to do any summaries, you will need to use running totals, since the usual summaries would count the non-group selected records.

-LB