Identify time gaps in sql table
Budget: $30 – $250 USD
I have a mssql table of time entries from which I'd like to generate a second table which lists the gaps in time - when there is no time entry in the original table. Note that there could be multiple overlappting time entries.
i'll provide a sample source table.
The fields of importance in the table are:
Member_ID - this is the user - there will be multiple users, gaps should be computed per user
Date_Start - date of time entry
Time_Start - start time of time entry
Time_End - end time of time entry
The input parameters should be
date start - date to start analysis
date end - date to end analysis
time start - time to start gap analysis (only interested in gaps during business hours)
time end - time to end gap analysis (for example these will be 9 AM - 5:30 PM)
The program should erase any records within the date range in the output table before proceeding.
The output table should have the following fields
Member_ID - user
Gap_Date - date
Gap_Time_Start - start time of time gap
Gap_Time_End - end time of time gap
Gap_Length - time gap in minutes
I'd prefer this written in Powershell or vbscript but open to visual basic - as long as I can edit and recompile the code.
i'll provide a sample source table.
The fields of importance in the table are:
Member_ID - this is the user - there will be multiple users, gaps should be computed per user
Date_Start - date of time entry
Time_Start - start time of time entry
Time_End - end time of time entry
The input parameters should be
date start - date to start analysis
date end - date to end analysis
time start - time to start gap analysis (only interested in gaps during business hours)
time end - time to end gap analysis (for example these will be 9 AM - 5:30 PM)
The program should erase any records within the date range in the output table before proceeding.
The output table should have the following fields
Member_ID - user
Gap_Date - date
Gap_Time_Start - start time of time gap
Gap_Time_End - end time of time gap
Gap_Length - time gap in minutes
I'd prefer this written in Powershell or vbscript but open to visual basic - as long as I can edit and recompile the code.