Consulting

Results 1 to 11 of 11

Thread: Solved: Table Design / SQL Design Advice

  1. #1

    Solved: Table Design / SQL Design Advice

    I'm trying to utilize DB's more in my little projects. I've often read that it's best to not store a value that can be calculated from the other data being stored. Taking that for gospel, I've designed a simple table that houses Time sheet data

    EmpID, dtWorked, TimeIn1, TimeOut1, TimeIn2, TimeOut2, etc. there is an actual need for up to 6 Time In/Out pairs in this case. I made EmpID and dtWork dual Primary Keys, so there's only one record for an employee on any given day.

    I was building a query with the intention of returning from this table the total time worked for a given employee between two dates. Using the Access Query Designer, I got to this point with the SQL looking at just the first two Time In/Out pairs...it would get much uglier for all 6 pair.

    [VBA]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
    SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, (DateDiff("n",[TimeIn1],[TimeOut1]))+IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])),IIf(nz([TimeOut2])>0,DateDiff("n",nz([TimeIn2]),nz([TimeOut2])),DateDiff("n",nz([TimeIn2]),DateAdd("d",1,nz([TimeOut2])))),0) AS TotalMinutes, Format(TimeSerial([TotalMinutes]\60,[TotalMinutes] Mod 60,0),"Short Time") AS TotalHours
    FROM tblDailyTimeSheets AS T
    WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null))
    ORDER BY T.dtWork;
    [/VBA]

    It works, returning the cumulative time worked for each day , but I'm guessing there must be a better more efficient way to do not just that, but to then Group it for the aggregate sum?

    It would be easy enough for me to add a column to the table to store the "Total Hours" when a record is added to the table, which would make this much simpler to do with an Aggregate query I'm guessing. I didn't get there without such a column so far.

    Any perspectives on how others would approach this query and/or table layout would be most welcome.

    Thanks,

    Shred

  2. #2

    Taking it to VBA ADO

    I"ve moved the SQL from the Access Query to a VBA function that returns the resulting recordset to a worksheet for testing. This works as a SQL call using adCmdText, but not as when trying to use the command object against a storedprocdure. (Is that not for Access, but rather SQL Server?)

    I'd like to just pull the resulting aggregate value of total minutes from the tbl design as described in earlier post. With that number I could do the math needed for my other purposes.

    Any Ideas?

    If not, I think I'll just create a template sheet and use excel formulas to calculate the result for me after dumping the recordset with just the time pairs. That would eliminate all the IIF's in the SQL. SQL would get much simpler.

    This is part of a project where Excel is the front end to an Access DB, so it's gotta go there at some point anyway. I just like to learn new ways of doing things more efficiently.

    Thanks for any insights,

    Shred

    [VBA]Public Function getTotalWorkedTime(lempID As Long, dtFrom As Date, dtTo As Date) As Boolean
    '---------------------------------------------------------------------------------------
    ' Procedure : getTotalWorkedTime
    ' Author : Shred Dude
    ' Date : August 16, 2011 - 19:47
    ' Purpose : Pull recordset for total time worked b/t two dates for a given empID
    '---------------------------------------------------------------------------------------
    '
    Const sPROCEDURE As String = "getTotalWorkedTime"

    Dim bResult As Boolean
    Dim sSQL As String

    Dim rsData As ADOdb.Recordset
    Dim cmAccess As ADOdb.Command
    Dim objParams As ADOdb.Parameters

    Dim lAffected As Long

    On Error GoTo errHandler

    'Modified SQL from qryEmployeeTimeBetweenTwoDates
    sSQL = "PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime; " & _
    "SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, " & _
    "(DateDiff(""n"", [TimeIn1], [TimeOut1])) + IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])), IIf([TimeOut2] > 0, DateDiff(""n"", [TimeIn2], [TimeOut2]), DateDiff(""n"", [TimeIn2], DateAdd(""d"", 1, [TimeOut2]))), 0) AS TotalMinutes, " & _
    "[TotalMinutes]/60 AS TotalHours " & _
    "FROM tblDailyTimeSheets AS T " & _
    "WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null)) " & _
    "ORDER BY T.dtWork;"

    'Confirm connection...
    If Not mcnConnect.State = adStateOpen Then mcnConnect.Open

    ' Create the Command object.
    Set cmAccess = New ADOdb.Command

    With cmAccess
    .ActiveConnection = mcnConnect
    .CommandText = sSQL
    .CommandType = adCmdText

    ' ''Not Working with Access Database...
    ' .CommandText = "qryEmployeeTimeBetweenDates"
    ' .CommandType = adCmdStoredProc

    ' Create and append the parameters.
    Set objParams = .Parameters

    objParams.Append .CreateParameter("EmpID", adInteger, adParamInput, 0, lempID)
    objParams.Append .CreateParameter("dtFrom", adDate, adParamInput, 10, dtFrom)
    objParams.Append .CreateParameter("dtTo", adDate, adParamInput, 10, dtTo)

    'Execute the Query
    Set rsData = .Execute(lAffected)

    End With

    'Dump data to a worksheet for testing...
    If gwkbUI Is Nothing Then HookUI

    With gwkbUI.Sheets(sSheetTabName(gwkbUI, "wksTest"))
    .Activate
    With .[ptrTitle]
    .CurrentRegion.ClearContents
    .CopyFromRecordset rsData
    .Offset(0, 2).Resize(.CurrentRegion.Rows.Count, 4).NumberFormat = "[$-409]h:mm AM/PM;@"
    .CurrentRegion.Columns.AutoFit
    End With
    End With


    errExit:
    getTotalWorkedTime = bResult

    'Release Objects

    DestroyADORecordset rsData
    Set objParams = Nothing
    Set cmAccess = Nothing

    'Restore Application Settings
    'XL_RestoreDefaults

    Exit Function

    errHandler:

    bResult = False

    If bCentralErrorHandler(msMODULE, sPROCEDURE, , False) Then
    'if in Debug Mode go here...
    Stop
    Resume
    Else
    Resume errExit
    End If

    End Function
    [/VBA]

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,229
    Location
    [
    Quote Originally Posted by Shred Dude
    I'm trying to utilize DB's more in my little projects. I've often read that it's best to not store a value that can be calculated from the other data being stored. Taking that for gospel, I've designed a simple table that houses Time sheet data
    That is right. The reason is that the calculation is a business rule, business rules can change, so you need flexibility.

    Quote Originally Posted by Shred Dude
    EmpID, dtWorked, TimeIn1, TimeOut1, TimeIn2, TimeOut2, etc. there is an actual need for up to 6 Time In/Out pairs in this case. I made EmpID and dtWork dual Primary Keys, so there's only one record for an employee on any given day.
    You should allow for any in/out pairs and use a table design like so

    Employee Id
    Date
    In_Out_Index
    Time In
    Time Out

    Quote Originally Posted by Shred Dude
    I was building a query with the intention of returning from this table the total time worked for a given employee between two dates. Using the Access Query Designer, I got to this point with the SQL looking at just the first two Time In/Out pairs...it would get much uglier for all 6 pair.

    [VBA]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
    SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, (DateDiff("n",[TimeIn1],[TimeOut1]))+IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])),IIf(nz([TimeOut2])>0,DateDiff("n",nz([TimeIn2]),nz([TimeOut2])),DateDiff("n",nz([TimeIn2]),DateAdd("d",1,nz([TimeOut2])))),0) AS TotalMinutes, Format(TimeSerial([TotalMinutes]\60,[TotalMinutes] Mod 60,0),"Short Time") AS TotalHours
    FROM tblDailyTimeSheets AS T
    WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null))
    ORDER BY T.dtWork;
    [/VBA]

    It works, returning the cumulative time worked for each day , but I'm guessing there must be a better more efficient way to do not just that, but to then Group it for the aggregate sum?

    It would be easy enough for me to add a column to the table to store the "Total Hours" when a record is added to the table, which would make this much simpler to do with an Aggregate query I'm guessing. I didn't get there without such a column so far.

    Any perspectives on how others would approach this query and/or table layout would be most welcome.
    Much easier with the table as I lay it out.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    XLD:

    Thank you for the insight, as always.

    I think my original table design was too Excel spreadsheet layout oriented. I'm trying....

    I'll give your suggestion a go.

    So once I have:

    EmpID, dtWork, iTimeInOut, dtTimeIn, dtTimeOut as fields...

    I would still need to generate an aggregate query to sum off of an expression field I'd include in the query that calculates total time per iTimeInOut record, to generate the total time per employee per day, right?


    For example, if my table has emp# 100 with two In/Out pairs yesterday as below:

    100, 8/16/2011, 1, 9:00 AM, 5:00 PM
    100, 8/16/2011, 2, 7:00PM, 10:00PM

    I'd want to return

    100, 8/16/2011, 11

    (9a to 5p = 8 hours) + (7p to 10p = 3 hours) = 11 hours

    After a couple of mis-steps I achieved this rather quickly with the following SQL:

    [vba]PARAMETERS lEmpID Long, dtWorkDate DateTime;
    SELECT tblTimePairs.empID, tblTimePairs.dtWork, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
    FROM tblTimePairs
    GROUP BY tblTimePairs.empID, tblTimePairs.dtWork
    HAVING (((tblTimePairs.empID)=[lEmpID]) AND ((tblTimePairs.dtWork)=[dtWorkDate]));[/vba]

    I can appreciate the value of designing the table layout as you've suggested. Your way, if things change in the future and I need eight pairs instead of six, no problem. Much easier to include than having to redesign a DB already deployed to include TimeIn_8 as a field for example.

    Thank you very much for taking the time to have a read and offer up yet another valuable insight for me. I truly appreciate it.

    Best Regards,

    Shred

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,229
    Location
    In a pure sense, some might argue that you should only have one time, and an indicator as to whether it is In or Out. This is where I feel some reality checking is required, you have to remember that you want to report on this data. The times will always be in pairs, and it makes the SQL a lot simpler.

    Good luck on the transition up to databases!
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6

    Too Quick

    I was a little too quick in thinking I had it. My Original goal was to aggreaget all time pairs between two dates. My earlier post was successful in doing that for a single date, but when I tried modifying it to include a From and To date parameter, I'm getting stuck on returning a record for each date. What I want is the total across the range of dates given.

    Here's where I'm at...any pointers?

    [vba]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
    SELECT TP.empID, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
    FROM tblTimePairs AS TP
    GROUP BY TP.dtWork, TP.empID
    HAVING (((TP.dtWork) Between [dtFrom] And [dtTo]) AND ((TP.empID)=[lEmpID]));
    [/vba]

  7. #7

    Seek and you shall find

    A little reading enlightened me on including both a WHERE and a HAVING clause in the SQL. That got me to where I wanted to be.

    [vba]PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime;
    SELECT TP.empID, Sum(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours
    FROM tblTimePairs AS TP
    WHERE (((TP.dtWork) Between [dtFrom] And [dtTo]))
    GROUP BY TP.empID
    HAVING (((TP.empID)=[lEmpID]));[/vba]

    Is this how you'd do it, with Access?

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,229
    Location
    I was going to ask you about that HAVING. Having is used to filter aggregate items, not row items, so doesn't seem necessary here. I think

    PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime; 
    SELECT TP.empID, SUM(DateDiff("n",[dtTimeIn],[dtTimeOut])/60) AS Hours 
    FROM tblTimePairs As TP 
    WHERE (TP.dtWork BETWEEN [dtFrom] AND [dtTo]) AND (TP.empID=[lEmpID])
    GROUP BY TP.empID ;
    should be sufficient.

    BTW, it is convention to use upper-case for keywords.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    That worked too.

    I had gotten to the HAVING clause by using the Query Designer in Access. When I clicked on the Sigma button (SUM) it converted by SELECT query to an Aggregate query and automatically put in the HAVING clause based on which columns had been checked with GroupBy.

    I see how the simpler version achieves the same result given the Expression as the Sum in it.

    Thanks again!

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by Shred Dude
    Any perspectives on how others would approach this query and/or table layout would be most welcome.
    Shred
    Perhaps a little late in this thread but: most timeclock systems use use a card swiper, or Server/Intranet login from a PC. Time is usually stored as GMT then converted for timezone in another field. You would have your employee table, a 'punch' table, and a lookup table for punch type, viz: (assumes in-times are 100 series, out-times are 200 series

    100=punch in for work
    200=punch out for lunch
    101=punch in from lunch
    201=punch out for emergency/sickness/appointment
    102=punch back in from [201]
    etc..

    It makes the SQL for calculating total time a little more complex but manageable. Just .02 Stan

  11. #11
    Stan:

    Thanks for the perspective.

    The project in front of me now however is much less sophisticated than one involving a time clock. Basically I"m facilitating the transcription of a paper based time sheet an employee fills out each week, and submits to a supervisor. The supervisor then needs to validate and aggregate to then submit up the chain for another person to "do payroll".

    I like your concept if they were being required to capture any sort of reason codes with their in/outs. I"ll tuck that away for another time. These employees will never touch a computer, time clock, etc.

    Thanks again for the input.

    Shred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •