Outputs:

SQL: [IDI_Community].[drv_dlr_endorsement].[dlr_endorsement_202603]
SAS: libname cm_dlr_endorsement dsn=IDI_community_srvprd schema=drv_dlr_endorsement; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Context

This module creates a table of driver licence endorsement spells by the type and status of endorsement. It is sourced from Waka Kotahi NZ Transport Agency’s wider register of driver licences, which also includes information specific to endorsements.

Business Concepts

Endorsements are required to operate certain types of vehicles and services. There are nine types of driver license endorsements each of which is also known by a one letter code - passenger (P), vehicle recovery (V), driving instructor (I), testing officer (O), dangerous goods (D), forklift (F), roller (R), tracks (T) and wheels (W).

Endorsements are usually gained by completing an appropriate course. Certain endorsements - for example, driving instructor (I), driver testing officer (O), commercial passenger (P) and vehicle recovery (V) - also require a police background check.

Endorsements may also require a medical certificate (unless already provided to Waka Kotahi in the last five years).

Licence endorsements

The ten types of endorsement are detailed below.

1. Passenger (P) to drive paying passengers or a large passenger service vehicle (e.g., taxi or bus), you need a P endorsement.

2. Vehicle Recovery (V) to operate a vehicle recovery service - for example, tow truck and car transporter drivers - you need a V endorsement.

3. Driver Instructor (I) to teach people to drive for business or payment, you need an I endorsement.

4. Testing officer (O) to conduct practical driving tests, you need an O endorsement, and must be employed by - or contracted to - Waka Kotahi NZ Transport Agency, its testing agent VTNZ, or the NZ Defence Force.

5. Forklift (F) to drive a forklift on the road, you need an F endorsement and a weight-appropriate licence class. An F endorsement for a class 1 licence, covers forklifts with a weight of up to 18,000kg when loaded; and for a class 2 licence, covers forklifts over 18,000kgs when loaded.

6. Dangerous Goods (D) to drive a vehicle or vehicle combination to transport dangerous goods, you need a D endorsement.

7. Special-type vehicles (R T W) to drive a special-type vehicle on the road, you need an appropriate endorsement.

8. Roller (R) to drive vehicles on rollers.

9. Tracks (T) to drive vehicles on self-laying tracks.

10. Wheels (W) to drive special-type vehicles on wheels as used in mining or roadworks (excluding forklifts, tractors, trade vehicles etc.).

Endorsement Status:

Each endorsement type has a status. For example, a specific endorsement type may have a status of expired. If a licence holder is disqualified, then all their endorsements will also have a status of requalify at the end of the disqualification period.

Current status. Endorsement holders are eligible to operate certain vehicle types and services provided they have a current licence status.

Non-current statuses. For the following statuses, the endorsement type is not current.

• Disqualified Licence and endorsement holders are disqualified, or partially disqualified, by the courts. Partial disqualification applies to specific classes or endorsements.
• Expired The endorsement needs to be renewed before a person is eligible to operate the endorsement.
• Reinstated The endorsement needs to be reinstated following a sanction of one year or less, or the completion of a zero-alcohol licence.
• Requalify The endorsement needs to be requalified following a sanction of greater than 1 year, or an expiry of more than 5 years.
• Revoked The endorsement is revoked usually on medical grounds.
• Suspended The endorsement is suspended due to excess demerit points, 28-day roadside suspension or medical suspension.
• Voluntary Surrender The endorsement is voluntarily surrendered by notifying - or delivering the driver licence to - Waka Kotahi NZ Transport Agency.
• Prohibit P Endorsement. People convicted of specified serious offences are prohibited from holding a passenger (P) endorsement.

Cancelled Status. The endorsement type has been cancelled.

Waka Kotahi Open Data Comparison

To verify the accuracy of IDI sourced endorsement spell data, we compared the number of endorsement holders (via the IDI) to numbers released by NZ Transport Agency Waka Kotahi.

End of year comparisons (from 2008 to 2023) between these two sources showed a match of over 98%.

Business history

References

1.Getting an endorsement Waka Kotahi NZ Transport Agency 2024.
2.Driving licencing fees Waka Kotahi NZ Transport Agency 2024.

Community of Interest

Key Business Rules

We calculate the end date of an endorsement status as one day before the start of the next status.

Parameters

The following parameters should be supplied to this module to run it in the database:

1. {targetdb}: The SQL database on which the spell datasets are to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets that have the same name.

Variable Descriptions

The business key for this spell table is one row per snz_uid, endorsement type, endorsement status, and spell start.

Module Version & Change History

DROP VIEW IF EXISTS [(targ etschema)].[(proj prefix)_driver _license_e ndorsemen ts];
GO
/* This creates a table all demographic information */
CREA TE VI EW [(ta r ge ts chema)].[(pr ojp refix)_driver lic ense endorsements] AS

Code

 :setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"

/* Clear view */

DROP TABLE IF EXISTS #new_temp;
WITH nz_licences AS(
    SELECT [snz_uid]
        , [snz_nzta_uid]
        , [nzta_dlr_endorsement_type_text]
        , [nzta_dlr_endorsement_start_date]
        , [nzta_dlr_endorsement_expiry_date]
        , [nzta_dlr_endorsement_status_text]
        , [nzta_dlr_endorsement_from_date]
        , CASE 
			WHEN nzta_dlr_endorsement_status_text = 'CURRENT' THEN 1 ELSE 0 END AS [current_indicator]
        , [nzta_dlr_endorsement_from_date] AS spell_start
        , [nzta_dlr_endorsement_expiry_date] AS expiry_date
    FROM [$(idicleanversion)].[nzta_clean].[drivers_licence_register]
    WHERE nzta_dlr_endorsement_type_text IS NOT NULL
    AND nzta_dlr_endorsement_start_date IS NOT NULL
)
    , table1 AS(
    SELECT *
        , ROW_NUMBER() OVER (
			PARTITION BY nzta_dlr_endorsement_type_text, snz_uid, spell_start
			ORDER BY nzta_dlr_endorsement_type_text ASC, snz_uid ASC, spell_start DESC
		) AS seqnum
    FROM nz_licences
)
    , licences_dsc AS(
    SELECT *
    FROM table1
    WHERE seqnum = 1
)
    , licences_withlag_dsc1 AS(
    SELECT *
        , LAG(■■■■■■■■■■■■ OVER(
			ORDER BY nzta_dlr_endorsement_type_text ASC, snz_uid ASC, spell_start DESC
		  ) AS next_snz_uid
        , LAG(DATEADD(DAY, -1, spell_start),1) OVER(
			ORDER BY nzta_dlr_endorsement_type_text ASC, snz_uid ASC, spell_start DESC
		 ) AS spell_end1
    FROM licences_dsc
)
    , licences_withlag_dsc AS(
    SELECT *
        , CASE
			WHEN [snz_uid] != [next_snz_uid] THEN '9999-12-01'
			WHEN [snz_uid] = [next_snz_uid] THEN spell_end1 END AS spell_end
    FROM licences_withlag_dsc1
)
SELECT [snz_uid]
    , [snz_nzta_uid]
    , [spell_start]
    , [spell_end]
    , [nzta_dlr_endorsement_type_text]
    , [nzta_dlr_endorsement_status_text]
INTO #new_temp
FROM licences_withlag_dsc;

SELECT * 

INTO [$(targetdb)].[$(targetschema)].[$(projprefix)_driver_license_endorsements]

FROM #new_temp
 ;
GO