Welcome to Database School

Dynamic SQL Query Development for Missing Monthly Entries in VIN Data

Client: Carlos Galarce

Industry: Automotive Data / CRM Analytics

Platform: Microsoft SQL Server (SSMS)

 
  1. Project Title:
Automated SQL Query for Identifying and Filling Missing Monthly Entries in Vehicle Maintenance Records

  1. Objective:
To design a SQL-based data processing solution that scans time-series vehicle maintenance data and dynamically generates “dummy” monthly records for any missing months, ensuring continuity in reporting and CRM analytics.

  1. Project Description:
Carlos needed a SQL script to detect and fill missing months between service records (RODate) for each unique VIN. The records were stored in a .csv dataset and had large-scale implications across thousands of entries. The requirement involved:
  1. a) Analyzing gaps between sequential monthly dates per VIN
  2. b) Generating missing entries with consistent data (or NULL/defaults for non-date columns)
  3. c) Avoiding duplication where a month already exists
  4. d) Outputting a new dataset with seamlessly filled-in records
 
  1. Tools & Technologies Used:
  2. a) Microsoft SQL Server Management Studio (SSMS)
  3. b) Transact-SQL (T-SQL)
  4. c) Excel (Data Validation)
  5. d) PowerPoint (Client-Supplied Visual Specs)
  6. e) Zoom (Client Collaboration)
 
  1. Key Features Implemented:
Task Description
Gap Analysis Parsed dates per VIN, calculated gaps in months
Dynamic Record Insertion Added synthetic records for missing months using loop-based T-SQL logic
Validation Tested logic using sample VINs and full dataset (3MB+)
Client Feedback Loop Incorporated real-time corrections (e.g., avoiding duplicates)
Scalability Script optimized to support bulk VIN uploads with consistent performance
 
  1. Challenges & Resolutions:
Challenge Resolution
Partial data initially provided (only 1 VIN case) Script was designed to generalize logic to scale across thousands of VINs
Incorrect insertion of duplicate months Implemented stricter month-year check before generating new records
Ambiguity in dummy record fields Confirmed client preference to retain same or NULL values for other columns
No direct access to production DB Simulated data in local SQL environment and validated with client feedback


  1. Outcome & Benefits:
  2. a) A reusable SQL script that reliably fills in missing month gaps per VIN
  3. b) The dataset was transformed to support complete monthly analysis, enabling improved CRM insights
  4. c) Delivered robust results after 8–9 hours of focused development and testing
  5. d) Helped reduce manual intervention and improved data consistency