Solved

Sql question

Posted on 2014-10-26
11
219 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

813 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

18 Experts available now in Live!

Get 1:1 Help Now