Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: Minimum Date Post Reply Post New Topic
Author Message
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 11
Quote bjfils Replybullet Topic: Minimum 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?
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1493
Quote kevlray Replybullet 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.
IP IP Logged
bjfils
Newbie
Newbie


Joined: 03 Apr 2016
Online Status: Offline
Posts: 11
Quote bjfils Replybullet 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.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1493
Quote kevlray Replybullet 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.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4282
Quote lockwelle Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.