Select

[ErnstNoto]

Hi!

I have a situation where I need to remove certain events based on a certain other events

An example of on a situation/selection. The report is grouped on case numbers. I am only interested in Court claims after a certain event has occured. Might be useful to know that these events also are stored in the same table.

date case code description amount
20050802 C10147 680 Court claim1 sent 845
20061107 C10147 069 Transport declaration 1 sent 0
20080116 C10147 067 Transport declaration 2 Credit sent 0
20080909 C10147 680 Court claim sent 860

In this case theres been sendt 2 courtclaims. But, the first one is sendt before a certain code/event, in this case "069 transport declaration". Therefor, this is an event i am not interessted in. I am only interested in court claim 2 and the amount on this one so I can certain calculations.

It is tricky because all this info is in the same table. It contains the history on cases. If I could somehow say "If date > date on a certain code, that might help. Something like "If date > date on code In ["069","...."]". But this would mean I need to compare fields on the same line. Code and date on specific events, and I dont know how to do that.

Hope this was somewhat understandable and that someone has a solution.

Best Regards
Ernst Noto

 

[lbass]

You could add the same table a second time and link it on the case field and on the date field. Limit the records in the original table to those with codes including [067,069,etc]. Limit the second table to those with code 680 (etc). Use an equal join for the case field and use an equal join, but with a < join type, for the date field. Then the table_1 alias table should return only codes 680 with dates greater than those for the 067, 069 codes.

-LB

 

[ErnstNoto]

Hi!

Thank you!

I have however been working on adding the history table a second time. I did not link it the way you suggested but acomplished more or less the same with formulas. But I then got another problem. The values I am interested in, appeared twice. I now tried linking it the way you suggested, and I then accomplish more or less what I want without formulas, which is rather nice. But, the values also appear twice here. But not always it seems, which makes it wierd?

On one case the lines should appear as following:
Case____Date______Code_Description__________Amount
A38421__20070731__840__Reduced court claim__-860
A27421__20080515__686__Court Claim___________860

But for som reason it appears as following:
Case____Date______Code_Description__________Amount
A38421__20070731__840__Reduced court claim__-860
A27421__20080515__686__Court Claim___________860
A27421__20080515__686__Court Claim___________860

The last court claim appears twice and I dont understand why that particular one shows up twice and not 840 as well. If both showed up twice, I might have made some sense of it. A code may appear twice on a case, and if so I am interested, but it seems as if it returns the value of the last one it hits twice. Which means that on a case where there are 2 codes I am interested in, the report will return 3 because the second is retured twice. Ideas?

Best Regards
Ernst Noto

 

[ErnstNoto]

Also, if the case has only one 686 code, it gets returned twice as well!



Best Regards
Ernst Noto

 

[IanWaterman]

In File-> Report Options

Check select distinct records

Ian

 

[ErnstNoto]

After further checking, it seems even stranger. It does not seem to be only when hitting the last code it returns more than one line. Sometimes it's the first and sometimes all hits gets returned more than once. In one case where there should be only one, 4 gets returned. I dont get it!

Ernst

Best Regards
Ernst Noto

 

[lbass]

If they both meet the criteria of having a date greater than the date of the other codes, then they would both appear. I think you need to show what other fields are in the record that might be causing this--try to detect what is different about the records that at first appear to be the same.

-LB

 

[ErnstNoto]

If there actually are 2 codes that I am interested in on a case that meet the criteria, they should show. But the problem I am having is that the report returns one or more of the codes several times even though it only exists one instance of the code on the case. I will try to describe the entire report:

I have 3 distinct tables in the report where history is added twice, which makes 4 tables.

Table 1: Company
Table 2: Case
Table 3: History
Table 4: History_1

This is linked as following:

Company and Case: Inner Join on Company_nr
Case and History: Inner Join on Case_nr
History and History_1: Inner join on case_nr and inner join on date with a < join.

I then have a select on Country_Code in Company table: <> NO

And I have a select on history table: {History.History_Code} in ["077", "067", "061", "071", "070", "069"]

And I then have the following in history_1:
{status_1.Status_Kode} in ["019", "020", "021", "022", "023", "024", "025", "026", "047", "049", "055", "679", "680", "681", "686", "690", "691", "840", "A11", "A12", "A13", "A52", "A53", "A56", "A70"]

Fields:
Company_Nr, _Name and _CountryCode
History_Case_nr
History_1_Date
History_1_Code
History_1_Description
History_1_Amount

So most fileds are from History_1.

And to specify: If I look up a case to view it in the actual program, it will show

Case____Date______Code_Description__________Amount
A38421__20070731__840__Reduced court claim__-860 A27421__20080515__686__Court Claim___________860


But in the report, it shows this:
Case____Date______Code_Description__________Amount
A38421__20070731__840__Reduced court claim__-860 A27421__20080515__686__Court Claim___________860
A27421__20080515__686__Court Claim___________860

And there are several different variations. The only thing that is constant, is that there is not one case that comes up correctly. I dont understand why it returns more values than there actually is?

Ernst




Best Regards
Ernst Noto

 

[lbass]

I didn't look at your last post carefully, but I think the issue is that there are multiple records in the initial table, so one record in the second table appears for each record in the first table. So try adding the code field from the first table to the detail section and see if that is the issue.

If so, you can create a SQL expression {%maxdt} to limit the first table to one row like this (varies based on database and Crystal version and I don't know either--you should always specify this):

(
select max(`date`)
from History A
where A.`Case` = History.`Case` and
A.`Code` in ('077', '067', '061', '071', '070', '069')
)

Change the punctuation as appropriate to your datasource. Leave the 'A' as is, as it acts as an alias table just for the expression. If using v.9 to XI, do not add table name within the summary parens. If using CR2008, use A.`date` within the parens.

Then your selection criteria would look like:

{Company.Country_Code} <> "NO" and
{History.History_Code} in ["077", "067", "061", "071", "070", "069"] and
{History.Date} = {%maxdt}

-LB

 

[ErnstNoto]

Hi!
The issue seems to be exactly what you describe!

My Crystal version is 11.5.12.1838

But when it comes to creating a SQL statement in crystal, I am not sure how to do this.

EN

Best Regards
Ernst Noto

 

[ErnstNoto]

Think I might have found out where - But, not sure I understand the statement you showed.

Here is a simple sql from my database:

SELECT history.history_code, history.history_date, history.history_CaseNr, history.history_casenr
FROM Client.dbo.history history, Client.dbo.history history_1
WHERE history.history_Casenr = history_1.history_casenr AND ((history.history_Code In ('077','067','061','071','070','069')))

Is it possible for you to write the sql you provided as it should be from this info?

(PS! I am a novice at this and I greatly appreciate the help)




Best Regards
Ernst Noto

 

[ErnstNoto]

Tried to create sql by entering database expert and "adding Command" _ Is this the right place?

Tried to write the sql

(select max("History_Date")
from History A where A.History_Casenr =History_Casenr and
A.history_Code In ('077','067','061','071','070','069'))

(At this point I am just trying and guessing - No idea what I'm doing!)

This command then appear as a new table named "Command" and has one field called "Expr1000" I then go to linking.

I then try to browse field in this new table and it showes todays date - The highest date I guess.

I then try to link this - No idea to what really, but since the field in the command showes a date I guessed inner join on history_date and Expr1000

Then I close the database expert and go to Select Expert to try and make as you suggested, which meant removing the select from history table_1

So then it went from this:

{history.history_code} in ["077", "067", "061", "071", "070", "069"] and
{history_1.history_code} in ["019", "020", "021", "022", "023", "024", "025", "026", "047", "049", "055", "679", "680", "681", "686", "690", "691", "840", "A11", "A12", "A13", "A52", "A53", "A56", "A70"] and
{Company.Country_Code} <> "NO"

To this:

{history.history_code} in ["077", "067", "061", "071", "070", "069"] and
{Company.Country_Code} <> "NO" and
{History.history_date}={Command_1.Expr1000}

Tried running the report and it showes no result. But now I have no idea what I am doing so please point out what and where I go wrong.

Thank you

Best Regards
Ernst Noto

 

[lbass]

No, just go into the field explorer->SQL expression-> and enter it there. Try the following exactly in this area:

(
select max("History_Date")
from History A
where A."History_Casenr" =History."History_Casenr" and
A."history_Code" In ('077','067','061','071','070','069')
)

What is your database? Oracle? What? If you are in the SQL expression and you add a field from the field list to the expression, does it have double quotes like this?

-LB

 

[ErnstNoto]

Ok!

I tried this now and after trying a bit the following sql didnt give any errors:

(select max("History_Date")from Status A where A."History_Casenr" = "History_Casenr"
and A."History_Code" In ('077','067','061','071','070','069'))

I then added {History.History_Date} = {%maxdt}
to my select so that became as following:

{History.History_Code} in ["077", "067", "061", "071", "070", "069"] and
{History_1.History_Code} in ["019", "020", "021", "022", "023", "024", "025", "026", "047", "049", "055", "679", "680", "681", "686", "690", "691", "840", "A11", "A12", "A13", "A52", "A53", "A56", "A70"] and
{Company.Company_CountryCode} <> "NO" and
{History.History_Date} = {%maxdt}

Ran the report, but it came out blank.

The database is MS SQL.

Best Regards
Ernst Noto

 

[ErnstNoto]

Just checked the new sql expression by putting the field into the report, and the info this field shows is the max date in the database, which means today.

Not sure if I understand why it shows todays date because if I understand the statement correctly, it should show the max date of one of the events on each case with code "077", "067", "061", "071", "070", "069"]. Not every case has an event today, so where and why this expression returns todays date, I dont know. I gather I have done something wrong.

But I think Maybe I have solved it another way:
1) I linked the tables as you suggested
2) To not show the duplicate amount values i did the following:
I made a formula saying: {history.history_Date} = minimum({history.history_Date},{history_1.history_casenr})

This way i get the value TRUE on the first Transportdeclaration on a case.

3) I am then only interested in the amounts on the events after this date - I then made a formula saying:

IF {history_1.history_Code} In ["019", "020", "021", "022", "023", "024", "025", "026", "047", "049", "055", "679", "680", "681", "686", "690", "691", "840", "A11", "A12", "A13", "A52", "A53", "A56", "A70"]
and {@04 Mindate Transport} = TRUE then {history_1.history_amount}

I need to check the results carefully, but I think this might have solved the problem for now.

I do now however have a problem making a summary on this amount field i made. It wont show up in the summary functions.??

I would preferred to make it work the way you suggested since that involves less formulas, so I wont quite give up on that.

Best Regards
Ernst Noto

 

[ErnstNoto]

My solution didnt quite solve it anyway! :-(

Best Regards
Ernst Noto

 

[ErnstNoto]

In the statement you've suggested, it seem to be a need for an evalutation within a group. Now it returns the highest date in the database, but if the statement could evaluate the the highest date within a group, this might solve it?

I dont know how to make a group in the statement. Is it possible to add a grouping to youre statement?:

(select max("History_Date")from Status A where A."History_Casenr" = "History_Casenr" and A."History_Code" In ('077','067','061','071','070','069'))



Best Regards
Ernst Noto

 

[lbass]

You are not entering it correctly. Please use the following exactly:

(
select max("History_Date")
from History A
where A."History_Casenr" = History."History_Casenr" and
A."History_Code" In ('077','067','061','071','070','069')
)

This would limit the records to the most recent per casenr.

-LB

 

[ErnstNoto]

Hi!

Now i think it works as i want it to. Need to check the results carefully, but it looks correct now.

Thank you so much!!

Best Regards
Ernst Notø



Best Regards
Ernst Noto