Solved

Change SQL question

Posted on 2015-01-12
13
311 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
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 36

Assisted Solution

by:PatHartman
PatHartman earned 166 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 69

Accepted Solution

by:
Scott Pletcher earned 167 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 69

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 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