Solved

SQL Report Query

Posted on 2013-06-01
8
343 Views
Last Modified: 2013-06-04
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.

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].[TIMESHEET]
                                    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([FirstName] + [SURNAME], @Month, @Year)  as Capcaity
      , dbo.ufn_GetWorkedHours([FirstName] + [SURNAME], @Month, @Year) as WorkedHours
   FROM [Timesheets].[dbo].[Staff]
  WHERE [current] = 1
  ORDER  BY [FirstName]
      ,[SURNAME]
0
Comment
Question by:Kevin Robinson
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:expert_dharam
ID: 39213536
Create  stored procedure to store this query..
Execution time should improve..
0
 
LVL 5

Accepted Solution

by:
DOSLover earned 500 total points
ID: 39213564
Assuming the tables have right indexes based, a join may be faster that UDF:
SELECT  s.FirstName
      , s.SURNAME
        , Year(t.date)
        , DATENAME(month, t.date)
      , Sum(w.Hours) as Capcaity
      , SUM(t.time) as WorkedHours
        , Sum(w.Hours) - SUM(t.time) as wVariance
   FROM Timesheets t
   LEFT JOIN Staff s ON s.UserName = t.idVolNow
   LEFT JOIN workdays w on w.UserName = t.idVolNow and Year(t.date)=Year(w.date) and DATENAME(month, w.date) = DATENAME(month, t.date)
  WHERE [current] = 1
  GROUP BY s.FirstName, s.SURNAME, Year(t.date), DATENAME(month, t.date)
  ORDER BY s.FirstName, s.SURNAME, Year(t.date), DATENAME(month, t.date)
0
 
LVL 3

Author Comment

by:Kevin Robinson
ID: 39213601
Hey DOSLover this looks good except the Staff Members that have not entered any timesheet info are not appearing in the query.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39213698
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.
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39213811
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)
0
 
LVL 3

Author Comment

by:Kevin Robinson
ID: 39219097
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_GetFullTimeStaffReport]
(
@Month      varchar(30),
@YEAR      int
)
AS
SELECT     TIMESHEET.IdVolNow as UserName,
dbo.ufn_GetCapacity(TIMESHEET.IdVolNow, @Month, @YEAR) AS Capacity,
SUM(TIMESHEET.TIME) AS HoursWorked,
SUM(TIMESHEET.TIME) - dbo.ufn_GetCapacity(TIMESHEET.IdVolNow, @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(UserName, @Month, @YEAR) AS Capacity,
0 AS HoursWorked,  dbo.ufn_GetCapacity(UserName, @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
0
 
LVL 3

Author Closing Comment

by:Kevin Robinson
ID: 39219102
Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39220701
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)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 27
what are the unique tables in SQL master database 5 61
Create snapshot on MSSQL 2012 3 18
sql server service accounts 4 25
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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