Return records based on grouping

[steve053]

I would like to create a command in Crystal 10 using SQL 2000. I am new to Crystal reports (< 1 year) and have very little experience with SQL code. Any and all help is appreciated.

When a charge is entered it creates a new record with a unique TX_ID for the charge. This new charge is also assigned a DETAIL_TYPE = 1 in the same row. The service date of the charge is ORIGINAL_SERVICE_DATE.

When the charge is voided a new record is created with the same TX_ID and ORIGINAL_SERVICE_DATE, and assigned a DETAIL_TYPE = 10.

Other actions to the charge create additional rows in the table, but with DETAIL_TYPES other than 1 and 10. DETAIL_TYPES 1 and 10 are unique within the TX_ID grouping. If the same charge is reposted (after a void), a new TX_ID is assigned.

What I want to do is create a list of all TX_ID’s that have not been voided (i.e. only have DETAIL_TYPE = 1 associated with the TX_ID), and pass a user prompted beginning and ending ORIGINAL_SERVICE_DATE.

All of the fields are in the same table "TDL".

Any help writing the query is appreciated.

Thanks.

Steve

 

[maswien]

try this, (not tested)

select TX_ID from TDL 
where DETAIL_TYPE in (1, 10)
group by TX_ID
having count(*) = 1

 

[steve053]

Thank you maswien!

I'm testing it now and will let you know how it turns out.

Steve