select record if criteria 1, unless criteria 2

[lpa]

I am trying to refine a select for a report. sample data:

urn status date on staff last status date
2059 A 10/8/1997 <NULL>
2059 T 10/8/1997 1/10/02
1050 T 1/1/1996 9/30/01

I need to select all records where status is 'T' and last status date is greater than 1/1/2000 UNLESS there is another record with the same urn and the status of that record is 'A'

So in my example, my expectation is that only the record for urn 1050 would be returned.

Any suggestions?

thank you.

Paul

 

[synapsevampire]

This is Crystal version and database dependent, please include technical information in future posts.

Using SQL you might use a subquery to determine if the urn group has a status of A, which would be the most efficient manner, within Crystal you might use a formula to and suppression to hide them, or even use a subreport.

-k

 

[lpa]

crystal 9.0
SQL database 7.0

 

[lbass]

Is the last status date always null for records with status &quot;A&quot;? Assuming this is true, then you could try:

(isnull({table.laststatusdate}) or
{table.laststatusdate} = date(0,0,0) or
{table.laststatusdate} >= date(2000, 01, 01)) and
{table.status} in [&quot;A&quot;,&quot;T&quot;]

Group on {table.urn} and then create a formula {@status}:

if {table.status} = &quot;A&quot; then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@status},{table.urn}) = 0

This should return only those &quot;urns&quot; with no &quot;A&quot; value. Once you have used a group select, for further calculations you will need to use running totals, rather than using the more usual summaries, since non-group selected values will still contribute to summaries, though not displayed.

-LB

 

[nagornyi]

select * from Table
where status = 'T' and status_date>'1-JAN-2000'
and urn not in
(select urn from Table where status = 'A')

 

[vidru]

Don't know if this necessarily helps you, but here goes...

I created a table called Urns in SQL Server 2000 with your data, and added the table to a report. Threw the fields on the report, then went to Show SQL Query, and replaced everything in it with this:

SELECT
    T.Urn, T.Status, T.StaffDate, T.LastStatusDate
FROM
    Urns T  
LEFT OUTER JOIN 
    Urns A ON (T.Urn = A.Urn) AND (A.Status = 'A')
WHERE
    (T.Status = 'T') AND (T.LastStatusDate > '1/1/2000')
    AND (A.Urn IS NULL)

-dave

 

[lpa]

Thank you for all the replies...I tried them out, but still having trouble. Not an urgent issue, and we have finally scheduled someone to come in and do training, so I will take it up with them. Thanks again, though...I have found many valuable tips on this page.