Kevin Robinson
asked on
SQL Report Query
Hi
I will start with my end result required.
So I want to get a query like
ActualHoursWorked HoursToWork Variance
WendyOSBORNE 100 120 20
YvonneDOLAN 90 100 10
Sandra AADAIR 110 140 30
I have a timesheets table (with the only the columns relating to my question below) which holds timesheet information and hours actually worked.
TABLE [dbo].[TIMESHEET](
[DATE] [datetime] NULL,
[TIME] [decimal](4, 2) NOT NULL,
[IdVolNow] as varchar(50)
sample data
RefId DATE TIME IdVolNow
28521 2011-02-28 00:00:00.000 1.25 FidelmaGLASS
28591 2011-02-28 00:00:00.000 0.50 FidelmaGLASS
40030 2011-02-28 00:00:00.000 4.75 FidelmaGLASS
50046 2012-02-22 00:00:00.000 8.00 NicciWILSON
54758 2011-06-03 00:00:00.000 2.50 RuthMULHOLLAND
62266 2011-01-03 00:00:00.000 4.00 WendaGRAY W
62290 2011-01-03 00:00:00.000 7.00 Lyne GGREENWOOD
62291 2011-01-04 00:00:00.000 7.00 Lyne GGREENWOOD
62292 2011-01-07 00:00:00.000 7.00 Lyne GGREENWOOD
I have another table which specifies the days and hours that an employee should be working.
sample data
ID Date UserName Hours
365 2013-01-01 KathyCORBETT 6
366 2013-01-02 KathyCORBETT 6
367 2013-01-03 KathyCORBETT 6
368 2013-01-04 KathyCORBETT 6
369 2013-01-07 KathyCORBETT 6
370 2013-01-08 KathyCORBETT 6
I have a staff table with staff names
WendyOSBORNE
YvonneDOLAN
Sandra AADAIR
First of all i have the query that I want partially working but a bit slow. Im sure there is a better way to do what I want that the way I describe below.
I created a Function for Total Hours To Work, referred to here as capacity.
ALTER FUNCTION [dbo].[ufn_GetCapacity]
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS Int
BEGIN
DECLARE @Capacity int
Select @Capacity = (SELECT Sum(Hours) as TotalHours
FROM WorkDays WHERE
DATENAME(month, [Date]) = @Month
AND YEAR([Date]) = @Year
AND UserName=@UserName)
Return @Capacity
created a function for total hours actually worked
ALTER FUNCTION [dbo].[ufn_GetWorkedHours]
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS Int
BEGIN
DECLARE @WorkedHours int
Select @WorkedHours = (SELECT Sum([TIME]) as Total FROM [Timesheets].[dbo].[TIMESH EET]
WHERE [IdVolNow] = @Username
AND Year([Date]) = @Year
AND DATENAME(month, [Date]) = @Month)
Return @WorkedHours
END
I can now run a query like below. Which does actually work but
1) is a bit slow about 15sec
2) I still have to work the variance which im sure will add another 10 secs if i continue using this method.
SELECT [FirstName]
,[SURNAME]
, dbo.ufn_GetCapacity([First Name] + [SURNAME], @Month, @Year) as Capcaity
, dbo.ufn_GetWorkedHours([Fi rstName] + [SURNAME], @Month, @Year) as WorkedHours
FROM [Timesheets].[dbo].[Staff]
WHERE [current] = 1
ORDER BY [FirstName]
,[SURNAME]
I will start with my end result required.
So I want to get a query like
ActualHoursWorked HoursToWork Variance
WendyOSBORNE 100 120 20
YvonneDOLAN 90 100 10
Sandra AADAIR 110 140 30
I have a timesheets table (with the only the columns relating to my question below) which holds timesheet information and hours actually worked.
TABLE [dbo].[TIMESHEET](
[DATE] [datetime] NULL,
[TIME] [decimal](4, 2) NOT NULL,
[IdVolNow] as varchar(50)
sample data
RefId DATE TIME IdVolNow
28521 2011-02-28 00:00:00.000 1.25 FidelmaGLASS
28591 2011-02-28 00:00:00.000 0.50 FidelmaGLASS
40030 2011-02-28 00:00:00.000 4.75 FidelmaGLASS
50046 2012-02-22 00:00:00.000 8.00 NicciWILSON
54758 2011-06-03 00:00:00.000 2.50 RuthMULHOLLAND
62266 2011-01-03 00:00:00.000 4.00 WendaGRAY W
62290 2011-01-03 00:00:00.000 7.00 Lyne GGREENWOOD
62291 2011-01-04 00:00:00.000 7.00 Lyne GGREENWOOD
62292 2011-01-07 00:00:00.000 7.00 Lyne GGREENWOOD
I have another table which specifies the days and hours that an employee should be working.
sample data
ID Date UserName Hours
365 2013-01-01 KathyCORBETT 6
366 2013-01-02 KathyCORBETT 6
367 2013-01-03 KathyCORBETT 6
368 2013-01-04 KathyCORBETT 6
369 2013-01-07 KathyCORBETT 6
370 2013-01-08 KathyCORBETT 6
I have a staff table with staff names
WendyOSBORNE
YvonneDOLAN
Sandra AADAIR
First of all i have the query that I want partially working but a bit slow. Im sure there is a better way to do what I want that the way I describe below.
What I did
I created a Function for Total Hours To Work, referred to here as capacity.
ALTER FUNCTION [dbo].[ufn_GetCapacity]
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS Int
BEGIN
DECLARE @Capacity int
Select @Capacity = (SELECT Sum(Hours) as TotalHours
FROM WorkDays WHERE
DATENAME(month, [Date]) = @Month
AND YEAR([Date]) = @Year
AND UserName=@UserName)
Return @Capacity
created a function for total hours actually worked
ALTER FUNCTION [dbo].[ufn_GetWorkedHours]
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS Int
BEGIN
DECLARE @WorkedHours int
Select @WorkedHours = (SELECT Sum([TIME]) as Total FROM [Timesheets].[dbo].[TIMESH
WHERE [IdVolNow] = @Username
AND Year([Date]) = @Year
AND DATENAME(month, [Date]) = @Month)
Return @WorkedHours
END
I can now run a query like below. Which does actually work but
1) is a bit slow about 15sec
2) I still have to work the variance which im sure will add another 10 secs if i continue using this method.
SELECT [FirstName]
,[SURNAME]
, dbo.ufn_GetCapacity([First
, dbo.ufn_GetWorkedHours([Fi
FROM [Timesheets].[dbo].[Staff]
WHERE [current] = 1
ORDER BY [FirstName]
,[SURNAME]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey DOSLover this looks good except the Staff Members that have not entered any timesheet info are not appearing in the query.
except the Staff Members that have not entered any timesheet info are not appearing in the query.
If current is part of Staff Or workdays it cannot be in the WHERE clause, it has to be in the JOIN or it will treat the OUTER JOIN as an INNER JOIN and you will not return all the rows.
If current is part of Staff Or workdays it cannot be in the WHERE clause, it has to be in the JOIN or it will treat the OUTER JOIN as an INNER JOIN and you will not return all the rows.
The statement can be tweaked based on such requirement. For example, say, we want to make Staff as the primary driving table - pull data for all users from Staff table whether they have workdays defined or have entered timesheet info or not. To acperkins question abt 'current=1' - I assumed it is there to retrieve current row (in case historical records are also stored) - or else that condition can be dropped.
SELECT s.FirstName
, s.SURNAME
, Year(w.date)
, DATENAME(month, w.date)
, Sum(w.Hours) as Capcaity
, SUM(t.time) as WorkedHours
, Sum(w.Hours) - SUM(t.time) as wVariance
FROM Staff s
LEFT JOIN workdays w on w.UserName = s.UserName
LEFT JOIN Timesheet t ON t.UserName = w.UserName and Year(t.date)=Year(w.date) and DATENAME(month, t.date) = DATENAME(month, w.date)
WHERE [current] = 1
GROUP BY s.FirstName, s.SURNAME, Year(w.date), DATENAME(month, w.date)
ORDER BY s.FirstName, s.SURNAME, Year(w.date), DATENAME(month, w.date)
SELECT s.FirstName
, s.SURNAME
, Year(w.date)
, DATENAME(month, w.date)
, Sum(w.Hours) as Capcaity
, SUM(t.time) as WorkedHours
, Sum(w.Hours) - SUM(t.time) as wVariance
FROM Staff s
LEFT JOIN workdays w on w.UserName = s.UserName
LEFT JOIN Timesheet t ON t.UserName = w.UserName and Year(t.date)=Year(w.date) and DATENAME(month, t.date) = DATENAME(month, w.date)
WHERE [current] = 1
GROUP BY s.FirstName, s.SURNAME, Year(w.date), DATENAME(month, w.date)
ORDER BY s.FirstName, s.SURNAME, Year(w.date), DATENAME(month, w.date)
ASKER
Hi that last query didnt quite work but I ended going with this, ( A Union Join). Hopefully you dont think it is too messy :). It works anyway. Thank for your excellent help.
ALTER PROCEDURE [dbo].[sp_GetFullTimeStaff Report]
(
@Month varchar(30),
@YEAR int
)
AS
SELECT TIMESHEET.IdVolNow as UserName,
dbo.ufn_GetCapacity(TIMESH EET.IdVolN ow, @Month, @YEAR) AS Capacity,
SUM(TIMESHEET.TIME) AS HoursWorked,
SUM(TIMESHEET.TIME) - dbo.ufn_GetCapacity(TIMESH EET.IdVolN ow, @Month, @YEAR) AS Variance
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
AND IsFullTime = 1
GROUP BY TIMESHEET.IdVolNow
UNION
SELECT UserName,
dbo.ufn_GetCapacity(UserNa me, @Month, @YEAR) AS Capacity,
0 AS HoursWorked, dbo.ufn_GetCapacity(UserNa me, @Month, 2013) AS Variance
FROM Staff
WHERE Staff.USername NOT IN (SELECT IdVolNow FROM TIMESHEET WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month) )
AND staff.[current] = 1
AND IsFullTime = 1
ORDER BY UserName
ALTER PROCEDURE [dbo].[sp_GetFullTimeStaff
(
@Month varchar(30),
@YEAR int
)
AS
SELECT TIMESHEET.IdVolNow as UserName,
dbo.ufn_GetCapacity(TIMESH
SUM(TIMESHEET.TIME) AS HoursWorked,
SUM(TIMESHEET.TIME) - dbo.ufn_GetCapacity(TIMESH
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
AND IsFullTime = 1
GROUP BY TIMESHEET.IdVolNow
UNION
SELECT UserName,
dbo.ufn_GetCapacity(UserNa
0 AS HoursWorked, dbo.ufn_GetCapacity(UserNa
FROM Staff
WHERE Staff.USername NOT IN (SELECT IdVolNow FROM TIMESHEET WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month) )
AND staff.[current] = 1
AND IsFullTime = 1
ORDER BY UserName
ASKER
Thanks
I suspect you may have overlooked my previous comment so I will try it again:
This:
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
Is exactly the same as this:
FROM TIMESHEET INNER JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
If you really want a LEFT JOIN then you should code it correctly as in:
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName AND staff.[current] = 1
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
This:
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
Is exactly the same as this:
FROM TIMESHEET INNER JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
AND staff.[current] = 1
If you really want a LEFT JOIN then you should code it correctly as in:
FROM TIMESHEET LEFT JOIN
Staff ON TIMESHEET.IdVolNow = Staff.UserName AND staff.[current] = 1
WHERE (YEAR(TIMESHEET.DATE) = @YEAR) AND (DATENAME(month, TIMESHEET.DATE) = @Month)
Execution time should improve..