asked on

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

SOLUTION

Robert Sherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

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!

bfuchs

ASKER

@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

Robert Sherman

@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

bfuchs

ASKER

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

Robert Sherman

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

Scott Pletcher

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.

bfuchs

ASKER

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

bfuchs

ASKER

Hi Experts,

Please advise if there is any solution to this problem?

Thanks,
Ben

Robert Sherman

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.

bfuchs

ASKER

Hi Robert,
It looks like you have got it!
I really appreciate that.
Thanks,
Beb