No 'CreateDate' for cleints . . .

[smilbert]

Hello, I'm usig CRXI with SQL2000.

I've got a clients table that unfortunately doesn't have a CreateDate field. That makes it difficult to report on new clients (within the last 30 days).

My workaround idea is to look at the orders table and find only the clients who'se OLDEST order is within the last 30 days.

I can create the formula to calculate the clients oldest order:
Minimum ({tbOrders.Created})

But, this formula obviously cannot be used in the select expert (because it's not calculated yet).

Is there a formula I can use within the select expert that will evaluate the Order date (tbOrders.Created) for each (tbClient.ClientID)?

Any other ideas to accomplish this?? Perhaps I could use my report as a Sub-Report?? Will I be able to link based on this formula?

 

[MJRBIM]

Can you create a VIEW on your MS-SQL database that shows just the ClientID and Minimum ({tbOrders.Created}) as it's two fields?

You can then use that VIEW as the source for your "New Clients" report.

You could probably also do it as a COMMAND in the Report, but I think a VIEW on the MS-SQL side would run faster.

 

[smilbert]

A VIEW sounds like the best option--that way I could use it from any other Reports I create too!

I've never created a veiw before. It's not like creating a table, right? I'm not actually impacting the database at all?? Are there any threads or docs you can recommend to learn how?

If it is as simple as a SQL Query command, here are the actual table names and values that I'd like in the view.

tbClients.ClientID
minimum ({tbOrders.Created}

As always, thanks for your time ahd assistance.

 

[MJRBIM]

Is "ClientID" also in the "tbOrders" table...?

 

[smilbert]

Yes, each order in tbOrders references the ClientID. Of course, there can be multiple orders for the same ClientID so ClientID is not unique in tbOrders.

Thanks