Calculating Service Duration and Transforming Data Using Bold Data Hub

In this article, we will demonstrate how to import tables from a CSV file, calculate service duration through transformations, and migrate the cleaned data into the destination database using Bold Data Hub. Follow the step-by-step process below.

Sample Data Source:

Sample CSC Data

Creating Pipeline

Learn about Pipeline Creation

Applying Transformation

Learn more about transformation here

Calculating Service Duration

Overview

Understanding the time taken to resolve tickets helps assess efficiency and identify potential bottlenecks in the support process. This query calculates the service duration by measuring the difference between ticket creation and resolution dates.

Approach

We use the DATEDIFF function to compute the number of days between ticket creation and resolution. Only records with valid resolution dates are considered, and negative durations are excluded.

SQL Query for Calculating Service Duration

SELECT 
    Ticket_ID, 
    Ticket_Creation_Date, 
    Ticket_Resolution_Date, 
    DATEDIFF('day', CAST(Ticket_Creation_Date AS DATE), CAST(Ticket_Resolution_Date AS DATE)) AS Service_Duration_Days 
FROM {pipeline_name}.sample_csc_data 
WHERE 
    Ticket_Resolution_Date IS NOT NULL 
    AND Ticket_Creation_Date IS NOT NULL 
    AND DATEDIFF('day', CAST(Ticket_Creation_Date AS DATE), CAST(Ticket_Resolution_Date AS DATE)) > 0;