none
Prozentuale Anzahl mit Variablen

    Frage

  • Hallo zusammen,

    ich habe eine Tabelle, in welcher Störfälle aufgelistet sind. (Spalten sind u.a. Datum, Störklasse, bearbeitendes Team)

    Nun sollte ich für eine Auswertung wissen, zu wieviel Prozent eine bestimmte Klasse (Ersatzteil) pro Monat pro Team auftaucht.

    Habe mal mit dem folgenden Statement versucht, hat aber leider nicht geklappt.

    Hat jemand ne Idee:

    DECLARE @Ticketuebersicht AS Table (Klasse varchar(50), Team nvarchar(50),Datum datetime);
    DECLARE @total int;
    
    INSERT INTO @Ticketuebersicht (Klasse, Team, Datum) 
    select Klasse, Team, Erstellt_am from Ticketuebersicht_komplett where datepart(year, Erstellt_am) = 2018;
    
    with Vorbereitung as
    (
    SELECT DATEPART(Year, Datum) as 'Jahr', DATEPART(MONTH, Datum) as 'Monat', Klasse, Team, total = 
    case 
    	when Team = 'Team1' then (SELECT COUNT(*) AS Gesamt FROM @Ticketuebersicht where Team like '%Team1')
    	when Team = 'Team2' then (SELECT COUNT(*) AS Gesamt FROM @Ticketuebersicht where Team like '%Team2')
    	else 1
    end
    from @Ticketuebersicht
    where Klasse like '%Ersatzteile')
    
    SELECT Klasse, Team, DATEPART(Year, Erstellt_am) as 'Jahr', DATEPART(MONTH, Erstellt_am) as 'Monat'
    	  ,COUNT(Klasse) as Anzahl
          ,CONVERT(decimal(18, 2), (100.0 * COUNT(*) / MAX(total.Gesamt))) AS Prozent
    FROM @Ticketuebersicht, total
    GROUP BY DATEPART(Year, Datum), DATEPART(MONTH, Datum), Klasse, total.gesamt
    order by DATEPART(Year, Datum), DATEPART(MONTH, Datum), Prozent DESC

    Danke

    Freitag, 9. November 2018 07:48

Antworten

  • Probier's mal hiermit:

    DECLARE @Ticketuebersicht AS TABLE
    (
        Team   nvarchar(50),
        Klasse nvarchar(50),
        Datum  datetime
    );
    
    INSERT INTO @Ticketuebersicht
    (
        Team, Klasse, Datum
    )
    VALUES
    ( 'Software', 'Ersatzteil', '20180401' ),
    ( 'Software', 'Irgendwas',  '20180501' ),
    ( 'Software', 'Ersatzteil', '20180501' ),
    ( 'Software', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Irgendwas',  '20180401' ),
    ( 'Hardware', 'Ersatzteil', '20180401' ),
    ( 'Hardware', 'Irgendwas',  '20180501' ),
    ( 'Hardware', 'Ersatzteil', '20180501' ),
    ( 'Hardware', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Ersatzteil', '20180503' );
    
    WITH TeamQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Team,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    GROUP BY Team,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    ), FilteredTeamQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Team,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    WHERE    Klasse = 'Ersatzteil'
    GROUP BY Team,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    )
    SELECT 'Ersatzteil' AS Klasse,
           t.Team,
           t.Year,
           t.Month,
           t.NumberOfRows  AS TotalPerTeam,
           ft.NumberOfRows AS TotalPerFilteredTeam,
           100.0 / ( CONVERT( decimal( 18, 3 ), t.NumberOfRows ) / CONVERT( decimal( 18, 3 ), ft.NumberOfRows ) ) AS Percentage
    FROM   FilteredTeamQuery ft
           INNER JOIN TeamQuery t ON t.Team = ft.Team AND t.Year = ft.Year AND t.Month = ft.Month


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Freitag, 9. November 2018 14:08
    Moderator

Alle Antworten

  • Wo ist dein Problem?
    Freitag, 9. November 2018 08:36
  • Das Problem ist, dass das Statement so nicht funktioniert und ich keine Idee mehr habe. Eventuell ist ja der Ansatz falsch und es könnte einfacher gelöst werden.
    Freitag, 9. November 2018 08:42
  • Hallo Timo,

    poste bitte Beispieldaten und das gewünschte Ergebnis zu diesen Daten.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Freitag, 9. November 2018 10:19
    Moderator
  • Hoffe dies reicht, ansonsten kann ich auch eine kleine Tabelle extrahieren:

    kleines Beispiel:

    Team;Klasse;Datum

    Software;Ersatzteil;2018-04-19 10:26:53.000

    Software;Test;2018-04-19 10:26:53.000

    Hardware;Reparatur;2018-05-19 10:26:53.000

    Software;Test;2018-05-19 10:26:53.000

    Hardware;Ersatzteil;2018-05-21 10:26:53.000

    Hardware;Ersatzteil;2018-05-25 10:26:53.000

    Als Ergebnis sollte dann rauskommen:

    Ersatzteil;Software;April 2018; 50%

    Ersatzteil;Software;Mai 2018; 0%

    Ersatzteil;Hardware;April 2018; 50%

    Ersatzteil;Hardware;Mai 2018; 33,33%

    Freitag, 9. November 2018 10:43
  • Hallo Timo,

    die Beispieldaten passen nicht zu dem gewünschten Ergebnis.

    Bspw. ist das Team Hardware im April 2018 gar nicht enthalten, wird aber dennoch mit 50% ausgegeben. Im Mai 2018 ist das Team Hardware in der Klasse Ersatzteil das einzigste, das vorkommt, wird aber nur mit 33,33% ausgegeben.

    Das passt also leider vorn und hinten nicht.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Freitag, 9. November 2018 11:08
    Moderator
  • Sorry, mein Fehler.

    Das Ergebnis sollte so aussehen:

    Ersatzteil;Software;April 2018; 50%

    Ersatzteil;Software;Mai 2018; 0%

    Ersatzteil;Hardware;April 2018; 0%

    Ersatzteil;Hardware;Mai 2018; 66,66%

    Im Mai wurde im Team Hardware 2 von 3 mal Ersatzteil als Klasse gewählt.

    Freitag, 9. November 2018 11:49
  • Hallo Timo,

    ich hab es jetzt mal anders aufgebaut:

    DECLARE @Ticketuebersicht AS TABLE
    (
        Team   nvarchar(50),
        Klasse nvarchar(50),
        Datum  datetime
    );
    
    INSERT INTO @Ticketuebersicht
    (
        Team, Klasse, Datum
    )
    VALUES
    ( 'Software', 'Ersatzteil', '20180401' ),
    ( 'Hardware', 'Ersatzteil', '20180401' ),
    ( 'Software', 'Ersatzteil', '20180501' ),
    ( 'Hardware', 'Ersatzteil', '20180501' ),
    ( 'Software', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Ersatzteil', '20180503' );
    
    WITH ClassQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Klasse,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    GROUP BY Klasse,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    ), ClassAndTeamQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Klasse,
             Team,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    GROUP BY Klasse,
             Team,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    )
    SELECT c.Klasse,
           ct.Team,
           c.Year,
           c.Month,
           c.NumberOfRows  AS TotalPerClass,
           ct.NumberOfRows AS TotalPerClassAndTeam,
           100.0 / ( CONVERT( decimal( 18, 3 ), c.NumberOfRows ) / CONVERT( decimal( 18, 3 ), ct.NumberOfRows ) ) AS Percentage
    FROM   ClassAndTeamQuery ct
           INNER JOIN ClassQuery c ON c.Klasse = ct.Klasse AND c.Year = ct.Year AND c.Month = ct.Month
    

    Damit sollte das so gehen. Die Teams, die in einem Monat nicht vorhanden sind, habe ich mal nicht mit ausgegeben, das ergibt sich ja schon dadurch, dass sie fehlen.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Freitag, 9. November 2018 11:51
    Moderator
  • Hallo Timo,

    passt irgendwie auch nicht.

    Oder soll die Abfrage übergreifend über alle Klassen sein?

    Ich hatte es so verstanden, dass man die Zahlen pro Klasse und Team benötigt und in dem Fall nur für Ersatzteile.

    Also Gesamtmenge Ersatzteile in Bezug zur Menge Ersatzteile pro Team.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Freitag, 9. November 2018 11:55
    Moderator
  • Sieht schon mal sehr gut aus. Und passt fast.

    Wichtig sind für mich nur Ersatzteile. Aber die sollte ich prozentual pro Team haben.

    (Bsp: Wenn Team Hardware 5 Zeilen hat und 4 davon sind Ersatzteile, dann sind das 80%. Unabhängig davon, wieviel in den anderen Teams ist.)

    Hoffe, das war verständlich. (Hab das Gefühl, ich bin heute ein bisschen kompliziert.Sorry)

    Freitag, 9. November 2018 13:41
  • Hallo Timo,

    also ist das unabhängig von den Teams an sich.

    Also wenn bspw. nur 5 Einträge vorhanden sind (alle mit Ersatzteile) und 2 davon Team 1, 3 dann Team 2, hat Team 1 keine 40% und Team 2 60%?


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Freitag, 9. November 2018 13:51
    Moderator
  • Nicht ganz. Es sind mehrere Teams und pro Team mehrere Klassen eingetragen. Und das jeweils pro Monat.

    Mich interessiert dann die Prozentuale Anzahl der Klasse "Ersatzteile" im Verhältnis zu den anderen Klassen. Aber immer nur für ein Team.

    100% sind alle Einträge pro Team pro Monat.

    Freitag, 9. November 2018 14:00
  • Probier's mal hiermit:

    DECLARE @Ticketuebersicht AS TABLE
    (
        Team   nvarchar(50),
        Klasse nvarchar(50),
        Datum  datetime
    );
    
    INSERT INTO @Ticketuebersicht
    (
        Team, Klasse, Datum
    )
    VALUES
    ( 'Software', 'Ersatzteil', '20180401' ),
    ( 'Software', 'Irgendwas',  '20180501' ),
    ( 'Software', 'Ersatzteil', '20180501' ),
    ( 'Software', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Irgendwas',  '20180401' ),
    ( 'Hardware', 'Ersatzteil', '20180401' ),
    ( 'Hardware', 'Irgendwas',  '20180501' ),
    ( 'Hardware', 'Ersatzteil', '20180501' ),
    ( 'Hardware', 'Ersatzteil', '20180502' ),
    ( 'Hardware', 'Ersatzteil', '20180503' );
    
    WITH TeamQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Team,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    GROUP BY Team,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    ), FilteredTeamQuery AS
    (
    SELECT   COUNT( 1 ) AS NumberOfRows,
             Team,
             DATEPART( YEAR,  Datum ) AS Year,
             DATEPART( MONTH, Datum ) AS Month
    FROM     @Ticketuebersicht
    WHERE    Klasse = 'Ersatzteil'
    GROUP BY Team,
             DATEPART( YEAR,  Datum ),
             DATEPART( MONTH, Datum )
    )
    SELECT 'Ersatzteil' AS Klasse,
           t.Team,
           t.Year,
           t.Month,
           t.NumberOfRows  AS TotalPerTeam,
           ft.NumberOfRows AS TotalPerFilteredTeam,
           100.0 / ( CONVERT( decimal( 18, 3 ), t.NumberOfRows ) / CONVERT( decimal( 18, 3 ), ft.NumberOfRows ) ) AS Percentage
    FROM   FilteredTeamQuery ft
           INNER JOIN TeamQuery t ON t.Team = ft.Team AND t.Year = ft.Year AND t.Month = ft.Month


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport


    Freitag, 9. November 2018 14:08
    Moderator
  • Hallo Stefan,

    funktioniert super. Vielen Dank.

    Gruß

    Timo

    Freitag, 9. November 2018 14:51