none
Gruppierung anhand verschiedene Statien RRS feed

  • Frage

  • Hallo liebe Forumsmitglieder,

    ich habe folgende Abfrage:

    WITH [t1] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000073', 0 UNION ALL
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch2', '2019-10-17 21:00:00.0000000', '000000073', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000074', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    )
    
    SELECT * FROM [t1]


    Ich habe bei einer Seriennummer zum Beispiel Status 0 und 3. Sollte das der Fall sein, sollte aus der Gruppe der Status 3 genommen werden, sollte jedoch in einer anderen Gruppe nur 0 sein, dann sollte er den Status nehmen mit dem kleinsten Datumswert?

    Jeder Versuch das mit MIN() und jeder Gruppierung, sowie mit HAVING zu filtern ist bei jeglich gescheitert. Könnt Ihr mit Tipps geben wie ich es lösen könnte?

    Am Ende sollte oben die AUSGABE so aussehen:

    WITH [t2] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    )
    
    SELECT * FROM [t2]

    Vielen Dank im Voraus.

    Gruß dash

    Freitag, 11. Oktober 2019 11:06

Antworten

  • Auch wenn das Ergebnis von Olaf so aussieht wie gewünscht, fürchte ich doch, dass nicht die gewünschten Bedingungen erfüllt wurden. Hier ein anderer Ansatz, den ich zweigeteilt habe, um erst einmal die ermittelten Kennzeichen zu zeigen, der zweite Teil liefert dann das Ergebnis.

    Hierbei gehe ich aber nicht auf Status 3 ein, sondern nehme den größten Status. Das müsste man ggf. noch in der Where-Klausel filtern, wenn nur 0 und 3 genommen werden sollen!

    -- Ermittle erst einmal die Kennzeichen
    WITH [t1] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000073', 0 UNION ALL
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch2', '2019-10-17 21:00:00.0000000', '000000073', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000074', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    )
    Select [MaschID],[Beginn],[Seriennummer],[Status],
    	case 
    		when exists(Select * from t1 b where b.MaschID <> t1.MaschID and b.Seriennummer = t1.Seriennummer and b.Status = 3) then 1
    		else 0
    	end as Status_3_Nehmen,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Beginn asc) as rn_Datum,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Status desc) as rn_Status
    from t1
    order by MaschID, Seriennummer, Beginn;
    
    -- Jetzt etwas komplexer mit Verarbeitung der Kennzeichen
    WITH [t1] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000073', 0 UNION ALL
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch2', '2019-10-17 21:00:00.0000000', '000000073', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000074', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    ),
    Kennzeichen as
    (
    Select [MaschID],[Beginn],[Seriennummer],[Status],
    	case 
    		when exists(Select * from t1 b where b.MaschID <> t1.MaschID and b.Seriennummer = t1.Seriennummer and b.Status = 3) then 1
    		else 0
    	end as Status_3_Nehmen,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Beginn asc) as rn_Datum,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Status desc) as rn_Status
    from t1
    )
    Select [MaschID],[Beginn],[Seriennummer],[Status]
    from Kennzeichen
    where (rn_Status = 1 and Status_3_Nehmen = 1)
    or (rn_Datum = 1 and Status_3_Nehmen = 0)
    Order by MaschID, Seriennummer;
    

    Ein bisschen schwierig ist das Beispiel mit Seriennummer 74, den hier ist bei Masch2 das kleinste Datum auch gleichzeitig der Status 3. Daher passt das Ergebnis von Olaf auch, wenn man immer die mit Status 3 nimmt.


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 11. Oktober 2019 13:35

Alle Antworten

  • Hallo,

    wie ist den hier der Primary Key definiert?

    Mal davon ausgehend es ist MaschID + Seriennummer + Status dann bekommst Du das Ergebnis mit

    SELECT * 
    FROM t1
    WHERE [Status] = 3
    
    UNION ALL
    
    SELECT *
    FROM t1 AS main
    WHERE [Status] = 0
          AND NOT EXISTS (SELECT 1 FROM t1 AS sub 
                          WHERE sub.MaschID = main.MaschID 
                                AND sub.Seriennummer = main.Seriennummer
                                AND sub.Status = 3)
    ORDER BY MaschID, [Status], Seriennummer;


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Freitag, 11. Oktober 2019 12:09
  • Auch wenn das Ergebnis von Olaf so aussieht wie gewünscht, fürchte ich doch, dass nicht die gewünschten Bedingungen erfüllt wurden. Hier ein anderer Ansatz, den ich zweigeteilt habe, um erst einmal die ermittelten Kennzeichen zu zeigen, der zweite Teil liefert dann das Ergebnis.

    Hierbei gehe ich aber nicht auf Status 3 ein, sondern nehme den größten Status. Das müsste man ggf. noch in der Where-Klausel filtern, wenn nur 0 und 3 genommen werden sollen!

    -- Ermittle erst einmal die Kennzeichen
    WITH [t1] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000073', 0 UNION ALL
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch2', '2019-10-17 21:00:00.0000000', '000000073', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000074', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    )
    Select [MaschID],[Beginn],[Seriennummer],[Status],
    	case 
    		when exists(Select * from t1 b where b.MaschID <> t1.MaschID and b.Seriennummer = t1.Seriennummer and b.Status = 3) then 1
    		else 0
    	end as Status_3_Nehmen,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Beginn asc) as rn_Datum,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Status desc) as rn_Status
    from t1
    order by MaschID, Seriennummer, Beginn;
    
    -- Jetzt etwas komplexer mit Verarbeitung der Kennzeichen
    WITH [t1] ([MaschID],[Beginn],[Seriennummer],[Status]) AS (
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 14:00:00.0000000', '000000073', 0 UNION ALL
    SELECT 'Masch1', '2019-12-09 19:00:00.0000000', '000000074', 0 UNION ALL
    SELECT 'Masch1', '2019-12-10 08:00:00.0000000', '000000075', 0 UNION ALL
    SELECT 'Masch1', '2019-10-11 00:00:00.0000000', '000000072', 3 UNION ALL
    SELECT 'Masch1', '2019-10-10 23:00:00.0000000', '000000073', 3 UNION ALL
    
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000072', 0 UNION ALL
    SELECT 'Masch2', '2019-10-17 21:00:00.0000000', '000000073', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000074', 0 Union ALL
    SELECT 'Masch2', '2019-10-17 20:00:00.0000000', '000000075', 0 Union ALL
    SELECT 'Masch2', '2019-09-03 07:00:00.0000000', '000000072', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 08:00:00.0000000', '000000073', 3 Union ALL
    SELECT 'Masch2', '2019-09-03 06:00:00.0000000', '000000074', 3 
    ),
    Kennzeichen as
    (
    Select [MaschID],[Beginn],[Seriennummer],[Status],
    	case 
    		when exists(Select * from t1 b where b.MaschID <> t1.MaschID and b.Seriennummer = t1.Seriennummer and b.Status = 3) then 1
    		else 0
    	end as Status_3_Nehmen,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Beginn asc) as rn_Datum,
    	ROW_NUMBER() OVER(PARTITION BY MaschID, Seriennummer ORDER BY Status desc) as rn_Status
    from t1
    )
    Select [MaschID],[Beginn],[Seriennummer],[Status]
    from Kennzeichen
    where (rn_Status = 1 and Status_3_Nehmen = 1)
    or (rn_Datum = 1 and Status_3_Nehmen = 0)
    Order by MaschID, Seriennummer;
    

    Ein bisschen schwierig ist das Beispiel mit Seriennummer 74, den hier ist bei Masch2 das kleinste Datum auch gleichzeitig der Status 3. Daher passt das Ergebnis von Olaf auch, wenn man immer die mit Status 3 nimmt.


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 11. Oktober 2019 13:35
  • Vielen Dank für die schnelle Rückmeldung und tut mir Leid, dass ich jetzt erst antworten kann.

    Mein Problem war das Ermitteln der Kennzahlen, nachdem ich es jetzt gesehen haben, ergibt es sogar Sinn so vorzugehen. Ich hatte es ständig mit MAX / MIN etc versucht gehabt. An Row_Number() OVER(...) wäre ich glaube ich in den nächsten Monaten noch nicht drauf gekommen.

    Konnte es jetzt so an meine QUERY anwenden und es läuft einwandfrei.

    Gruß dash 

    Montag, 14. Oktober 2019 08:01