Client: Carlos Galarce
Industry: Automotive Data / CRM Analytics
Platform: Microsoft SQL Server (SSMS)
- Project Title:
Automated SQL Query for Identifying and Filling Missing Monthly Entries in Vehicle Maintenance Records
- 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.
- 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:
- a) Analyzing gaps between sequential monthly dates per VIN
- b) Generating missing entries with consistent data (or NULL/defaults for non-date columns)
- c) Avoiding duplication where a month already exists
- d) Outputting a new dataset with seamlessly filled-in records
- Tools & Technologies Used:
- a) Microsoft SQL Server Management Studio (SSMS)
- b) Transact-SQL (T-SQL)
- c) Excel (Data Validation)
- d) PowerPoint (Client-Supplied Visual Specs)
- e) Zoom (Client Collaboration)
- 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 |
- 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 |
- Outcome & Benefits:
- a) A reusable SQL script that reliably fills in missing month gaps per VIN
- b) The dataset was transformed to support complete monthly analysis, enabling improved CRM insights
- c) Delivered robust results after 8–9 hours of focused development and testing
- d) Helped reduce manual intervention and improved data consistency