use hstdads
go
SELECT old.ads_data_set_name -- , old.ads_file_count, new.ads_file_count
FROM   archive_data_set_all old, archive_data_set_all new, fos_ref_data
WHERE  old.ads_data_set_name = new.ads_data_set_name
and  old.ads_data_set_name = fsr_data_set_name
AND    old.ads_archive_class = new.ads_archive_class
AND    old.ads_data_receipt_time = (
     SELECT MAX(olda.ads_data_receipt_time)
     FROM   archive_data_set_all olda
     WHERE  olda.ads_data_set_name = old.ads_data_set_name
     AND    olda.ads_archive_class = old.ads_archive_class
     AND    olda.ads_data_receipt_time < "May 1 2003"
)
AND    new.ads_data_receipt_time = (
     SELECT MAX(newa.ads_data_receipt_time)
     FROM   archive_data_set_all newa
     WHERE  newa.ads_data_set_name = new.ads_data_set_name
     AND    newa.ads_archive_class = new.ads_archive_class
)
AND old.ads_data_set_name LIKE 'Y%'
AND old.ads_archive_class LIKE 'CAL'
AND ( old.ads_file_count > new.ads_file_count )
and fsr_grndmode != "TARGET ACQUISITION"
go

