FLOW Analytics | Datatim Base Query 6 | Vehicle Masterfile Table

FLOW Analytics | Datatim Base Query 6 | Vehicle Masterfile Table

You may need to adjust the KM parameters based on how the client defines their parameters.

SELECT  VehicleNumber,
        Registration,
        TranspNo,
        TranspDescrip,
        VehDormantFlag,
        CASE WHEN VehicleCategory = 'H' THEN "Horse" WHEN VehicleCategory = 'L' THEN "Leader" WHEN VehicleCategory = 'F' THEN "Follower" WHEN VehicleCategory = 'R' THEN "Rigid"
        ELSE NULL END AS Vehicle_Category,
        SpeedoCurrent,
        LicenceDate,
        COFDate,
        PDPDate,
        DieselTargetConsumption,
        MinServiceInterval,
        MinServiceDue,
        ServiceDueFlag,
        ServiceDueInKms,
        KmBeforeService,
        Datediff(LicenceDate,NOW()) AS Licence_ExpiryDays,
        CASE WHEN Licence_ExpiryDays < 0 THEN "1. Expired" WHEN Licence_ExpiryDays
    BETWEEN 0 AND 90 THEN "2. Expiry Upcoming"
        ELSE "3. Valid" END AS License_ExpiryFlag,
        CASE WHEN LicenceDate < date(now()) THEN LicenceDate
        ELSE ''end AS Licence_Expired,
        CASE WHEN LicenceDate < date_add(date(now()),interval 7 day) AND LicenceDate >= date(now()) THEN LicenceDate
        ELSE '' END AS Licence_7_Days,
        CASE WHEN LicenceDate < date_add(date(now()),interval 15 day) AND LicenceDate >= date_add(date(now()),interval 7 day) THEN LicenceDate
        ELSE '' END AS Licence_7_To_15_Days,
        CASE WHEN LicenceDate < date_add(date(now()),interval 30 day) AND LicenceDate >= date_add(date(now()),interval 15 day) THEN LicenceDate
        ELSE '' END AS Licence_15_To_30_Days,
        CASE WHEN LicenceDate < date_add(date(now()),interval 60 day) AND LicenceDate >= date_add(date(now()),interval 30 day) THEN LicenceDate
        ELSE '' END AS Licence_30_To_60_Days,
        CASE WHEN LicenceDate < date_add(date(now()),interval 90 day) AND LicenceDate >= date_add(date(now()),interval 60 day) THEN LicenceDate
        ELSE '' END AS Licence_60_To_90_Days,
        CASE WHEN LicenceDate >= date_add(date(now()),interval 90 day) THEN LicenceDate
        ELSE '' END AS Licence_90_Plus_Days,
        CASE WHEN LicenceDate < date(now()) THEN 1
        ELSE 0 END AS Licence_Expired_Count,
        CASE WHEN LicenceDate < date_add(date(now()),interval 7 day) AND LicenceDate >= date(now()) THEN 1
        ELSE 0 END AS Licence_7_Days_Count,
        CASE WHEN LicenceDate < date_add(date(now()),interval 15 day) AND LicenceDate >= date_add(date(now()),interval 7 day) THEN 1
        ELSE 0 END AS Licence_7_To_15_Days_Count,
        CASE WHEN LicenceDate < date_add(date(now()),interval 30 day) AND LicenceDate >= date_add(date(now()),interval 15 day) THEN 1
        ELSE 0 END AS Licence_15_To_30_Days_Count,
        CASE WHEN LicenceDate < date_add(date(now()),interval 60 day) AND LicenceDate >= date_add(date(now()),interval 30 day) THEN 1
        ELSE 0 END AS Licence_30_To_60_Days_Count,
        CASE WHEN LicenceDate < date_add(date(now()),interval 90 day) AND LicenceDate >= date_add(date(now()),interval 60 day) THEN 1
        ELSE 0 END AS Licence_60_To_90_Days_Count,
        CASE WHEN LicenceDate >= date_add(date(now()),interval 90 day) THEN 1
        ELSE 0 END AS Licence_90_Plus_Days_Count,
        Datediff(COFDate,NOW()) AS COF_ExpiryDays,
        CASE WHEN COF_ExpiryDays < 0 THEN "1. Expired" WHEN COF_ExpiryDays
    BETWEEN 0 AND 90 THEN "2. Expiry Upcoming"
        ELSE "3. Valid" END AS COF_ExpiryFlag,
        CASE WHEN COFDate < date(now()) THEN COFDate
        ELSE ''end AS COF_Expired,
        CASE WHEN COFDate < date_add(date(now()),interval 7 day) AND COFDate >= date(now()) THEN COFDate
        ELSE '' END AS COF_7_Days,
        CASE WHEN COFDate < date_add(date(now()),interval 15 day) AND COFDate >= date_add(date(now()),interval 7 day) THEN COFDate
        ELSE '' END AS COF_7_To_15_Days,
        CASE WHEN COFDate < date_add(date(now()),interval 30 day) AND COFDate >= date_add(date(now()),interval 15 day) THEN COFDate
        ELSE '' END AS COF_15_To_30_Days,
        CASE WHEN COFDate < date_add(date(now()),interval 60 day) AND COFDate >= date_add(date(now()),interval 30 day) THEN COFDate
        ELSE '' END AS COF_30_To_60_Days,
        CASE WHEN COFDate < date_add(date(now()),interval 90 day) AND COFDate >= date_add(date(now()),interval 60 day) THEN COFDate
        ELSE '' END AS COF_60_To_90_Days,
        CASE WHEN COFDate >= date_add(date(now()),interval 90 day) THEN COFDate
        ELSE '' END AS COF_90_Plus_Days,
        CASE WHEN COFDate < date(now()) THEN 1
        ELSE 0 END AS COF_Expired_Count,
        CASE WHEN COFDate < date_add(date(now()),interval 7 day) AND COFDate >= date(now()) THEN 1
        ELSE 0 END AS COF_7_Days_Count,
        CASE WHEN COFDate < date_add(date(now()),interval 15 day) AND COFDate >= date_add(date(now()),interval 7 day) THEN 1
        ELSE 0 END AS COF_7_To_15_Days_Count,
        CASE WHEN COFDate < date_add(date(now()),interval 30 day) AND COFDate >= date_add(date(now()),interval 15 day) THEN 1
        ELSE 0 END AS COF_15_To_30_Days_Count,
        CASE WHEN COFDate < date_add(date(now()),interval 60 day) AND COFDate >= date_add(date(now()),interval 30 day) THEN 1
        ELSE 0 END AS COF_30_To_60_Days_Count,
        CASE WHEN COFDate < date_add(date(now()),interval 90 day) AND COFDate >= date_add(date(now()),interval 60 day) THEN 1
        ELSE 0 END AS COF_60_To_90_Days_Count,
        CASE WHEN COFDate >= date_add(date(now()),interval 90 day) THEN 1
        ELSE 0 END AS COF_90_Plus_Days_Count,
        CASE WHEN MinServiceDue - SpeedoCurrent <= 0 THEN MinServiceDue - SpeedoCurrent END AS 'Overdue',
        CASE WHEN MinServiceDue - SpeedoCurrent > 0 AND MinServiceDue - SpeedoCurrent <= 500 THEN MinServiceDue - SpeedoCurrent END AS '0-500km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 500 AND MinServiceDue - SpeedoCurrent <= 1000 THEN MinServiceDue - SpeedoCurrent END AS '501-1000km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 1000 AND MinServiceDue - SpeedoCurrent <= 1500 THEN MinServiceDue - SpeedoCurrent END AS '1001-1500km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 1500 AND MinServiceDue - SpeedoCurrent <= 2000 THEN MinServiceDue - SpeedoCurrent END AS '1501-2000km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 2000 AND MinServiceDue - SpeedoCurrent <= 2500 THEN MinServiceDue - SpeedoCurrent END AS '2001-2500km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 2500 AND MinServiceDue - SpeedoCurrent <= 3000 THEN MinServiceDue - SpeedoCurrent END AS '2501-3000km',
        CASE WHEN MinServiceDue - SpeedoCurrent > 3000 THEN MinServiceDue - SpeedoCurrent END AS '3000 Plus km',
        CASE WHEN ServiceDueInKms
    BETWEEN 0 AND 3000 THEN "2. Upcoming Service" WHEN MinServiceDue <= SpeedoCurrent THEN "1. Due for Service"
        ELSE "3. No Service Needed" END AS ServiceStatus,
        t.TrCrName AS TransporterName
FROM vwa_vehiclemaster v
LEFT JOIN vwa_transito t ON v.TranspNo = t.TrCrNumber
WHERE VehDormantFlag = 'N' 

    • Related Articles

    • Datatim Back-End Tables

      The attached file shows each table and fields in the most used and standard Datatim tables, currently in use on FLOW Analytics. These include: 1. tblloadschedule - Load Schedule 2. loadconfirmation - Collection and Delivery addresses 3. KPI - KPI ...

    • FLOW Analytics | Datatim Base Query 7 | Vehicle Expense (VTRN) Table

      This is showing you ALL expense data. SELECT * FROM vwa_vtrn If you want Diesel Consumption, build a separate Data Model and have a look at the next article titled: FLOW Analytics | Datatim Base Query 8 | Diesel Consumption (VTRN) Table

    • FLOW Analytics | Datatim Base Query 3 | Driver Masterfile Table

      The Driver Masterfile has a lot of unneccessary fields, but the Expiries dashboards are very popular, hence the detailed query below. SELECT DriverNumber, DriverName, TranspNo, TranspDescrip, DriverDormantFlag, PDPDate , LicenseDate , ...

    • FLOW Analytics | Datatim Base Query 2 | Creditor Masterfile Table

      The only thing that is important, but does not stand out, is the Dedicated tick box on the Creditor Masterfile. In the backend, it's called CrInterestFlag, therefore, we need to format it like so: SELECT *, CASE WHEN CrInterestFlag = 'Y' THEN ...

    • FLOW Analytics | Datatim Base Query 1 | Load Schedule Table

      This should cover the base Datatim systems and run successfully. The only challenge will be intricately customized systems. This is additional scoping. Go to Data Model > Add New and paste this Base Query, and then click Run. Base Query: SELECT l.*, ...