Solved

Sql question

Posted on 2014-10-26
11
215 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
  • 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 3

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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

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 14

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:ScottPletcher
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 3

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 3

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now