寻求Crystal Reports中按日期时间匹配筛选数据的代码实现帮助

Hey there! Let's work through your Crystal Reports data matching requirements step by step. I'll break this down into two core scenarios you mentioned: finding exact date-time matches, and identifying records with overlapping date-time ranges.

If you need to pull pairs (or more) of records that share the exact same date and time, here are two reliable approaches:

Option 1: Use a Self-Join in the Database Expert #

1. Open the Database Expert, add your data table twice, and assign aliases (e.g., MainTable and MatchingTable).
2. Set up a link between the two tables using your date-time field, plus a condition to exclude the same record:

   MainTable.EventDateTime = MatchingTable.EventDateTime AND MainTable.RecordID <> MatchingTable.RecordID
   

3. To avoid duplicate pairs (e.g., Record A paired with Record B and vice versa), add this to your Record Selection Formula:

   MainTable.RecordID < MatchingTable.RecordID
   

   This ensures each pair only appears once in your results.

Option 2: Use a Count-Based Selection Formula #

If you don't want to set up a self-join, you can filter records where the same date-time occurs at least twice:

Count({YourTable.EventDateTime}, {YourTable.EventDateTime}) >= 2

You can then group by EventDateTime to see all matching records grouped together.

Pro Tip: If your date-time fields include milliseconds that you want to ignore (e.g., two records are identical except for millisecond differences), adjust the match condition to compare date, hour, and minute separately:

Date({MainTable.EventDateTime}) = Date({MatchingTable.EventDateTime}) 
AND Hour({MainTable.EventDateTime}) = Hour({MatchingTable.EventDateTime}) 
AND Minute({MainTable.EventDateTime}) = Minute({MatchingTable.EventDateTime})

For records that have overlapping date-time windows (e.g., one starts before another ends and vice versa), we'll use a similar self-join approach, but with range-based conditions.

Assuming your table has StartDateTime and EndDateTime fields:

1. Add your table twice to the Database Expert (aliases like RangeTable1 and RangeTable2).
2. Set up the join condition to detect overlaps:

   RangeTable1.StartDateTime <= RangeTable2.EndDateTime 
   AND RangeTable1.EndDateTime >= RangeTable2.StartDateTime 
   AND RangeTable1.RecordID <> RangeTable2.RecordID
   

3. To flag overlapping records in your report, create a formula field (e.g., IsOverlapping):

   If ({RangeTable1.StartDateTime} <= {RangeTable2.EndDateTime}) 
   AND ({RangeTable1.EndDateTime} >= {RangeTable2.StartDateTime}) 
   AND ({RangeTable1.RecordID} <> {RangeTable2.RecordID}) 
   Then "Overlap Detected" 
   Else "No Overlap"
   

4. Use this formula to filter records or highlight overlapping entries in your report (via conditional formatting).

Quick Notes to Avoid Headaches #

• Make sure your date-time fields are stored as DateTime types (not strings) — string comparisons can lead to unexpected matches or misses.
• For large datasets, self-joins might impact performance. If that's an issue, consider pre-filtering data at the database level (e.g., using a SQL command instead of direct table access).

内容的提问来源于stack exchange，提问作者byoung