SQL Procedure for Shift Punching -- 2
Budget: ₹1,500 – ₹12,500 INR
I need an SQL stored procedure that converts our raw machine punches into clear IN and OUT records for every employee, taking our floating-shift logic into account. The punches arrive already tagged with numeric shift codes, and each employee’s master record – which lists their ID, name, department, and shift preferences – tells us whether they are tied to D1, N1 or can move between the two.
Here is what the routine must do:
• Read the unprocessed punch table straight from the clocking machine.
• Match each punch to the correct employee and decide whether it begins or ends a D1 or an N1 shift, even when the shift crosses midnight.
• Build (or update) an attendance table that stores one clear IN and one clear OUT per person, per shift, per day.
• Flag exceptions (missing OUT, double IN, unexpected shift, etc.) in a separate column so our HR team can review them quickly.
I will share the current table definitions and several days of sample data as soon as we start. Please keep your script fully commented, include any needed indexing or performance tweaks, and provide a simple test query that shows the expected output. I’m working on SQL Server, so T-SQL is preferred, but if you have a different suggestion that stays in the Microsoft stack let me know.
Once the procedure runs cleanly on the sample set, I’ll validate it against a larger slice of production data before signing off.
Here is what the routine must do:
• Read the unprocessed punch table straight from the clocking machine.
• Match each punch to the correct employee and decide whether it begins or ends a D1 or an N1 shift, even when the shift crosses midnight.
• Build (or update) an attendance table that stores one clear IN and one clear OUT per person, per shift, per day.
• Flag exceptions (missing OUT, double IN, unexpected shift, etc.) in a separate column so our HR team can review them quickly.
I will share the current table definitions and several days of sample data as soon as we start. Please keep your script fully commented, include any needed indexing or performance tweaks, and provide a simple test query that shows the expected output. I’m working on SQL Server, so T-SQL is preferred, but if you have a different suggestion that stays in the Microsoft stack let me know.
Once the procedure runs cleanly on the sample set, I’ll validate it against a larger slice of production data before signing off.