Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Union Abfrage gruppieren

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Hazel4tw1, 5 August 2014.

  1. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Hallo zusammen,

    Ich habe mir eine Abfrage gebastel die so aussieht:

    Code:
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,
      Sum(CASE WHEN Send_To_Dispatch_Reason IS NULL and ServiceTickets.Service_Organisation_Displayname = 'CSC_NOC' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS nocsolved, '' AS completsol, '' AS ausnoc
    FROM ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)
    Union
    SELECT Day(ServiceTickets.Creation_Date) AS Tag,
      Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' THEN 1 ELSE 0 END) AS erstellt,'' as nocsolved,'' AS completsol,'' AS ausnoc
    FROM ServiceTickets
    WHERE Month(ServiceTickets.Creation_Date) = 7 AND Year(ServiceTickets.Creation_Date) = 2014
    GROUP BY Day(ServiceTickets.Creation_Date)
    union
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved,
      Sum(CASE WHEN CSC_NOC = '1' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS completsol,'' AS ausnoc
        FROM ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)
    union
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved, '' AS completsol,
      Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' and Send_To_Dispatch_Reason IS NOT NULL THEN 1 ELSE 0 END) AS ausnoc
      from ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)
    Das Ergbniss sieht so aus:

    upload_2014-8-5_14-40-53.png


    Jetzt möchte ich natürlich die "Tage" nicht immer doppelt haben, und nach kruzem googeln, soll man wohl so Union Abfragen gruppieren können:

    Code:
    select * from (
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,
      Sum(CASE WHEN Send_To_Dispatch_Reason IS NULL and ServiceTickets.Service_Organisation_Displayname = 'CSC_NOC' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS nocsolved, '' AS completsol, '' AS ausnoc
    FROM ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)
    Union
    SELECT Day(ServiceTickets.Creation_Date) AS Tag,
      Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' THEN 1 ELSE 0 END) AS erstellt,'' as nocsolved,'' AS completsol,'' AS ausnoc
    FROM ServiceTickets
    WHERE Month(ServiceTickets.Creation_Date) = 7 AND Year(ServiceTickets.Creation_Date) = 2014
    GROUP BY Day(ServiceTickets.Creation_Date)
    union
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved,
      Sum(CASE WHEN CSC_NOC = '1' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS completsol,'' AS ausnoc
        FROM ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)
    union
    SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved, '' AS completsol,
      Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' and Send_To_Dispatch_Reason IS NOT NULL THEN 1 ELSE 0 END) AS ausnoc
      from ServiceTickets
    WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
    GROUP BY Day(ServiceTickets.Solved_Date_Time)) as test
    group by
    test.tag,test.erstellt,test.nocsolved,test.completsol,test.ausnoc
    Aber das Ergebnis ändert sich dabei nicht, was habe ich denn da falsch verstanden, oder ist dies ganricht möglich?
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Du willst sicherlich nach Tag gruppieren und die anderen Felder summieren. Aber warum tust Du es nicht?
     
  3. Hazel4tw1

    Hazel4tw1 Aktiver Benutzer

    Möp, manchmal seh ich den Wald vor lauter Bäumen nicht, danke.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden