Solved

Change SQL question

Posted on 2015-01-12
13
302 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Assisted Solution

by:Robert Sherman
Robert Sherman earned 167 total points
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
Comment Utility
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 69

Accepted Solution

by:
ScottPletcher earned 167 total points
Comment Utility
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
 
LVL 3

Author Closing Comment

by:bfuchs
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
@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
Comment Utility
@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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 3

Author Comment

by:bfuchs
Comment Utility
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
Comment Utility
@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 69

Expert Comment

by:ScottPletcher
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
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 3

Author Comment

by:bfuchs
Comment Utility
Hi Experts,

Please advise if there is any solution to this problem?

Thanks,
Ben
0
 
LVL 7

Expert Comment

by:Robert Sherman
Comment Utility
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 3

Author Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

12 Experts available now in Live!

Get 1:1 Help Now