SQL Queries and incrementing numbers 1

[winstonep]

Hi,
I'm trying to write a SQL query within Crystal Reports Professional 9 that will retrieve a few important fields from a table and add one new field.
I would like to be able to return this in the query but if I have to create a temporary table then I will.
The main issue is, how can I actually put a field in the table (or get the query to generate it) that will be numeric and allow me to add 1 to it every time a record is returned?

For example:

If the query would return the fields Case ID and Request ID with the following values:

Case ID Request ID
200 0001928
203 0001987
224 0001092
247 0000918
255 0009108

I would want to add a field at runtime that would contain the record numbers 1 to 5. The query I currently have is:

SELECT "HPD_HelpDesk"."Case ID+", "CSD_ACT__Call_Actions"."Case ID", "CSD_ACT__Call_Actions"."Create Time", "CSD_ACT__Call_Actions"."Request ID"
FROM "HPD_HelpDesk" "HelpDesk"
INNER JOIN "CSD_ACT__Call_Actions" "CallActions" ON "HelpDesk"."Case ID+"="CallActions"."Case ID"
WHERE "HelpDesk"."Case ID+" = "CallActions"."Case ID"
ORDER BY "CallActions"."Create Time"

My knowledge of SQL is limited so there may be blatant errors here! The join may not be quite correct but I do have to join the two tables as HelpDesk will have the current Case ID+ (unique) and CallActions may have multiple occurences.

Added to this problem is the difficulty I have had in getting anything returned from the Remedy database that contains the data.

Can anyone suggest where I should start?

 

[dwelch35]

Ok, let me first warn you that I use CR8 and I'm unfamiliar with the differences between CR8 and CR9.

Next, you mention "how can I actually put a field in the table (or get the query to generate it)". Being a data dipper, Crystal Reports will not place information into your data tables, only pull data from them.

Now, having said that, you can generate an incrementing number in your report. If you want a simple count of the records, use the Special Field called "Record Number" (Field Explorer/Special Fields/Record Number).

If what you're interested in is a count of fields matching certain criteria, you can create a formula that increments a variable. Here's an example:

<Begin Formula Text>
Shared Numbervar MyCount;
If (insert condition here) then
MyCount := MyCount + 1;

MyCount
<End Formula Text>

If you place the above formula in the detail band of your report, it will display it's value for each record in the report, whether it increments or not.

Hope this helps!

dw

 

[winstonep]

I'm not sure if this will do what I need so here's a bit more info.

The report I'm working on will actually need to limit the number of Actions returned from the CallActions table that are printed. So for example, the way the existing report works for a different database is that there is a line number (effectively record number) in the table that the report just checks. So if I want the last 5 actions and there are 15 actions for the Case ID then I request actions 11 to 15, printing the Action Detail and Request ID fields. As my Remedy database does not have this feature (and may not have for months), I need a quick and easy way to refer to the action number. All I really have to record is the Request ID and an action number or be able to calculate the action number.

Unless there is an alternative, I thought the ideal way to dynamically create something that could be queried for Case ID, Request ID and record number is to create a table through a SQL query. However I've been wondering if there is any Crystal support for arrays i.e. can I build up a dynamic array with the returned values, incrementing a simple record field and then just take the actions I need from the array?

 

[synapsevampire]

If you're dynamically creating this table, then each time you query, the ID would change.

You might run the query from CR 9 to create a temporary table for all rows that have a date up to the current date, adding to the table a unique id, write to another table that keeps track of when you last ran it, and all future executions would only read in new rows (based on some datetime field).

Lousy solution to what appears to be a serious Remedy design flaw.

Try to do all of this on the database, not from Crystal.

-k

 

[winstonep]

Yes I agree that this should be done in Remedy. However getting our team to do this may take a few months and I have perhaps a few weeks in which to get this done.

My preference would be to have a SQL Server query/stored procedure which is part of the database and allows me to query it and return the information I want and therefore make the creation of this report much easier.

Only thing is, I don't know enough about SQL Server to do this. At least if I know how to add an automatically inrementing value to the returned query then I should be able to wrok out the rest.

 

[lyanch]

What do you need the increment for? There may be an easier answer.

Lisa

 

[winstonep]

I need to limit the number of actions returned by Remedy. for each Case ID, there could be any number of actions. What we want to do is limit the number of actions printed to 10 in order to keep the report readable. More than that, we want to print the latest 10 only.

So if anyone has any ideas of a quick fix, please let me know! At the moment I'm leaning towards the need for a SQL Server stored procedure to create a query that does it for me.

 

[lbass]

If this really is a display issue, and you don't mind initially returning all records, then the following should work (substitute your own fields):

1-Group on {CaseID}.

2-Sort by {Action.Date} descending.

3-Create a running total {#ActionDates} which does a count of {Action.Date}, evaluates on every record, and resets on change of {CaseID}. Add this to your details section.

4-In format section->Details->Suppress->E-2 button, place:

{#ActionDates} > 5

-LB