Solved

Sql question

Posted on 2014-10-26
11
221 Views
Last Modified: 2014-10-29
Hi Experts,
I have an PatientsEmployeesSchedule table that consists of the following:
ID (PK)
EmployeeID
PatientID
Day (date of schedule)
From (time schedule started)
To (time schedule ended)

Now I have another table that consists of EmployeeID and days only (for what we are concerned now), and users would like to see next to each record of table2, if that employee worked at least 30 hours for that week (from Sun to Sat), what is the best way to accomplish that in performance matters?

FYI- front end is MS Access and back end is SQL.
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 7

Expert Comment

by:slubek
ID: 40405334
I don't have an access to MS SQL Server now, but (if I understand correctly what are you using table2 for) I'd try to start with something like:
select pes.employeeID, DATEPART(wk, pes.Day), sum(pes.To - pes.From)
  from PatientsEmployeesSchedule pes
    right join table2 t2 on t2.employeeID=pes.employeeID
  group by DATEPART(wk, pes.Day)
  having sum(pes.To - pes.From)>30

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40405350
Well, if by "employee worked", you mean the they were seeing patients, you could create a summation query that sums the "appointment times" for a particular employee week,

Something like:

SELECT Emp_ID, Format([ApptDay], "yy-ww"), Sum(DateDiff("n", [ApptTimeFrom], [ApptTimeTo]))/60 as ApptHours
FROM yourTable
GROUP BY Emp_ID, Format([ApptDay], "yy-ww")

This would give you the # of hours and partial hours that each employee had scheduled with patients for each week of the year.  You could refine it to a particular week with a WHERE clause.

BTW, Day, To, and From are probably poor field names, Day is a reserved word and a function used to identify the day of the month of a particular date: Day(#10/5/14#) = 0

To and From could easily be confused with email addresses, I would personally use ApptDay, ApptTimeFrom, ApptTimeTo.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40405428
Hi Experts,

@slubek,
When I tried running in SSMS (2008), I got the following msg :
"Msg 8117, Level 16, State 1, Line 5
Operand data type datetime is invalid for sum operator."

@Dale,
Yes worked means scheduled for patients.
The names are there for the longest time and I simply don't have the time to rewrite the app to fix those..
I was thinking from something like this you posted, however the first problem that came in my mind was, what will be at the beginning and at end of year, where the week gets cut of in half..?

One more thing guys, I think the best would be to create a function in sql that takes EmployeeID and Day as parameters, then returns true or false bases on total of hours >30, and next I need to assign that to the check box in access, something like control source = "MyFunction(EmployeeID, Day)"

Thanks,
Ben
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Expert Comment

by:slubek
ID: 40405499
As I said, I cannot check syntax right now, but maybe sum(DATEDIFF(hour, pes.From, pes.To)) will work instead. Or "minute" if you want to sum schedules shorter than hour.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40405523
instead of the week datepart, you might want to go with the first day of the week.  That way, it wouldn't matter whether the week overlapped years.  Besides, chances are you probably need some code in there to account for holidays as well.  You don't really expect 30 contact hours with patients on 4 day work weeks, do you?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40405635
@slubek,
Still the same error.

@Dale,
We do have Employees that work in sleep in shifts, however your idea of counting the first day of week may work, how do I apply that?

Thanks,
Ben
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40405736
Hello,

Try This

select pes.employeeID, DATEPART(wk, pes.Day), sum(DATEDIFF(HH,pes.To , pes.From))
  from PatientsEmployeesSchedule pes
    right join table2 t2 on t2.employeeID=pes.employeeID
  group by DATEPART(wk, pes.Day)
  having sum(DATEDIFF(HH,pes.To , pes.From))>30

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40406636
SELECT t2.*, t1.Week_Hours_Worked, t1.Week_Start
FROM table2 t2
LEFT OUTER JOIN (
    SELECT EmployeeID, DATEADD(DAY, DATEDIFF(DAY, 6, [Day]) / 7 * 7, 6) AS Week_Start,
        CAST(SUM(DATEDIFF(MINUTE, [From], [To])) / 60.0 AS decimal(5, 2)) AS Week_Hours_Worked
    FROM table1
    GROUP BY EmployeeID, DATEADD(DAY, DATEDIFF(DAY, 6, [Day]) / 7 * 7, 6)
) AS t1 ON
    t2.EmployeeID = t1.EmployeeID AND
    t2.[Day] >= t2.Week_Start AND
    t2.[Day] < DATEADD(DAY, 1, t2.Week_Start)


Btw, for best performance, stop clustering table1 on "ID" and cluster it on ( [Day], [From], EmployeeID ) instead ["From" might not need to be in the key, depending on the specific queries most commonly used for that table].
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40407391
@Vikas,
Your version with a little adjustment worked, however I would still have the issue of End year mentioned above (ID: 40405428).
@Scott,
I tried copying your version, and just replaced the table names with my own, and it gave me the following:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Week_Start'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'Week_Start'.

@All Experts,
I end up doing similar to what  Dale suggested, created the following function in access:
Public Function EmpHave30HoursSched(EmpID As Long, Day As Date) As Boolean
    Dim db As Database, rs As Recordset, Ssql As String
    Set db = CurrentDb
    Ssql = "SELECT Sum(MyDateDiff([From], [To])) as ApptHours From PatientsEmployeesSchedule"
    Ssql = Ssql & " where EmployeeID = " & EmpID & " and Day between #" & FirstDayOfWeek(Day) & "# and #" & FirstDayOfWeek(Day) + 6 & "#"
    Set rs = db.OpenRecordset(Ssql)
    If rs.EOF Then Exit Function
    rs.MoveFirst
    If rs(0) >= 1800 Then
        EmpHave30HoursSched = True
    Else
        EmpHave30HoursSched = False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

Open in new window

Public Function FirstDayOfWeek(Day As Date) As Date
    FirstDayOfWeek = Day + 1 - WeekDay(Day)
End Function

Open in new window

Public Function MyDateDiff(Optional dFrom, Optional dTo) As Integer

    If IsMissing(dFrom) Or IsMissing(dTo) Then
        MyDateDiff = 0
    ElseIf dFrom = dTo Then
        MyDateDiff = 780
    Else
        MyDateDiff = Nz(DateDiff("N", dFrom, dTo))
        If MyDateDiff < 0 Then MyDateDiff = 1440 + MyDateDiff
    End If
    
End Function

Open in new window


(We count 24 shift as 13 hours only, therefore I need the mydatediff function)
and I set the checkbox control source property = "EmpHave30HoursSched(EmployeeID,Day)
so far in my testing this works,
does someone have any comments/suggestions/subjections?

Thanks,
Ben
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40411699
As mentioned, this is the way it worked out best, thank you!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40411712
Glad to help.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question