SQL Expression that gets minimum date that is > than other date

[dazum]

I am using CR XI
I would like create a SQL Expression to get a minimum Legal Date that is greater than the Case Begin Date for each individual case. The report is grouped by Case Id.
Here is how the data currently shows;
(Group #1 - Case Id) Case Id - 1009699
Begin Date --- Legal Date
10-19-11 --- 6-4-12
10-19-11 --- 11-15-11
10-19-11 --- 3-6-09
(Group #1 - Case Id) Case Id - 1013499
Begin Date --- Legal Date
12-27-11 --- 2-29-12
12-27-11 --- 1-15-12
12-27-11 --- 12-29-11

What I would like to show:
(Group #1 - Case Id) Case Id - 1009699
Begin Date --- Legal Date --- SQL Expession
10-19-11 --- 6-4-12 ------ 11-15-11
10-19-11 --- 11-15-11 ------ 11-15-11
10-19-11 --- 3-6-09 ------- 11-15-11
(Group #1 - Case Id) Case Id - 1013499
Begin Date --- Legal Date --- SQL Expession
12-27-11 --- 2-29-12 ----- 12-29-11
12-27-11 --- 1-15-12 ----- 12-29-11
12-27-11 --- 12-29-11 ----- 12-29-11
Is it possible to use a SQL Expression to accomplish this?

 

[lbass]

Try:

(
select min(`legal date`)
from table A
where A.`legal date` > A.`begin date` and
A.`case ID`= table.`case ID`
)

Replace "table" with your table name.

-LB

 

[dazum]

lbass
I'm sorry, but I didn't mention "LEGAL_DATE" is in a different table than the "BEGIN_DATE" and the "CASE_ID". "LEGAL_DATE" is in a table called Legal_Base, and the "BEGIN_DATE" and the "CASE_ID" are in a table called Case_Status. I tried modifying the Sql Expreesion you sent to accomidate the different tables, but it would error out.

I had another question. Is it possible to use 2 Sql expressions in a report? I noticed a instances where cases have been reopened and has more than 1 "BEGIN_DATE". Will Crystal allow an additional Sql Exppression to get the Max("BEGIN_DATE) WHERE the "CASE_STATUS_CODE" = "OPEN" ("BEGIN_DATE" and the "CASE_STATUS_CODE" are both from the Case_Status table).

 

[lbass]

Please copy the SQL query from database->show SQL query into this thread.

-LB

 

[dazum]

lbass
I am not at work this week and do not have access to the report. I go back to work on 9-24-12, I will post the SQL query then.

 

[dazum]

lbass here is the SQL Query from the report;

SELECT "CASE_STATUS"."CASE_ID", "LEGAL_BASE"."LEGAL_ACTION_CODE", "LEGAL_BASE"."OFFICIAL_DATE", "CASE_STATUS"."BEGIN_EFF_DATE", "CASE_STATUS"."CASE_STATUS_CODE"
FROM "SACWIS"."CASE_STATUS" "CASE_STATUS" LEFT OUTER JOIN "SACWIS"."LEGAL_BASE" "LEGAL_BASE" ON "CASE_STATUS"."CASE_ID"="LEGAL_BASE"."CASE_ID"
WHERE ("CASE_STATUS"."CASE_ID"=1009699 OR "CASE_STATUS"."CASE_ID"=1013499) AND ("LEGAL_BASE"."LEGAL_ACTION_CODE" IS NULL OR ("LEGAL_BASE"."LEGAL_ACTION_CODE"='COMPLAINT' OR "LEGAL_BASE"."LEGAL_ACTION_CODE"='MOTION')) AND "CASE_STATUS"."CASE_STATUS_CODE"='OPEN'
ORDER BY "CASE_STATUS"."CASE_ID", "CASE_STATUS"."BEGIN_EFF_DATE" DESC, "LEGAL_BASE"."OFFICIAL_DATE" DESC

 

[lbass]

Try the following SQL expression as is:

(
SELECT min("OFFICIAL_DATE")
from "SACWIS"."CASE_STATUS" A
left outer join "SACWIS"."LEGAL_BASE" B on
A."CASE_ID"=B."CASE_ID"
where
(
A."BEGIN_EFF_DATE"=
(
select max("BEGIN_EFF_DATE")
from "SACWIS"."CASE_STATUS" A
left outer join "SACWIS"."LEGAL_BASE" B on
A."CASE_ID"=B."CASE_ID"
(
A."CASE_ID"=1009699 or
A."CASE_ID"= 1013499
) and
(
B."LEGAL_ACTION_CODE" IS NULL OR
(
B."LEGAL_ACTION_CODE"='COMPLAINT' OR
B."LEGAL_ACTION_CODE"='MOTION'
)
) AND
A."CASE_STATUS_CODE"='OPEN' and
A."CASE_ID"="CASE_STATUS"."CASE_ID"
)
) and
(
A."CASE_ID"=1009699 or
A."CASE_ID"= 1013499
) and
(
B."LEGAL_ACTION_CODE" IS NULL OR
(
B."LEGAL_ACTION_CODE"='COMPLAINT' OR
B."LEGAL_ACTION_CODE"='MOTION'
)
) AND
A."CASE_STATUS_CODE"='OPEN' and
A."CASE_ID"="CASE_STATUS"."CASE_ID"
)

This might be too complex to be efficient, however.

-LB

 

[dazum]

lbass
I inserted your code into a SQL expression, but at the line; A."BEGIN_EFF_DATE" = it erros out, looking for a "missing right parenthesis". I added a right parenthesis at that point. It then errored out at the line; A."CASE_ID" = 1009699 or A."CASE_ID" = 1013499, indicating a "invalid relational operator".

It would be ok to simplify the expression to just get the minimum LEGAL_BASE.OFFICIAL_DATE that is greater than CASE_STATUS.BEGIN_EFF_DATE