Help needed in design a SQL query/view

Hi Experts,

I have the following fields in PatientsEmployeesSchedule table

ID (AutoNum)
EmployeeID (int)
PatientID (int)
Day (DateTime) containing a date of schedule started ( ex, 10/29/15)
From (DateTime) containing time schedule started (like 9AM)
To (DateTime) containing time schedule ended (like 5PM)

(I know the name of fields are not the best as they are reserved words, but I cant change them at this point:(

And would want to have the following query executing in the most efficient manner

A total of hours scheduled ([To]-[From]) per employee, grouped by week (Sun-Sat)
But in case its a 24 hour shift ([From]=8AM - [To]=8AM), it should only calculate as 13 hours.

schedules can be entered as full hours (1AM - 2AM), half hours (1AM - 1:30AM) and 15 min (1AM - 1:15AM)

Thanks
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bfuchsAuthor Commented:
Hi Experts,

I will not be in the office tom, please post your answers and will test/reply on Sunday.

Have a nice weekend,

Thank you!
0
bfuchsAuthor Commented:
Attached a sample data of the table in question
untitled.bmp
0
Maria Benedict Nishanth ACommented:
Could you please more elaborate on your point "A total of hours scheduled ([To]-[From]) per employee, grouped by week (Sun-Sat)
But in case its a 24 hour shift ([From]=8AM - [To]=8AM), it should only calculate as 13 hours
."

I could help you....
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.

PortletPaulfreelancerCommented:
It will be impossible to answer this question without some details that were revealed in the prior question about this same table. see http://www.experts-exchange.com/questions/28783059/Help-needed-creating-a-union-query-in-SQL.html

Most notable is that a row of data can look like this:

ID          EmployeeID    PatientID     Day                From           To
1           123                  456              10/27/15        8:00 PM       8:00 AM

but the [From] and [To] columns actually hold the date 1899-12-30

i.e.
ID          EmployeeID    PatientID   [Day]                [From]                               [To]
1           123                  456              2015-10-27      1899-12-30 20:00:00      1899-12-30 08:00:00

AND that row of data represents a 12 hour shift commencing 2015-10-27 20:00:00, concluding  2015-10-28 08:00:00
0
PortletPaulfreelancerCommented:
Will this query be run for "the most recent week" only, or are you expecting it to run for all weeks every time you run it? Would you want to supply a "from date"?

Do you have a calendar table?
Do you have a table of integers?
What indexes exist on PatientsEmployeesSchedule ?

Can you provide "sample data" and "expected result" please.
Note the data should accurately reflect how it is really stored.

& nb: a "sample" does not have to be big or huge
0
bfuchsAuthor Commented:
Hi Experts,

@Maria,
Yes, that's the rule I was given, instead of counting 24 hours for live-in cases (8AM - 8AM) it should only count 13.

@Paul,
but the [From] and [To] columns actually hold the date 1899-12-30
Correct, actually everything that were explained there applies here as well, except the fact that here the records don’t need to be split, as they belong to the date specified in "Day" column.

I am attaching a screenshot of the SSMS and also including sample data in excel as requested.

I will be looking at one employees record for a given week at a time, so perhaps would be the ideal way to have this returned by a SQL function that has two parameters (EmployeeID as int, WeekStarted as date) and returns an int (count of hours).

A Calendar table I just created while working in the other post (for Brian's suggestion), and integer table I can create.

Included the table indexes in the attachment.

Let me know if you need anything else.

Thanks,
BenssmsPatEmpSched.csv
0
PortletPaulfreelancerCommented:
You need to provide sample data AND expected results

PLUS the expected results MUST relate to the data you provide

It is wise therefore to supply data that includes all your "edge cases"
e.g.
  8AM to 8AM
  when the TO is before the FROM (spans a midnight)

It is called a "short, self contained, correct example" see http://sscce.org/
0
bfuchsAuthor Commented:
@Paul,

OK, attached are both, the sample data included the above & expected results.

Thanks,
Ben
PatEmpSched.csv
PatEmpSchedRes.xls
0
bfuchsAuthor Commented:
@Paul,

Below is how I did that in Access query/VBA

SELECT Count(PatientsEmployeesSchedule.ID) AS CountOfID, PatientsEmployeesSchedule.EmployeeID, Sum(mydatediff([from],[to]))/60 AS TotalHours, Format([patientsemployeesschedule].[day],"ww-yy") AS Week
FROM PatientsEmployeesSchedule
WHERE (((PatientsEmployeesSchedule.Day)=#10/1/2015# Or (PatientsEmployeesSchedule.Day)=#10/2/2015# Or (PatientsEmployeesSchedule.Day)=#10/3/2015# Or (PatientsEmployeesSchedule.Day)=#10/4/2015# Or (PatientsEmployeesSchedule.Day)=#10/5/2015#) AND ((PatientsEmployeesSchedule.EmployeeID) In (1800,26104,74890,75137,153066)))
GROUP BY PatientsEmployeesSchedule.EmployeeID, Format([patientsemployeesschedule].[day],"ww-yy");

Open in new window


and this is my VBA function MyDateDiff
Public Function MyDateDiff(Optional dFrom, Optional dTo) As Integer

    If Not IsNull(dFrom) And dFrom = dTo Then
        MyDateDiff = 780
    ElseIf IsNull(dFrom) Or IsNull(dTo) Then
        MyDateDiff = 0
    Else
        MyDateDiff = Nz(Abs(DateDiff("N", dFrom, dTo)))
    End If
    
End Function

Open in new window


Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Paul,

Let me know if there is anything else you need me to provide.

Thanks,
Ben
0
bfuchsAuthor Commented:
Just thinking, perhaps would be faster to run two queries, one that calculates all [to] - [from], and in case of live-in that both are 8AM the total would be 0, the other query will only take all the live-in cases and calculate 13 for each record.

Does it make sense?

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Experts,
At this point I managed to create a function that would do the job.

However if someone can look into the code and see where it can be modified to improve efficiency I would appreciate.

USE [HomeCare]
GO
/****** Object:  UserDefinedFunction [dbo].[fnEmpWeeklyHoursSched]    Script Date: 11/04/2015 20:29:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[fnEmpWeeklyHoursSched] 
(
	@empid	AS	int,
	@Day	AS	datetime,
	@SchedID as int,
	@From as Datetime = null,
	@To as Datetime = null
)
RETURNS int
AS
BEGIN
	DECLARE @Result AS int = 0;
	
	if @SchedID = 0
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6); 
	else
	begin	   
	SET @Result = (SELECT Sum(dbo.fnMyDateDiff([From], [To])) FROM PatientsEmployeesSchedule
		 WHERE EmployeeID = @empid 
		   AND [Day] BETWEEN dbo.fnFirstDayOfWeek(@Day) AND dbo.fnFirstDayOfWeek(@Day)+6
			and ID <> @SchedID); 
	SET @Result = @Result + dbo.fnMyDateDiff(@From, @To);
	end			

	RETURN @Result;
END
------------------------------------------------------------


/****** Object:  UserDefinedFunction [dbo].[fnFirstDayOfWeek]    Script Date: 11/05/2015 15:30:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[fnFirstDayOfWeek](@Day As datetime)
 returns datetime
 AS
 Begin

	DECLARE @Result datetime

    set @Result = @Day + 1 - DATEPART(weekday,@day) 
    RETURN @Result;
End 

----------------------------
/****** Object:  UserDefinedFunction [dbo].[fnMyDateDiff]    Script Date: 11/05/2015 15:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnMyDateDiff] 
(
	@dFrom	AS	datetime = NULL,
	@dTo	AS	datetime = NULL
)
RETURNS smallint
AS
BEGIN
	DECLARE @Result smallint

	IF @dFrom IS NULL OR @dTo IS NULL
		SET @Result = 0;
	ELSE IF @dFrom = @dTo
		SET @Result = 780;
	ELSE
	BEGIN
		SET @Result = ISNULL(DATEDIFF(n,@dFrom,@dTo),0);
		IF @Result < 0 SET @Result = 1440 + @Result;
	END;
	RETURN @Result;
END

Open in new window


Thanks,
Ben
0
JimFiveCommented:
I can't test this right now, but I would follow the following plan:
First Add Day + to and Day + from to get full datetime fields.
Then if "Day+to" < "Day + from" add 1 day to "day + to" so it is in the future.
Then summarize by week:
So:
SELECT Datepart("w",Day) as WeekNum, sum(CASE WHEN datediff("nn",dayfrom, dayto)=(24*60)
                                THEN (13*60) ELSE datediff("nn",dayfrom,dayto) END as TotalMinutes
FROM (SELECT [Day], [Day]+[From] as Dayfrom, [Day]+[To]+CASE WHEN From=To THEN 1 ELSE 0 END as DayTo
       FROM PatientScheduleTable) T
GROUP BY Datepart("w",Day)

Open in new window

--
JimFive
0
bfuchsAuthor Commented:
@Jim,

So far, I was able to get the inner select to work as following.
SELECT Datepart("w",[Day]) as WeekNum,
 sum(CASE WHEN datediff("n",dayfrom, dayto)=(24*60)
                                THEN (13*60) ELSE datediff("n",dayfrom,dayto) END as TotalMinutes
FROM (SELECT [Day], [Day]+[From] as Dayfrom, [Day]+[To] + CASE WHEN [From]=[To] THEN 1 ELSE 0 END as DayTo
       FROM PatientsEmployeesSchedule) T
GROUP BY Datepart("w",Day)

Open in new window

However the outher select is still not working, I get the following error
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'T'.

Let me know how to proceed

Thanks,
Ben
0
PortletPaulfreelancerCommented:
I have been unable to contribute for a few days, and I'm a little behind on many things.

You provided some "sample data" and a "result" some while back, but I cannot figure how you get that result from that data. bfuchs.png
0
bfuchsAuthor Commented:
Welcome Back Paul:)

If you look above post (ID: 41194648), I have that logic converted to SQL, however the results you saw where from an Access VBA function, do you want me to post that as well?

Thanks,
Ben
0
PortletPaulfreelancerCommented:
From this data (combines the "sample" and the "results" provided in spreadsheets)
CREATE TABLE PatientsEmployeesSchedule
    ([ID] int, [EmployeeID] int, [PatientID] int, [Day] datetime, [From] datetime, [To] datetime)
;
    
INSERT INTO PatientsEmployeesSchedule
    ([ID], [EmployeeID], [PatientID], [Day], [From], [To])
VALUES
    (2530135, 1800, 1, '2015-10-02 00:00:00', '1899-12-30 08:00:00', '1899-12-30 18:00:00'),
    (2530137, 1800, 1, '2015-10-03 00:00:00', '1899-12-30 08:00:00', '1899-12-30 20:00:00'),
    (2530139, 1800, 1, '2015-10-04 00:00:00', '1899-12-30 08:00:00', '1899-12-30 20:00:00'),
    (2530141, 1800, 1, '2015-10-05 00:00:00', '1899-12-30 08:00:00', '1899-12-30 20:00:00'),
    (2545937, 26104, 1, '2015-10-02 00:00:00', '1899-12-30 17:30:00', '1899-12-30 00:00:00'),
    (2545977, 26104, 1, '2015-10-02 00:00:00', '1899-12-30 17:30:00', '1899-12-30 00:00:00'),
    (2536703, 74890, 1, '2015-10-01 00:00:00', '1899-12-30 13:30:00', '1899-12-30 21:30:00'),
    (2536704, 74890, 1, '2015-10-02 00:00:00', '1899-12-30 14:00:00', '1899-12-30 21:15:00'),
    (2536705, 74890, 1, '2015-10-05 00:00:00', '1899-12-30 13:15:00', '1899-12-30 20:15:00'),
    (2311789, 75137, 1, '2015-10-01 00:00:00', '1899-12-30 13:00:00', '1899-12-30 16:00:00'),
    (2311790, 75137, 1, '2015-10-02 00:00:00', '1899-12-30 13:00:00', '1899-12-30 16:00:00'),
    (2311791, 75137, 1, '2015-10-03 00:00:00', '1899-12-30 09:15:00', '1899-12-30 12:00:00'),
    (2311792, 75137, 1, '2015-10-04 00:00:00', '1899-12-30 09:00:00', '1899-12-30 12:00:00'),
    (2311793, 75137, 1, '2015-10-05 00:00:00', '1899-12-30 13:00:00', '1899-12-30 16:00:00'),
    (2524980, 75137, 1, '2015-10-01 00:00:00', '1899-12-30 09:15:00', '1899-12-30 12:15:00'),
    (2525499, 153066, 1, '2015-10-01 00:00:00', '1899-12-30 08:00:00', '1899-12-30 08:00:00'),
    (2525500, 153066, 1, '2015-10-02 00:00:00', '1899-12-30 08:00:00', '1899-12-30 08:00:00'),
    (2525501, 153066, 1, '2015-10-03 00:00:00', '1899-12-30 08:00:00', '1899-12-30 08:00:00'),
    (2605341, 149265, 5887, '2015-10-31 00:00:00', '1899-12-30 14:00:00', '1899-12-30 20:00:00'),
    (2605340, 140532, 5715, '2015-10-31 00:00:00', '1899-12-30 09:00:00', '1899-12-30 14:00:00'),
    (2605339, 140532, 5715, '2015-10-30 00:00:00', '1899-12-30 09:00:00', '1899-12-30 13:00:00'),
    (2605338, 140532, 5715, '2015-10-29 00:00:00', '1899-12-30 08:30:00', '1899-12-30 13:30:00')
;

Open in new window

I got this result:
| EmployeeID | dayOfWeek | WeekStarting | TotalMinutes |
|------------|-----------|--------------|--------------|
|       1800 |    Monday |   2015-09-28 |         1320 |
|      26104 |    Monday |   2015-09-28 |         2100 |
|      74890 |    Monday |   2015-09-28 |          915 |
|      75137 |    Monday |   2015-09-28 |          705 |
|     153066 |    Monday |   2015-09-28 |         2340 |
|       1800 |    Monday |   2015-10-05 |         1440 |
|      74890 |    Monday |   2015-10-05 |          420 |
|      75137 |    Monday |   2015-10-05 |          360 |
|     140532 |    Monday |   2015-10-26 |          840 |
|     149265 |    Monday |   2015-10-26 |          360 |

Open in new window

Using This query - which borrows from the suggestion by JimFive - although I have used a cross apply :
SELECT
      EmployeeID
    , DATENAME(weekday,ca.WeekStarting)    AS dayOfWeek
    , FORMAT(ca.WeekStarting,'yyyy-MM-dd') AS WeekStarting
    , SUM (CASE WHEN DATEDIFF (minute, dayfrom, dayto) = (24 * 60)
                   THEN (13 * 60) 
                   ELSE ABS(DATEDIFF(minute, dayfrom, dayto))
           END) AS TotalMinutes
FROM PatientsEmployeesSchedule
      CROSS APPLY (
            SELECT
                  DATEADD(WEEK, DATEDIFF(WEEK, 0, [Day]), 0) AS WeekStarting
                , [Day] + [From] AS Dayfrom
                , [Day] + [To] + CASE
                        WHEN [From] = [To] THEN 1
                        ELSE 0
                  END AS DayTo
      ) AS CA
GROUP BY
      EmployeeID
    , ca.WeekStarting

Open in new window

Note I prefer to use dates instead of week number, that way there is no issue if data spans 1/Jan and/or more than 1 year, so I use
DATEADD(WEEK, DATEDIFF(WEEK, 0, [Day]), 0)
to calculate the Monday of each week. (nb: If Monday is not desired what is?)

see: http://sqlfiddle.com/#!6/8c168/6

=============
and finally:
I have no idea if the result I calculated above  is correct or not - that is why the "expected result" should be provided by you.
0
JimFiveCommented:
bfuchs,
It looks like I missed a close parenthesis after the END, so:
SELECT Datepart("w",Day) as WeekNum, sum(CASE WHEN datediff("nn",dayfrom, dayto)=(24*60)
                                THEN (13*60) ELSE datediff("nn",dayfrom,dayto) END) as TotalMinutes
FROM (SELECT [Day], [Day]+[From] as Dayfrom, [Day]+[To]+CASE WHEN From=To THEN 1 ELSE 0 END as DayTo
       FROM PatientScheduleTable) T
GROUP BY Datepart("w",Day)

Open in new window

0
bfuchsAuthor Commented:
@Paul,

I get the following when using your query in our version of ssms (2008)

Msg 195, Level 15, State 10, Line 4
'FORMAT' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.

The week we need to calculate goes from Sun-Sat.

Thanks,
Ben
0
bfuchsAuthor Commented:
@Jim,

Yours (after changing the 'nn' which is not a valid param to 'n') is still giving me the following error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'From'.

Thanks,
Ben
0
PortletPaulfreelancerCommented:
You are allowed to fix errors yourself you know.

FORMAT() isn't known to SQL Server 2008, so you should remove it

It can be replaced with CONVERT() e.g.

convert(varchar(10), your_data_here ,121)

The following has been adjusted to start the week on a Sunday as well
SELECT
      EmployeeID
    , DATENAME(weekday,ca.WeekStarting)    AS dayOfWeek
    , CONVERT(varchar(10),ca.WeekStarting,121) AS WeekStarting
    , SUM (CASE WHEN DATEDIFF (minute, dayfrom, dayto) = (24 * 60)
                   THEN (13 * 60) 
                   ELSE ABS(DATEDIFF(minute, dayfrom, dayto))
           END) AS TotalMinutes
FROM PatientsEmployeesSchedule
      CROSS APPLY (
            SELECT
                  DATEADD(WEEK, DATEDIFF(WEEK, -1, [Day]), -1) AS WeekStarting /* for Sunday start */
                , [Day] + [From] AS Dayfrom
                , [Day] + [To] + CASE
                        WHEN [From] = [To] THEN 1
                        ELSE 0
                  END AS DayTo
      ) AS CA
GROUP BY
      EmployeeID
    , ca.WeekStarting

Open in new window

0
JimFiveCommented:
Missed bracketing your field name From
SELECT Datepart(ww,Day) as WeekNum, sum(CASE WHEN datediff(mi,dayfrom, dayto)=(24*60)
                                THEN (13*60) ELSE datediff(mi,dayfrom,dayto) END) as TotalMinutes
FROM (SELECT [Day], [Day]+[From] as Dayfrom, [Day]+[To]+CASE WHEN [From]=[To] THEN 1 ELSE 0 END as DayTo
       FROM PatientScheduleTable) T
GROUP BY Datepart(ww,Day)

Open in new window

0
bfuchsAuthor Commented:
Hi Experts,

Well, now both are working (without errors), however Paul's version is returning almost double amount of records than Jim's..will have to figure out this.

@Paul,Jim

where do I add a where clause, when looking for for a specific employee and date?

Thanks,
Ben
0
PortletPaulfreelancerCommented:
The WHERE clause always follows the FROM clause.

SELECT
FROM
      inner join
      left join
      cross apply
      outer apply
      ....
WHERE
GROUP BY
HAVING
ORDER BY

For the "date" in that where clause it should be a "date range" and I recommend AVOIDING use of BETWEEN
(for more on this topic please see: "Beware of Between")

e.g.
WHERE EmployeeID = 123456
AND [Day] >= '20151001' AND [Day] < '20151008'

note: Both those dates are Sundays

SELECT
      EmployeeID
    , DATENAME(weekday,ca.WeekStarting)    AS dayOfWeek
    , CONVERT(varchar(10),ca.WeekStarting,121) AS WeekStarting
    , SUM (CASE WHEN DATEDIFF (minute, dayfrom, dayto) = (24 * 60)
                   THEN (13 * 60) 
                   ELSE ABS(DATEDIFF(minute, dayfrom, dayto))
           END) AS TotalMinutes
FROM PatientsEmployeesSchedule
      CROSS APPLY (
            SELECT
                  DATEADD(WEEK, DATEDIFF(WEEK, -1, [Day]), -1) AS WeekStarting /* for Sunday start */
                , [Day] + [From] AS Dayfrom
                , [Day] + [To] + CASE
                        WHEN [From] = [To] THEN 1
                        ELSE 0
                  END AS DayTo
      ) AS CA
WHERE EmployeeID = 123456
      AND [Day] >= '20151001' AND [Day] < '20151008'
GROUP BY
      EmployeeID
    , ca.WeekStarting

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks experts,
from what I’ve tested so far, both solutions are working well
Great job!
0
Maria Benedict Nishanth ACommented:
Great job Paul Maxwell....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.