sqlsql-serverselectsequencestage

SQL Server Select where stage/sequence has been missed or is out of sequence


I have a table that has families_id, date, metric_id

A record gets inserted for each families_id there will be a date & metric_id 1-10.

So there should be 10 records for each families_id, the records get inserted with a date an each should follow on from each other. So metric_id 10 date should be greater than metric_id 6 date.

On mass how can I select where they have

  1. Missed a metric_id
  2. The date for the metric_id 6 is before the date for metric_id 2

Solution

  • use row_number to assign an ordinal to the metric_id and date for each family, then they should match - also metric_id, 1,2,3,4... should match with its calculated row_number(), also 1,2,3,4....

    SELECT IQ.* FROM (SELECT families_id, [date], metric_id, 
            ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY [date]) rn_date,
            ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY metricid) rn_metric FROM YourTable) IQ 
        WHERE IQ.rn_date != IQ.rn_metric;
    
    --should detect wrongly ordered metric_ids
    SELECT IQ.* FROM (SELECT families_id, [date], metric_id, 
            ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY [date]) rn_date,
            ROW_NUMBER() OVER (PARTITION BY families_id ORDER BY metricid) rn_metric FROM YourTable) IQ 
        WHERE IQ.metric_id != IQ.rn_metric;
    

    Another possibility - detect a metricID where the date is earlier for a higher id

        SELECT y1.families_id, y1.metric_id FROM yourtable y1
                        WHERE 
                        EXISTS(SELECT 0 FROM yourtable y2 WHERE y1.families_id = y2.families_id 
                                                                AND
                                                                y2.date < y1.date 
                                                                AND
                                                                y2.metricid > y1.metricid)