• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Change SQL question

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
bfuchs
Asked:
bfuchs
  • 6
  • 4
  • 2
  • +1
3 Solutions
 
Robert ShermanOwnerCommented:
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
 
PatHartmanCommented:
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
 
Scott PletcherSenior DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
@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
 
Robert ShermanOwnerCommented:
@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
 
bfuchsAuthor Commented:
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
 
Robert ShermanOwnerCommented:
@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
 
Scott PletcherSenior DBACommented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
Hi Experts,

Please advise if there is any solution to this problem?

Thanks,
Ben
0
 
Robert ShermanOwnerCommented:
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
 
bfuchsAuthor Commented:
Hi Robert,
It looks like you have got it!
I really appreciate that.
Thanks,
Beb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now