FLOW Analytics | Datatim Base Query 3 | Driver Masterfile 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 ,
        MedicalExaminationDate,
        Datediff(PDPDate,NOW()) AS PDP_ExpiryDays,
        CASE WHEN PDP_ExpiryDays < 0 THEN "1. Expired" WHEN PDP_ExpiryDays
    BETWEEN 0 AND 90 THEN "2. Expiry Upcoming"
        ELSE "3. Valid" END AS PDP_ExpiryFlag,
        CASE WHEN PDPDate < date(now()) THEN PDPDate
        ELSE ''end AS PDP_Expired,
        CASE WHEN PDPDate < date_add(date(now()),interval 7 day) AND PDPDate >= date(now()) THEN PDPDate
        ELSE '' END AS PDP_7_Days,
        CASE WHEN PDPDate < date_add(date(now()),interval 15 day) AND PDPDate >= date_add(date(now()),interval 7 day) THEN PDPDate
        ELSE '' END AS PDP_7_To_15_Days,
        CASE WHEN PDPDate < date_add(date(now()),interval 30 day) AND PDPDate >= date_add(date(now()),interval 15 day) THEN PDPDate
        ELSE '' END AS PDP_15_To_30_Days,
        CASE WHEN PDPDate < date_add(date(now()),interval 60 day) AND PDPDate >= date_add(date(now()),interval 30 day) THEN PDPDate
        ELSE '' END AS PDP_30_To_60_Days,
        CASE WHEN PDPDate < date_add(date(now()),interval 90 day) AND PDPDate >= date_add(date(now()),interval 60 day) THEN PDPDate
        ELSE '' END AS PDP_60_To_90_Days,
        CASE WHEN PDPDate >= date_add(date(now()),interval 90 day) THEN PDPDate
        ELSE '' END AS PDP_90_Plus_Days,
        CASE WHEN PDPDate < date(now()) THEN 1
        ELSE 0 END AS PDP_Expired_Count,
        CASE WHEN PDPDate < date_add(date(now()),interval 7 day) AND PDPDate >= date(now()) THEN 1
        ELSE 0 END AS PDP_7_Days_Count,
        CASE WHEN PDPDate < date_add(date(now()),interval 15 day) AND PDPDate >= date_add(date(now()),interval 7 day) THEN 1
        ELSE 0 END AS PDP_7_To_15_Days_Count,
        CASE WHEN PDPDate < date_add(date(now()),interval 30 day) AND PDPDate >= date_add(date(now()),interval 15 day) THEN 1
        ELSE 0 END AS PDP_15_To_30_Days_Count,
        CASE WHEN PDPDate < date_add(date(now()),interval 60 day) AND PDPDate >= date_add(date(now()),interval 30 day) THEN 1
        ELSE 0 END AS PDP_30_To_60_Days_Count,
        CASE WHEN PDPDate < date_add(date(now()),interval 90 day) AND PDPDate >= date_add(date(now()),interval 60 day) THEN 1
        ELSE 0 END AS PDP_60_To_90_Days_Count,
        CASE WHEN PDPDate >= date_add(date(now()),interval 90 day) THEN 1
        ELSE 0 END AS PDP_90_Plus_Days_Count,
        Datediff(LicenseDate,NOW()) AS License_ExpiryDays,
        CASE WHEN License_ExpiryDays < 0 THEN "1. Expired" WHEN License_ExpiryDays
    BETWEEN 0 AND 90 THEN "2. Expiry Upcoming"
        ELSE "3. Valid" END AS License_ExpiryFlag,
        CASE WHEN LicenseDate < date(now()) THEN LicenseDate
        ELSE ''end AS License_Expired,
        CASE WHEN LicenseDate < date_add(date(now()),interval 7 day) AND LicenseDate >= date(now()) THEN LicenseDate
        ELSE '' END AS License_7_Days,
        CASE WHEN LicenseDate < date_add(date(now()),interval 15 day) AND LicenseDate >= date_add(date(now()),interval 7 day) THEN LicenseDate
        ELSE '' END AS License_7_To_15_Days,
        CASE WHEN LicenseDate < date_add(date(now()),interval 30 day) AND LicenseDate >= date_add(date(now()),interval 15 day) THEN LicenseDate
        ELSE '' END AS License_15_To_30_Days,
        CASE WHEN LicenseDate < date_add(date(now()),interval 60 day) AND LicenseDate >= date_add(date(now()),interval 30 day) THEN LicenseDate
        ELSE '' END AS License_30_To_60_Days,
        CASE WHEN LicenseDate < date_add(date(now()),interval 90 day) AND LicenseDate >= date_add(date(now()),interval 60 day) THEN LicenseDate
        ELSE '' END AS License_60_To_90_Days,
        CASE WHEN LicenseDate >= date_add(date(now()),interval 90 day) THEN LicenseDate
        ELSE '' END AS License_90_Plus_Days,
        CASE WHEN LicenseDate < date(now()) THEN 1
        ELSE 0 END AS License_Expired_Count,
        CASE WHEN LicenseDate < date_add(date(now()),interval 7 day) AND LicenseDate >= date(now()) THEN 1
        ELSE 0 END AS License_7_Days_Count,
        CASE WHEN LicenseDate < date_add(date(now()),interval 15 day) AND LicenseDate >= date_add(date(now()),interval 7 day) THEN 1
        ELSE 0 END AS License_7_To_15_Days_Count,
        CASE WHEN LicenseDate < date_add(date(now()),interval 30 day) AND LicenseDate >= date_add(date(now()),interval 15 day) THEN 1
        ELSE 0 END AS License_15_To_30_Days_Count,
        CASE WHEN LicenseDate < date_add(date(now()),interval 60 day) AND LicenseDate >= date_add(date(now()),interval 30 day) THEN 1
        ELSE 0 END AS License_30_To_60_Days_Count,
        CASE WHEN LicenseDate < date_add(date(now()),interval 90 day) AND LicenseDate >= date_add(date(now()),interval 60 day) THEN 1
        ELSE 0 END AS License_60_To_90_Days_Count,
        CASE WHEN LicenseDate >= date_add(date(now()),interval 90 day) THEN 1
        ELSE 0 END AS License_90_Plus_Days_Count,
        Datediff(MedicalExaminationDate,NOW()) AS MedicalExamination_ExpiryDays,
        CASE WHEN MedicalExamination_ExpiryDays < 0
        OR NULL
        OR '' THEN "1. Expired" WHEN MedicalExamination_ExpiryDays
    BETWEEN 0 AND 90 THEN "2. Expiry Upcoming"
        ELSE "3. Valid" END AS MedicalExamination_ExpiryFlag,
        CASE WHEN MedicalExaminationDate < date(now()) THEN MedicalExaminationDate
        ELSE ''end AS Medical_Expired,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 7 day) AND MedicalExaminationDate >= date(now()) THEN MedicalExaminationDate
        ELSE '' END AS Medical_7_Days,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 15 day) AND MedicalExaminationDate >= date_add(date(now()),interval 7 day) THEN MedicalExaminationDate
        ELSE '' END AS Medical_7_To_15_Days,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 30 day) AND MedicalExaminationDate >= date_add(date(now()),interval 15 day) THEN MedicalExaminationDate
        ELSE '' END AS Medical_15_To_30_Days,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 60 day) AND MedicalExaminationDate >= date_add(date(now()),interval 30 day) THEN MedicalExaminationDate
        ELSE '' END AS Medical_30_To_60_Days,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 90 day) AND MedicalExaminationDate >= date_add(date(now()),interval 60 day) THEN MedicalExaminationDate
        ELSE '' END AS Medical_60_To_90_Days,
        CASE WHEN MedicalExaminationDate >= date_add(date(now()),interval 90 day) THEN MedicalExaminationDate
        ELSE '' END AS Medical_90_Plus_Days,
        CASE WHEN MedicalExaminationDate < date(now()) THEN 1
        ELSE 0 END AS MedicalExamination_Expired_Count,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 7 day) AND MedicalExaminationDate >= date(now()) THEN 1
        ELSE 0 END AS Medical_7_Days_Count,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 15 day) AND MedicalExaminationDate >= date_add(date(now()),interval 7 day) THEN 1
        ELSE 0 END AS Medical_7_To_15_Days_Count,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 30 day) AND MedicalExaminationDate >= date_add(date(now()),interval 15 day) THEN 1
        ELSE 0 END AS Medical_15_To_30_Days_Count,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 60 day) AND MedicalExaminationDate >= date_add(date(now()),interval 30 day) THEN 1
        ELSE 0 END AS Medical_30_To_60_Days_Count,
        CASE WHEN MedicalExaminationDate < date_add(date(now()),interval 90 day) AND MedicalExaminationDate >= date_add(date(now()),interval 60 day) THEN 1
        ELSE 0 END AS Medical_60_To_90_Days_Count,
        CASE WHEN MedicalExaminationDate >= date_add(date(now()),interval 90 day) THEN 1
        ELSE 0 END AS Medical_90_Plus_Days_Count
FROM vwa_drivermaster

    • 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 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.*, ...

    • 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 ...