SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE VIEW [transform_imr].[nw_gw_be_auf_nw_gw_bestand_cat]
AS
SELECT convert(DATE, '1900-01-01')                               AS "Accounting Date",
       ''                                                        AS "Document Type",
       0                                                         AS "Document Number",
       0                                                         AS "Position In Document",
       0                                                         AS "Customer Number",
       0                                                         AS "Nominal Account Number",
       ''                                                        AS "Is Balanced",
       0                                                         AS "Clearing Number",
       convert(DATE, '1900-01-01')                               AS "Document Date",
       0                                                         AS "Posted Value",
       ''                                                        AS "Debit Or Credit",
       0                                                         AS "Posted Count",
       0                                                         AS "Branch Number",
       0                                                         AS "Customer Contra Account",
       0                                                         AS "Nominal Contra Account",
       ''                                                        AS "Contra Account Text",
       0                                                         AS "Account Form Page Number",
       0                                                         AS "Account Form Page Line",
       0                                                         AS "Serial Number Each Month",
       0                                                         AS "Employee Number",
       convert(DATE, '1900-01-01')                               AS "Invoice Date",
       ''                                                        AS "Invoice Number",
       ''                                                        AS "Dunning Level",
       convert(DATE, '1900-01-01')                               AS "Last Dunning Date",
       0                                                         AS "Journal Page",
       0                                                         AS "Journal Line",
       0                                                         AS "Cash Discount",
       0                                                         AS "Term Of Payment",
       ''                                                        AS "Posting Text",
       ''                                                        AS "Vehicle Reference",
       ''                                                        AS "Vat Id Number",
       0                                                         AS "Account Statement Number",
       0                                                         AS "Account Statement Page",
       ''                                                        AS "Vat Key",
       0                                                         AS "Days For Cash Discount",
       convert(DATE, '1900-01-01')                               AS "Day Of Actual Accounting",
       0                                                         AS "Skr51 Branch",
       0                                                         AS "Skr51 Make",
       0                                                         AS "Skr51 Cost Center",
       0                                                         AS "Skr51 Sales Channel",
       0                                                         AS "Skr51 Cost Unit",
       ''                                                        AS "Previously Used Account No",
       c252                                                      AS "Free Form Accounting Text",
       c251                                                      AS "Free Form Document Text",
       ''                                                        AS "Nom_Account_Is Profit Loss Account",
       '1'                                                       AS "Rechtseinheit",
       ''                                                        AS "Betrieb",
       convert(DATE, '1900-01-01')                               AS "Bookkeep Date",
       ''                                                        AS "Marke",
       ''                                                        AS "Text",
       '1'                                                       AS "Mandant",
       0                                                         AS "Betrag",
       ''                                                        AS "Konto_mit_Bezeichnung",
       0                                                         AS "Menge_FIBU",
       0                                                         AS "Stellen Cost Center",
       ''                                                        AS "KST",
       0                                                         AS "Stellen Sales Channel",
       ''                                                        AS "Absatzkanal",
       0                                                         AS "Stellen Cost Unit",
       ''                                                        AS "Kostenträger_mit_Null",
       ''                                                        AS "Kostenträger",
       ''                                                        AS "GuV_Bilanz",
       ''                                                        AS "Susa",
       ''                                                        AS "Acct Nr",
       0                                                         AS "Stellen_Konto_Nr",
       SUM(0) OVER (PARTITION BY c166)                           AS "Summe_Betrag",
       c167                                                      AS "Internal Number_ori",
       c250                                                      AS "Vin",
       c249                                                      AS "License Plate",
       c248                                                      AS "License Plate Country",
       c247                                                      AS "License Plate Season",
       c246                                                      AS "Make Number",
       c245                                                      AS "Free Form Make Text",
       c244                                                      AS "Model Code",
       c243                                                      AS "Free Form Model Text",
       c242                                                      AS "Is Roadworthy",
       c241                                                      AS "Is Customer Vehicle",
       c240                                                      AS "Dealer Vehicle Type",
       c239                                                      AS "Dealer Vehicle Number",
       c173                                                      AS "First Registration Date",
       c238                                                      AS "Readmission Date",
       c237                                                      AS "Next Service Date",
       c236                                                      AS "Next Service Km",
       c235                                                      AS "Next Service Miles",
       c234                                                      AS "Production Year",
       c233                                                      AS "Owner Number",
       c232                                                      AS "Holder Number",
       c231                                                      AS "Previous Owner Number",
       c230                                                      AS "Previous Owner Counter",
       c229                                                      AS "Last Holder Change Date",
       c228                                                      AS "German Kba Hsn",
       c227                                                      AS "German Kba Tsn",
       c226                                                      AS "Austria Nat Code",
       c225                                                      AS "Is Prefer Km",
       c224                                                      AS "Mileage Km",
       c223                                                      AS "Mileage Miles",
       c222                                                      AS "Odometer Reading Date",
       c221                                                      AS "Engine Number",
       c220                                                      AS "Gear Number",
       c219                                                      AS "Unloaded Weight",
       c218                                                      AS "Gross Vehicle Weight",
       c217                                                      AS "Power Kw",
       c216                                                      AS "Cubic Capacity",
       c215                                                      AS "Is All Accidents Repaired",
       c214                                                      AS "Accidents Counter",
       c213                                                      AS "Has Tyre Pressure Sensor",
       c212                                                      AS "Carkey Number",
       c211                                                      AS "Internal Source Flag",
       c210                                                      AS "Emission Code",
       c209                                                      AS "First Sold Country",
       c208                                                      AS "First Sold Dealer Code",
       c207                                                      AS "Body Paint Code",
       c178                                                      AS "Body Paint Description",
       c206                                                      AS "Is Body Paint Metallic",
       c205                                                      AS "Interior Paint Code",
       c204                                                      AS "Interior Paint Description",
       c203                                                      AS "Trim Code",
       c202                                                      AS "Trim Description",
       c201                                                      AS "Fine Dust Label",
       c200                                                      AS "Internal Assignment",
       c199                                                      AS "Ricambi Free Input",
       c198                                                      AS "Document Number_2",
       c197                                                      AS "Salesman Number",
       c196                                                      AS "Sale Date",
       c195                                                      AS "Next Emission Test Date",
       c194                                                      AS "Next General Inspection Date",
       c193                                                      AS "Next Rust Inspection Date",
       c192                                                      AS "Next Exceptional Inspection Da",
       c191                                                      AS "Last Change Date",
       c190                                                      AS "Last Change Employee No",
       c189                                                      AS "Created Date",
       c188                                                      AS "Created Employee No",
       c187                                                      AS "Last Change Subsidiary",
       MIN(convert(DATE, '1900-01-01')) OVER (PARTITION BY c166) AS "Minimum_Accounting_Date",
       c186                                                      AS "Hauptbetrieb",
       c185                                                      AS "Standort",
       c184                                                      AS "Description_Makes",
       c184                                                      AS "Fabrikat",
       c183                                                      AS "Description_Models",
       c182                                                      AS "Model_Detail",
       c181                                                      AS "Model",
       c180                                                      AS "Fahrzeugtyp",
       c179                                                      AS "Fahrzeugart",
       c178                                                      AS "Farbe",
       c177                                                      AS "Customer Number_Vorbesitzer",
       c176                                                      AS "First Name_Vorbesitzer",
       c175                                                      AS "Family Name_Vorbesitzer",
       c174                                                      AS "Vorbesitzer",
       c173                                                      AS "Zulassungsdatum",
       1                                                         AS "Menge_1",
       COUNT(c239) OVER (PARTITION BY c166)                      AS "Menge_2",
       1 / (COUNT(c239) OVER (PARTITION BY c166))                AS "Menge",
       c172                                                      AS "Einsatz",
       CASE
           WHEN (c170 IS NULL)
               THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
           ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
           END                                                   AS "Standtage_Berechnung",
       (c171) / (COUNT(c239) OVER (PARTITION BY c166))           AS "Standtage",
       CASE
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) BETWEEN 0 AND 30
               )
               THEN ('0 - 30 Tage')
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) BETWEEN 31 AND 60
               )
               THEN ('31 - 60 Tage')
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) BETWEEN 61 AND 90
               )
               THEN ('61 - 90 Tage')
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) BETWEEN 91 AND 180
               )
               THEN ('91 - 180 Tage')
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) BETWEEN 181 AND 360
               )
               THEN ('181 - 360 Tage')
           WHEN (
               (
                   CASE
                       WHEN (c170 IS NULL)
                           THEN ((((c171) / (COUNT(c239) OVER (PARTITION BY c166)))))
                       ELSE ((c169) / (COUNT(c239) OVER (PARTITION BY c166)))
                       END
                   ) > 360
               )
               THEN ('> 360 Tage')
           ELSE NULL
           END                                                   AS "Standtagestaffel",
       (getdate())                                               AS "Heute",
       c168                                                      AS "FZG",
       c166                                                      AS "Vehicle_Reference_berechnet",
       c167                                                      AS "Internal Number"
FROM (SELECT ((dealer_vehicles."dealer_vehicle_type" +
               (rtrim((convert(VARCHAR(50), dealer_vehicles."dealer_vehicle_number")))))) AS c166,
             vehicles."internal_number"                                                   AS c167,
             ((dealer_vehicles."dealer_vehicle_type" +
               (rtrim((convert(VARCHAR(50), dealer_vehicles."dealer_vehicle_number")))))) + ' - ' + (
                 substring((
                               CASE
                                   WHEN (
                                       (
                                           CASE
                                               WHEN (models."description" = 'DIVERSE')
                                                   THEN (vehicles."free_form_model_text")
                                               ELSE (models."description")
                                               END
                                           ) IS NULL
                                       )
                                       THEN ('keine Angabe')
                                   WHEN (
                                       (
                                           CASE
                                               WHEN (models."description" = 'DIVERSE')
                                                   THEN (vehicles."free_form_model_text")
                                               ELSE (models."description")
                                               END
                                           ) LIKE '???%'
                                       )
                                       THEN ('???')
                                   ELSE (
                                       (
                                           CASE
                                               WHEN (models."description" = 'DIVERSE')
                                                   THEN (vehicles."free_form_model_text")
                                               ELSE (models."description")
                                               END
                                           )
                                       )
                                   END
                               ), 1, 170)
                 )                                                                        AS c168,
             day((getdate()) - dealer_vehicles."in_buy_invoice_no_date")                  AS c169,
             dealer_vehicles."in_buy_invoice_no_date"                                     AS c170,
             day((getdate()) - dealer_vehicles."in_arrival_date")                         AS c171,
             dealer_vehicles."calc_basic_charge" + dealer_vehicles."calc_accessory" +
             dealer_vehicles."calc_extra_expenses" + dealer_vehicles."calc_usage_value_encr_external" +
             dealer_vehicles."calc_usage_value_encr_internal" + dealer_vehicles."calc_usage_value_encr_other" +
             dealer_vehicles."calc_total_writedown" + dealer_vehicles."calc_commission_for_arranging" +
             dealer_vehicles."calc_cost_internal_invoices" + dealer_vehicles."calc_cost_other" +
             dealer_vehicles."calc_returns_workshop" +
             (dealer_vehicles."calc_sales_aid" + dealer_vehicles."calc_sales_aid_finish" +
              dealer_vehicles."calc_sales_aid_bonus") * - 1                               AS c172,
             vehicles."first_registration_date"                                           AS c173,
             (substring((convert(VARCHAR(50), customers_suppliers."customer_number")), 1, 7)) + ' - ' +
             customers_suppliers."first_name" + ' ' + customers_suppliers."family_name"   AS c174,
             customers_suppliers."family_name"                                            AS c175,
             customers_suppliers."first_name"                                             AS c176,
             customers_suppliers."customer_number"                                        AS c177,
             vehicles."body_paint_description"                                            AS c178,
             CASE
                 WHEN (
                     (
                         CASE
                             WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('N'))
                                 THEN ('NW')
                             WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('V'))
                                 THEN ('VFW')
                             WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('D'))
                                 THEN ('GW diff.')
                             WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('G'))
                                 THEN ('GW regel.')
                             WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('T'))
                                 THEN ('TZ')
                             ELSE NULL
                             END
                         ) IN ('VFW', 'TZ', 'NW')
                     )
                     THEN ('Neuwagen')
                 ELSE ('Gebrauchtwagen')
                 END                                                                      AS c179,
             CASE
                 WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('N'))
                     THEN ('NW')
                 WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('V'))
                     THEN ('VFW')
                 WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('D'))
                     THEN ('GW diff.')
                 WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('G'))
                     THEN ('GW regel.')
                 WHEN ((substring(vehicles."dealer_vehicle_type", 1, 1)) IN ('T'))
                     THEN ('TZ')
                 ELSE NULL
                 END                                                                      AS c180,
             (
                 substring((
                               upper((
                                   CASE
                                       WHEN (
                                           (
                                               CASE
                                                   WHEN (models."description" = 'DIVERSE')
                                                       THEN (vehicles."free_form_model_text")
                                                   ELSE (models."description")
                                                   END
                                               ) IS NULL
                                           )
                                           THEN ('keine Angabe')
                                       WHEN (
                                           (
                                               CASE
                                                   WHEN (models."description" = 'DIVERSE')
                                                       THEN (vehicles."free_form_model_text")
                                                   ELSE (models."description")
                                                   END
                                               ) LIKE '???%'
                                           )
                                           THEN ('???')
                                       ELSE (
                                           (
                                               CASE
                                                   WHEN (models."description" = 'DIVERSE')
                                                       THEN (vehicles."free_form_model_text")
                                                   ELSE (models."description")
                                                   END
                                               )
                                           )
                                       END
                                   ))
                               ), 1, 3)
                 )                                                                        AS c181,
             CASE
                 WHEN (
                     (
                         CASE
                             WHEN (models."description" = 'DIVERSE')
                                 THEN (vehicles."free_form_model_text")
                             ELSE (models."description")
                             END
                         ) IS NULL
                     )
                     THEN ('keine Angabe')
                 WHEN (
                     (
                         CASE
                             WHEN (models."description" = 'DIVERSE')
                                 THEN (vehicles."free_form_model_text")
                             ELSE (models."description")
                             END
                         ) LIKE '???%'
                     )
                     THEN ('???')
                 ELSE (
                     (
                         CASE
                             WHEN (models."description" = 'DIVERSE')
                                 THEN (vehicles."free_form_model_text")
                             ELSE (models."description")
                             END
                         )
                     )
                 END                                                                      AS c182,
             CASE
                 WHEN (models."description" = 'DIVERSE')
                     THEN (vehicles."free_form_model_text")
                 ELSE (models."description")
                 END                                                                      AS c183,
             makes."description"                                                          AS c184,
             '0' + (convert(VARCHAR(50), dealer_vehicles."in_subsidiary"))                AS c185,
             CASE
                 WHEN (('0' + (convert(VARCHAR(50), dealer_vehicles."in_subsidiary"))) =
                       '015                                                                                                                                                                                                                                                            ')
                     THEN ('15')
                 ELSE (dealer_vehicles."client_db")
                 END                                                                      AS c186,
             vehicles."last_change_subsidiary"                                            AS c187,
             vehicles."created_employee_no"                                               AS c188,
             vehicles."created_date"                                                      AS c189,
             vehicles."last_change_employee_no"                                           AS c190,
             vehicles."last_change_date"                                                  AS c191,
             vehicles."next_exceptional_inspection_da"                                    AS c192,
             vehicles."next_rust_inspection_date"                                         AS c193,
             vehicles."next_general_inspection_date"                                      AS c194,
             vehicles."next_emission_test_date"                                           AS c195,
             vehicles."sale_date"                                                         AS c196,
             vehicles."salesman_number"                                                   AS c197,
             vehicles."document_number"                                                   AS c198,
             vehicles."ricambi_free_input"                                                AS c199,
             vehicles."internal_assignment"                                               AS c200,
             vehicles."fine_dust_label"                                                   AS c201,
             vehicles."trim_description"                                                  AS c202,
             vehicles."trim_code"                                                         AS c203,
             vehicles."interior_paint_description"                                        AS c204,
             vehicles."interior_paint_code"                                               AS c205,
             vehicles."is_body_paint_metallic"                                            AS c206,
             vehicles."body_paint_code"                                                   AS c207,
             vehicles."first_sold_dealer_code"                                            AS c208,
             vehicles."first_sold_country"                                                AS c209,
             vehicles."emission_code"                                                     AS c210,
             vehicles."internal_source_flag"                                              AS c211,
             vehicles."carkey_number"                                                     AS c212,
             vehicles."has_tyre_pressure_sensor"                                          AS c213,
             vehicles."accidents_counter"                                                 AS c214,
             vehicles."is_all_accidents_repaired"                                         AS c215,
             vehicles."cubic_capacity"                                                    AS c216,
             vehicles."power_kw"                                                          AS c217,
             vehicles."gross_vehicle_weight"                                              AS c218,
             vehicles."unloaded_weight"                                                   AS c219,
             vehicles."gear_number"                                                       AS c220,
             vehicles."engine_number"                                                     AS c221,
             vehicles."odometer_reading_date"                                             AS c222,
             vehicles."mileage_miles"                                                     AS c223,
             vehicles."mileage_km"                                                        AS c224,
             vehicles."is_prefer_km"                                                      AS c225,
             vehicles."austria_nat_code"                                                  AS c226,
             vehicles."german_kba_tsn"                                                    AS c227,
             vehicles."german_kba_hsn"                                                    AS c228,
             vehicles."last_holder_change_date"                                           AS c229,
             vehicles."previous_owner_counter"                                            AS c230,
             vehicles."previous_owner_number"                                             AS c231,
             vehicles."holder_number"                                                     AS c232,
             vehicles."owner_number"                                                      AS c233,
             vehicles."production_year"                                                   AS c234,
             vehicles."next_service_miles"                                                AS c235,
             vehicles."next_service_km"                                                   AS c236,
             vehicles."next_service_date"                                                 AS c237,
             vehicles."readmission_date"                                                  AS c238,
             vehicles."dealer_vehicle_number"                                             AS c239,
             vehicles."dealer_vehicle_type"                                               AS c240,
             vehicles."is_customer_vehicle"                                               AS c241,
             vehicles."is_roadworthy"                                                     AS c242,
             vehicles."free_form_model_text"                                              AS c243,
             vehicles."model_code"                                                        AS c244,
             vehicles."free_form_make_text"                                               AS c245,
             vehicles."make_number"                                                       AS c246,
             vehicles."license_plate_season"                                              AS c247,
             vehicles."license_plate_country"                                             AS c248,
             vehicles."license_plate"                                                     AS c249,
             vehicles."vin"                                                               AS c250,
             (rtrim((convert(VARCHAR(50), dealer_vehicles."dealer_vehicle_number"))))     AS c251,
             dealer_vehicles."dealer_vehicle_type" +
             (rtrim((convert(VARCHAR(50), dealer_vehicles."dealer_vehicle_number"))))     AS c252
      FROM (
               (
                   (
                       (
                           LOCOSOFT."dbo"."vehicles" vehicles LEFT OUTER JOIN LOCOSOFT."dbo"."makes" makes
                           ON vehicles."make_number" = makes."make_number"
                           ) LEFT OUTER JOIN LOCOSOFT."dbo"."models" models
                       ON (vehicles."make_number" = models."make_number")
                           AND (vehicles."model_code" = models."model_code")
                       ) LEFT OUTER JOIN LOCOSOFT."dbo"."customers_suppliers" customers_suppliers
                   ON vehicles."previous_owner_number" = customers_suppliers."customer_number"
                   ) LEFT OUTER JOIN LOCOSOFT."dbo"."dealer_vehicles" dealer_vehicles
               ON (dealer_vehicles."dealer_vehicle_type" = vehicles."dealer_vehicle_type")
                   AND (dealer_vehicles."dealer_vehicle_number" = vehicles."dealer_vehicle_number")
               )
      WHERE (
                (dealer_vehicles."in_arrival_date" IS NOT NULL)
                    AND (dealer_vehicles."out_invoice_number" IS NULL)
                )
         -- order by c166 asc
     ) D1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

GO
