create a formula to combine 2 date fields?

[retygh45]

Not sure how to do this: we have 2 offices, 1 office and 1 data center. The office ships documents to the data center, and both sides log the files they ship is 2 separate databases.

Using CR11, I have 2 tables and I want to compare the count of files in each table to ensure that nothing was dropped. I was trying to create a report that is grouped on a date field formula and then do a count for each side

Tables are essentially: filename, date, time.

How can I create a formula to be "Date" and then do a count for each:

DistinctCount({table.Filename}, {@date}, "daily")

Or something like that. Any help is appreciated, thanks!

 

[Madawc]

You don't need to create a formula, let Crystal do the work. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Are you able to link the two tables to each other? Is it possible that either table could be missing records or only the second table?

-LB

 

[kskid]

If you cannot link the two tables, then I would use the Add Commmand and use the UNION to select the date fields.

Select
'TABLE_A' "FILENAME",
JOB,
DATE_FIELD
FROM
TABLE_A
WHERE
TABLE_A.DATE BETWEEN SYSDATE - 7 AND SYSDATE
UNION
Select
'TABLE_B' "FILENAME",
JOB,
DATE_FIELD
FROM
TABLE_B
WHERE
TABLE_B.DATE BETWEEN SYSDATE - 7 AND SYSDATE

You can then group by date and perform a distinct count
of jobs by date

-lw

 

[retygh45]

thanks for the replies. Yes, I can link the 2 tables by their common file name, which should be the same, but either table could be missing records. Which is the reason I'm putting this report together: to show that if the counts don't match, which side had more files and what those file names were.

Any help is appreciated, thanks!

 

[lbass]

I was going to suggest what kskid did then suggest, although I'm not sure why he added the where clauses. You could leave those out since they limit records to the last seven days.

-LB

 

[retygh45]

Thanks again. But how do I do a UNION statement in the add command window from 2 different databases?

 

[lbass]

Did you try the union statement? Did you get an error message? If the tables have the same fields, you would need to specify separate owners. What type of database is it? For example, if Oracle:

Select
'Location1' "Loc",
"TABLE_A"."JOB",
"TABLE_A"."DATE_FIELD"
FROM
"OWNER1"."TABLE_A" "TABLE_A"
UNION
Select
'Location2' "Loc",
"TABLE_A"."JOB",
"TABLE_A"."DATE_FIELD"
FROM
"OWNER2"."TABLE_A" "TABLE_A"

-LB

 

[retygh45]

Thanks again for your help!

They're both SQL Server 2000 DB's. I add the data source of either SQL instance, and put in the query, but it says it cannot find the other database.

Also, once I get them both into my report, how can I show the difference from either table if the total counts don't match?

Thanks again for all your help!

 

[lbass]

Are they on the same server? I think they have to be.

You would insert a group on {command.loc} and then insert counts on any recurring (non-null) field.

-LB

 

[retygh45]

Sorry, no, they're 2 different db's on 2 different servers. Is there anything else I can do?

 

[lbass]

This is beyond my experience. Could you pull both databases into Access and then use Access as your datasource for the CR command? Not sure. Maybe someone else will jump in.

-LB

 

[smcnulty2000]

Doing this in crystal: I've just tried several tricks and none of them worked to my satisfaction. I'm not sure if you can do this. I'm all ears, though.

In Oracle, we would create a database link between the two databases and then you could grab data through the side holding the link. Then write the report against that.

Can you do this same thing in SQL Server? I've not seen it but that's not my expertise. I think you might look through your Data Transformation Services (DTS) and see if you can do it there. Failing that use a DTS to pull the data from the data center to your other database as a snapshot and build the Union query in that database as a new view combining the snapshot with the data on your other databases. Similar to Lbass' suggestion of an Access DB.

Regarding the question of how to tell which dataset it comes from. In a union query- since you have to have the same fields and field types- you add an extra field to each part of the union query, and in the first part you hard code some string like 'SourceA' and in the other you hard code a different string; 'SourceB'. And so on if you are unioning multiple views.

So:

select a.*, 'SourceA' source
from alpha a
union all
select b.*, 'SourceB' source
from beta b

This will return the entire data set with a new column called 'source' that will show which dataset the record came from.

Here it is important to use UNION ALL rather than UNION. UNION returns unique records (deduping), but since we're adding a source tag to each record they will never be the same from a to b. If you think you have dupes in a then you could add the 'distinct' phrase just to a. Likewise for b. UNION ALL would therefore decrease the load on the server.

This works in a command object as well.

 

[retygh45]

WOW! Thanks for all the great responses, I really appreciate it. I did get this to work correctly: I had to add the linked server in SQL (under security) to the remote server DB. This allows me to use the UNION query to pull the results down from 1 ODBC connection. Thanks again.

2nd Question: I have them grouped by "Source" column with a total, which is nice: Total from db1 vs. total from db2. Is there a way to only show the differential records if the totals don't match? Something like:

If db1.total > db2.total, then show only the records that are different? And vice-versa?

Thanks again!!

 

[lbass]

If you go to report->selection formula->GROUP and enter:

distinctcount({command.field},{command.source}) <>
distinctcount({command.field})

...it would display those source groups that have fewer values of the field than the report as a whole has.

-LB