Community
    • Categories
    • Recent
    • Popular
    • Users
    • Search
    • Register
    • Login
    1. Home
    2. v.schardt
    V
    Offline
    • Profile
    • Following 0
    • Followers 0
    • Topics 0
    • Posts 1
    • Groups 0

    v.schardt

    @v.schardt

    0
    Reputation
    54
    Profile views
    1
    Posts
    0
    Followers
    0
    Following
    Joined
    Last Online
    Age 24

    v.schardt Unfollow Follow

    Latest posts made by v.schardt

    • RE: Lizenzen in Benutzung

      Hallo zusammen,

      ich habe noch ein Variante erstellt. Der Report ist voreingestellt nach dem Status abwärts sortiert. Mlögliche Stati sin 'Alle Lizenzen frei', 'Alle Lizenzen in Benutzung', 'Lizenzen verfuegbar (X Lizenzen)' und 'Lizenzen überbucht (X Lizenzen)'. So stehen die überbuchten Lizenzen immer als erstes in der Liste.

      
      SELECT
          obj_main.isys_obj__id AS __id__,
          obj_main.isys_obj__title AS 'Software',
          j1.isys_cats_lic_list__key AS 'Lizenzschluessel',
          CASE
            WHEN j1.isys_cats_lic_list__type = 2 THEN 'Volumenlizenz'
            WHEN j1.isys_cats_lic_list__type = 1 THEN 'Einzellizenz'
          END AS 'Lizenztyp',
          j1.isys_cats_lic_list__amount AS 'Anzahl Lizenzen',
          COUNT(j3.isys_catg_application_list__id) AS 'Lizenzen in Benutzung',
          CASE
            WHEN COUNT(j3.isys_catg_application_list__id) = 0 THEN 'Alle Lizenzen frei'
            WHEN COUNT(j3.isys_catg_application_list__id) = j1.isys_cats_lic_list__amount THEN 'Alle Lizenzen in Benutzung'
            WHEN COUNT(j3.isys_catg_application_list__id) < j1.isys_cats_lic_list__amount THEN CONCAT('Lizenzen verfuegbar (', j1.isys_cats_lic_list__amount - COUNT(j3.isys_catg_application_list__id), ' Lzenzen)')
            WHEN COUNT(j3.isys_catg_application_list__id) > j1.isys_cats_lic_list__amount THEN CONCAT('Lizenzen überbucht (', COUNT(j3.isys_catg_application_list__id) - isys_cats_lic_list__amount, ' Lizenzen)')
          END AS 'Status'
      
        FROM isys_obj AS obj_main
          LEFT JOIN isys_cats_lic_list AS j1 ON j1.isys_cats_lic_list__isys_obj__id = obj_main.isys_obj__id
          LEFT JOIN isys_obj_type AS j2 ON j2.isys_obj_type__id = obj_main.isys_obj__id
          LEFT JOIN isys_catg_application_list AS j3 ON j3.isys_catg_application_list__isys_cats_lic_list__id = j1.isys_cats_lic_list__id
          LEFT JOIN isys_connection AS j4 ON j4.isys_connection__id = j3.isys_catg_application_list__isys_connection__id
          LEFT JOIN isys_obj AS j5 ON j5.isys_obj__id = j4.isys_connection__isys_obj__id
          LEFT JOIN isys_obj AS j6 ON j6.isys_obj__id = j3.isys_catg_application_list__isys_obj__id
      
        WHERE 
          IFNULL(j6.isys_obj__status, 2) = 2 AND
          obj_main.isys_obj__status = 2 AND
          j1.isys_cats_lic_list__status = 2 AND
          obj_main.isys_obj__isys_obj_type__id = 33
      
        GROUP BY j1.isys_cats_lic_list__id
      
        ORDER BY
          CASE
            WHEN COUNT(j3.isys_catg_application_list__id) = 0 THEN 4
            WHEN COUNT(j3.isys_catg_application_list__id) = j1.isys_cats_lic_list__amount THEN 3
            WHEN COUNT(j3.isys_catg_application_list__id) < j1.isys_cats_lic_list__amount THEN 2
            WHEN COUNT(j3.isys_catg_application_list__id) > j1.isys_cats_lic_list__amount THEN 1
          END
      ;
      
      

      LG Volker Schardt

      posted in Betrieb
      V
      v.schardt