Sql question

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

Select allOpen in new window

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.

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

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.

@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

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

Select allOpen in new window

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].

@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

Select allOpen in new window

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

Select allOpen 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

Select allOpen 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

As mentioned, this is the way it worked out best, thank you!

Glad to help.