Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Change SQL question

Posted on 2015-01-12
13
Medium Priority
?
322 Views
Last Modified: 2015-01-18
Hi Experts,

I have a scheduling table that consists of the following columns.

ID (PK)
PatientID (Int)
EmployeeID (Int)
Day (DateTime=Date of schedule)
From (DateTime=Time start of schedule)
To (DateTime=Time end of schedule)

The way the table is used is the day field indicates the start date of schedule, the field “From” indicates the start time of schedule and the field “to” indicates the end time of schedule which could possible go into the next day, however it can go more than one day ahead as we don’t have schedules of more then 24 hours.

I have the following functions, one in Access and one equivalent in SQL, checking if there are weekend records in the schedule table for a particular EmployeeID in the current or previous week.
Now the function only checks for records that the day is Sat or Sun, however I would like to modify that it should also check for records that day are Fri and the schedule TimeTo is >= 12 AM which in actuality the schedule went into Sat night.
How can this be accomplished in the most efficient manner?

SQL version
FUNCTION [dbo].[fnEmpInsHave4Weekends]
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;

	IF (SELECT COUNT(*)
		  FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day)-1 AND dbo.fnFirstDayOfWeek(@Day)) > 0
		SET @Result = 1;

	RETURN @Result;
END

Open in new window

Access version
FUNCTION [dbo].[fnEmpInsHave4Weekends]
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;

	IF (SELECT COUNT(*)
		  FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day)-1 AND dbo.fnFirstDayOfWeek(@Day)) > 0
		SET @Result = 1;

	RETURN @Result;
END

Open in new window

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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 668 total points
ID: 40545969
Assuming your time fields are consistently storing only the time (in other words, the date portion is always 1900-01-01).   Also, there's technically no way to search for >= 12AM the way you are thinking about it.   Logic dictates that EVERY time of day that is not 12AM is GREATER THAN 12AM.  Therefore, you need to come up with a cut-off time for the TimeTo field that would indicate shifts that started on Friday and not Saturday.  

In the below query, I chose 6:00AM as the cut-off.  The problem with this, is that if you have people that start work on Friday at, say, 1:00AM and finish work 4 hours later at 5:00AM, the day for that shift may also be "Friday" because that's when the shift started, but it's going to be included in the query below because there is no other way to account for time only with correct wrap-around for shifts that cross the midnight boundary.

I hope that makes sense in some way..   Basically, the idea of storing the times separate from the date has the potential to cause great headaches.


FUNCTION [dbo].[fnEmpInsHave4Weekends]
(
	@empid	AS	int,
	@Day	AS	smalldatetime
)
RETURNS bit
AS
BEGIN
	DECLARE @Result AS bit = 0;

	IF (SELECT COUNT(*)
		  FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day)-1 AND dbo.fnFirstDayOfWeek(@Day)
		   OR ([Day] = dbo.fnFirstDayOfWeek(@Day)-2 AND TimeTo <= '06:00') 
		   ) > 0
		SET @Result = 1;

	RETURN @Result;
END

Open in new window


EDIT: Also, from what I could tell, the SQL you posted was the same in both your SQL and Access examples.
0
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 664 total points
ID: 40546694
You should also be aware that Day, From, and To are reserved words and therefore should not be used as object names.  Short of memorizing lists of hundreds of names, the simplest solution to avoiding reserved words is to make compound names so - DayDT, FromTime, and ToTime would work in this case.  

By not including date in your From and To fields, you make it more difficult to do comparisons because you always have to determine if ToTime is the following day or the DayDT.  It also means that no one can actually work more than 24 hours.  That is rare but it does happen.

Friday 12AM is midnight so as Robert mentioned, all "times" would be >= 12 AM.  That is why including the date in the time field will help you.  What you would need to do to guess if the To time extends into Saturday is to compare the From and To times.  If Day is Friday and ToTime is Less than FromTime, ToTime can be assumed to be Saturday.  But, add the date and avoid the confusion.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 668 total points
ID: 40546944
For efficiency:
don't count all occurrences, just see if one exists;
use a single RETURN() statement only, avoiding any local variables;
rather than calling another function, use a method for determining day of week that is date and language-setting independent, i.e., will work correctly under all datefirst and language settings.


FUNCTION [dbo].[fnEmpInsHave4Weekends]
(
      @empid      AS      int,
      @Day      AS      smalldatetime
)
RETURNS bit
AS
BEGIN
RETURN (
    SELECT CASE WHEN EXISTS(
        SELECT 1
        FROM dbo.PatientsEmployeesSchedule pes
        WHERE
            pes.EmployeeID = @empid
          AND
                (DATEDIFF(DAY, 0, pes.[Day]) % 7 IN ( 5, 6 ) /*Sat or Sun*/
                 OR (DATEDIFF(DAY, 0, pes.[Day]) % 7 IN ( 4 ) /*Fri*/ AND pes.[From] < pes.[To]))
            )
    THEN 1
    ELSE 0
    END
)
END --FUNCTION
GO
0
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40547697
Hi Experts,
This is the type of discussion/answers I always prefer to get, where each expert adds something unique, and I end up with:
a- good solution
b- working for all situations
c- best performance.
Thank you!
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40548189
@scott,

a- I am testing your suggestion and for a record 11/28/14 (Fri) from 7 AM to 3 PM its returning true, which it shouldn't as both are on Fri, now I also have the following questions.
b- where do you compare against the @Day passed variable?
c- I tried changing the DATEDIFF(DAY.. to DATEDIFF(@DAY, and I got an error msg datediff  wrong number of arguments..

Can you please advise how to fix it?
Thanks,
Ben
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40548213
@bfuchs,

For item "a" in your question to Scott -- take a look at the time fields for the record in question and make sure the date portion hasn't been changed to something other than 1-1-1900.  it's possible that somehow the date part got changed in one of those time fields which would throw off the comparison between the times.  (Default date for those fields should be 1-1-1900
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40548235
Hi Robert,

First thanks for replying,

I checked and all time fields have their date portion 1899/12/30
however I  may be wrong with that assumption that this record is causing the results to be positive, very likely the question "b" is the culprit, as I brings up other schedules, not necessary for desired week.
I also tested that portion of SQL by itself (by selecting * instead of select 1) and confirmed that other records are showing as well..

If you have a fix to that let me know.

Thanks,
Ben
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40548342
@bfuchs,

I suspect there's some confusion going on here regarding the original question.  

As you mentioned, it doesn't look like Scott's solution is using the passed-in date parameter @Day.  (Scott, forgive me if I'm wrong)  I've been trying to figure out exactly what the DATEDIFFs are doing, to try to offer some advice on how to incorporate the incoming @Day, but it's late and my neurons don't seem to be talking to each other in this department.

To clarify what the function is supposed to do, you're passing in a date (@Day) and you want to see if an employee has a shift on the Friday, Saturday, or Sunday that occurs immediately before @Day.  So, looking at this month's calendar, let's say you pass in 1/14/2015, you want to see if the employee had a shift on 1/9, 1/10, or 1/11.  Is that right?

I think what Scott's function is doing right now is just looking for employees that have ANY shift at all that falls on a Friday, Saturday or Sunday.   (Again, I could be wrong here)    @Scott: when you compare using datediff(day, 0, pes.[Day]) what's the purpose of comparing to date "0"?   I'm intrigued by what this does.

@bfuchs: the error you got when you tried DATEDIFF(@Day, [...] was due to the placement of that variable.  The first parameter to DATEDIFF is the unit you want to count by.  So if you wanted to count the number of weeks between two dates, you could use DATEDIFF(Week, date1, date2) for example.  

Sorry I couldn't be more help.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40549086
Sorry, quite true, I left out the @Day conditions.

I think we just need to add back in the date range check:

AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day)-1 AND dbo.fnFirstDayOfWeek(@Day)

to the other conditions.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40549895
Hi Experts,

@Scott,
Added that condition, however function still not working, it gives me a record of Fri 7AM-3PM
See Attached.

@Robert,
you want to see if the employee had a shift on 1/9, 1/10, or 1/11.  Is that right?
Yes
Untitled.png
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40552096
Hi Experts,

Please advise if there is any solution to this problem?

Thanks,
Ben
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40554992
Hey @bfuchs,

I think I've spotted the problem.  Try flipping that less-than sign around, so that you have
pes.[From] > pes.[To]

Open in new window

As I recall, the reason for adding that criteria was that we were looking for shifts that ended after midnight, so the comparison should be seeking an end time that is earlier in a 24-hour day than the start time.

If that doesn't do it, let me know and I'll give the whole question another look.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40556664
Hi Robert,
It looks like you have got it!
I really appreciate that.
Thanks,
Beb
0

Featured Post

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

660 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