Minimum Date
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22760
Printed Date: 29 Apr 2024 at 3:40pm
Topic: Minimum Date
Posted By: bjfils
Subject: Minimum Date
Date Posted: 12 Apr 2019 at 10:23am
|
First, let me say I'm a CR novice. I'm trying to find out a couple of things, all date related to our customers, and can't find the right syntax for one of them. I'm looking to find the number of days between the date we enter a new customer and the date they place their first order. The date entered as a customer is a static field in the customer record. However, there's no static field in any table about the first order date. There is for the last order date, but that only works if they've placed a single order. In order to get the first order date, I need to look at all orders and select the minimum date in the order entered field. For any one customer for the time frame I'm using, there could be dozens of individual orders. Conceptually, it's quite simple, but I can't get it to work. Any ideas?
|
Replies:
Posted By: kevlray
Date Posted: 15 Apr 2019 at 4:56am
|
Are you using a command to retrieve the data. Then you could probably write some SQL for the code. Otherwise it might have to be a sub-query that returns the minimal date per customer.
|
Posted By: bjfils
Date Posted: 15 Apr 2019 at 5:09am
Here's the selection formula I'm using.
not ({Customer.Number}like "w*")and
{Customer.DateOpened} in Date (2018,01,01 ) to Date(2018,12,31)
I'm trying to add something to this to get the minimum date in [ticket.date_entered]. Like I said, I'm a novice at this and have no idea how to write SQL code or do a sub query. I used to be able to do a lot of things in Access, but that was almost 20 years ago.
|
Posted By: kevlray
Date Posted: 16 Apr 2019 at 4:05am
I meant to say sub-report (I have been doing way too much SQL lately). But now that I think about it. There could be another way. If you group by the customer and the order [ticket.date_entered] (hide details) descending. thus in the group footer, you would have the oldest [ticket.date_entered]. I do not know if this will work for you.
|
Posted By: lockwelle
Date Posted: 02 May 2019 at 10:23am
This is a odd one...couldn't you just ask for the Minimum(orderDate, CustomerGroup)? and then do a datediff against the CustomerCreateDate?
I haven't tried it myself, it just seems like a plausible approach.
HTH
|
|