SQL Query Help

I am trying to write a program to use at home... my son is impacted by Autism and we have employees paid by the state to help with his care.  I have all the time entry stuff working but now have to pull from the sql DB and generate reports.. The state makes it hard to track hours and if things are messed up people don't get paid for months.. im trying to make it easier on the employees that work at my house

I am kind of new to SQL/reporting and am trying to write a query but im stuck.
I have two tables.. one with employees  and one with clock in and out info
Here is what i started with - this pulls all time stamps that happened between two dates.. that works
SELECT clockData.fkEmp, concat(EmployeeInfo.EmpFirstName, EmployeeInfo.EmpLastName) as Name,  ClockData.ClockIn, ClockData.ClockOut, ClockData.ClockInDAte, ClockData.Is2on1 
FROM dbo.ClockData Inner Join EmployeeInfo ON ClockData.fkEmp=EmployeeInfo.pKey 
where fkEmp = '1' and ClockData.ClockInDate between '2018-03-17' and '2018-03-19'

Open in new window


notice there is a column called Is2On1
that lets me now if a single employee is logged in or if the are logged in as a second staff... I have to track an employees hours separately if they are the second staff
one staff could have hours as a primary and secondary staff in the same day.

so what i am trying to pull from this would look like this  (ignore the formatting ... this is an example of what a report might look like when done ..

Employee 1
                    Worked as primary (is2on1 not = true)
                         Date,  Time In, Time out, total hours worked
                         Date, Time In, Time out, total hours worked
                         Date, Time in, Time out, total hours worked
                                                                                 Sum Total hours

                   Worked as second staff (is2on1 = tru)
                         Date, Time In, Time out, Total hours worked
                          Date, Time In, Time out, Total hours worked
                          Date, Time In, Time out, Total hours worked
                                                                                Sum Total hours as second staff

in a perfect world one query would select, group and perform the time difference between the clock in and out ... i just get all kinds of errors when i try
Any help would be great
David ModugnoAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
The only change would be how you query on dates. Instead of doing this:

        WHERE  ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'

You'd use a >= and < range like this:

      AND    cd.ClockInDate >= '2018-03-17'
      AND    cd.ClockInDate < dateAdd(d, 1, CAST('2018-03-19' AS Date))

It's actually more flexible as it works with date only and date + times.  The above translates to this:

      AND    cd.ClockInDate >= '2018-03-17 00:00:00'  (On or after March 17 at midnight)
      AND    cd.ClockInDate < '2018-03-20 00:00:00'  (But before March 20 at midnight)

Example: DEMO - NEW TABLES
create table EmployeeInfoNew
(
EmployeeID INT identity
, FirstName varchar(100)
, LastName varchar(100)
)

create table ClockDataNew
(
ClockDataID int identity
, EmployeeID int
, ClockInDate datetime
, ClockOutDate datetime
, is2on1 BIT
)
go

-- ADD DEMO DATA
insert into EmployeeInfoNew values 
('John', 'Smith'), ('Bob', 'Column')
insert into ClockDataNew values 
(1, '2018-03-17 10:00:00', '2018-03-17 14:12:00', 0)
, (1, '2018-03-17 18:00:00', '2018-03-18 22:13:00', 1)
, (2, '2018-03-17 10:00:00', '2018-03-17 11:12:00', 0)
, (2, '2018-03-17 12:00:00', '2018-03-18 13:13:00', 1)
, (2, '2018-03-17 16:00:00', '2018-03-17 18:12:00', 0)
go

Open in new window


Example: QUERY
-- NEW QUERY
;WITH data
AS
(
	SELECT CASE WHEN is2On1 = 1 THEN 2 ELSE 4 END AS SortOrder
			, e.FirstName +' '+ e.LastName as EmployeeName
			, CASE WHEN is2On1 = 1 THEN 'Primary' ELSE 'Secondary Staff' END AS WorkType
			, ClockInDate
			, ClockOutDate
			, DateDiff(n, ClockInDate, ClockOutDate) / 60.0 AS TotalHours
			, is2on1
	FROM  ClockDataNew cd INNER JOIN EmployeeInfoNew e ON cd.EmployeeID = e.EmployeeID
	WHERE  e.EmployeeID = 1
	AND    cd.ClockInDate >= '2018-03-17' 
	AND    cd.ClockInDate < dateAdd(d, 1, CAST('2018-03-19' AS Date))
)
, totals AS
(
	SELECT CASE WHEN is2On1 = 1 THEN 1 ELSE 3 END AS SortOrder
			, 'Total '+ WorkType AS WorkType
			, SUM(TotalHours) TotalHours
	FROM   data
	GROUP BY CASE WHEN is2On1 = 1 THEN 1 ELSE 3 END
			, 'Total '+ WorkType
)
SELECT SortOrder, EmployeeName, ClockInDate, ClockOutDate, TotalHours FROM data 
UNION ALL 
SELECT SortOrder, WorkType, NULL, NULL, TotalHours FROM totals 
ORDER BY SortOrder

Open in new window

0
 
_agx_Commented:
Can you post the table table structure so we can see the data types?  Either a screen shot from SSMS OR the DLL select a table then > Script table as > CREATE TO > New Query Editor Window

Also, can you post a scrubbed sample of the data? ie don't include any personal data like names.
0
 
David ModugnoAuthor Commented:
I am adding pics of the table designs and of sample data of the ClockData table .. the other table just has their names and basic info.
table design for ClockData table
Employee-Info.png
Data-Example.png
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
David ModugnoAuthor Commented:
Thank you in advance for looking at this
0
 
_agx_Commented:
Thanks for the additional details. Do employees ever work across days, like ie Clock in at Friday 3/23 at 11:00 PM and clock out on Saturday 07:00 AM?
0
 
David ModugnoAuthor Commented:
So far no, but there is a possibility it could happen one day.. it would be nice to account for that now and not have it break later
Thanks again
0
 
_agx_Commented:
Sorry but I think I overestimated my recollection of grouping operators (and I'm using 2008). Without using some ugly, klunky SQL, I could only come up with the detail, not the sub totals. So I'd recommend getting other opinions.  

	SELECT e.EmpFirstName +' '+ e.EmpLastName as Name
			, ClockInDate
			, ClockIn
			, ClockOut
			, DateDiff(n, ClockInDateTime, ClockOutDateTime) / 60.0 AS TotalHours
			, is2on1
	FROM  #ClockData cd INNER JOIN #EmployeeInfo e ON cd.fkEmp=e.pKey 
			CROSS APPLY (SELECT CAST(ClockInDate AS DATETIME) + CAST(ClockIn AS DATETIME)) ckin(ClockInDateTime)
			CROSS APPLY (SELECT CAST(ClockInDate AS DATETIME) + CAST(ClockOut AS DATETIME)) ckout(ClockOutDateTime)
	WHERE  e.pKey = 2
	AND    cd.ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'

Open in new window

0
 
David ModugnoAuthor Commented:
Thank you for that.. its a good help
0
 
_agx_Commented:
I usually do aggregating on the front end, rather than in SQL, so I'd forgotten how rusty my grouping skills were ;-)

there is a possibility it could happen one day

Then you might consider changing the table structure.  Without a separate "check out date" columns, any queries would be inaccurate if the work ever spanned multiple days.  Also, it's easier to do date/time calculations if you store date and time in the same column IMO.  If that change would break too many things, you could also create a VIEW instead. Then use the VIEW in your queries.

You can do this without a VIEW, but just to show you what I meant .. here's an example. It gets the job done, but it's ... a little klunky IMO. So hopefully someone else will post something slicker.

Results:

Query Results
Create View (one time event)
CREATE VIEW EmployeeHoursByType
AS
SELECT 	CASE WHEN is2On1 = 1 THEN 'Primary' ELSE 'Secondary Staff' END AS WorkType
		, e.pKey AS EmployeeID
		, e.EmpFirstName +' '+ e.EmpLastName as Name
		, ClockInDate
		, ClockIn
		, ClockOut
		, DateDiff(n, ClockInDateTime, ClockOutDateTime) / 60.0 AS TotalHours
		, is2on1
FROM  ClockData cd INNER JOIN EmployeeInfo e ON cd.fkEmp=e.pKey 
			CROSS APPLY (SELECT CAST(ClockInDate AS DATETIME) + CAST(ClockIn AS DATETIME)) ckin(ClockInDateTime)
			CROSS APPLY (SELECT CAST(ClockInDate AS DATETIME) + CAST(ClockOut AS DATETIME)) ckout(ClockOutDateTime)
GO

Open in new window


Query:

;WITH data
AS
(
	SELECT *, CASE WHEN WorkType = 'Primary' THEN 2 ELSE 4 END AS SortOrder
	FROM   EmployeeHoursByType
	WHERE  EmployeeID = 1
	AND    ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'
)
, totals AS
(
	SELECT CASE WHEN WorkType = 'Primary' THEN 1 ELSE 3 END AS SortOrder
		, 'Total Worked as '+ WorkType AS WorkType
		, SUM(TotalHours) TotalHours
	FROM   data
	GROUP BY CASE WHEN WorkType = 'Primary' THEN 1 ELSE 3 END
			, 'Total Worked as '+ WorkType
)
SELECT SortOrder, Name, ClockInDate, ClockIn, ClockOut, TotalHours FROM data 
UNION ALL 
SELECT SortOrder, WorkType, NULL, NULL, NULL, TotalHours FROM totals 
ORDER BY SortOrder			

Open in new window

0
 
_agx_Commented:
If it doesn't have to be a single query, you could easily calculate the totals and details with a few separate queries. If you want, wrap it in a stored procedure so you only have to EXEC YourProcName @EmployeeID to get the results.  

Here's an example, again using the VIEW for simplicity

Results:

hours_worked_separate.pngQueries

--- Employee 

DECLARE @EmployeeID INT
SET @EmployeeID = 2

SELECT 'Total Worked as Primary' AS Category, SUM(TotalHours) AS TotalHours
FROM   EmployeeHoursByType
WHERE  ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'
AND    is2on1 = 1
AND    EmployeeID = @EmployeeID

SELECT Name, ClockInDate, ClockIn, ClockOut, TotalHours
FROM   EmployeeHoursByType
WHERE  ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'
AND    is2on1 = 1
AND    EmployeeID = @EmployeeID

SELECT 'Total Worked as Secondary' AS Category, SUM(TotalHours) AS TotalHours
FROM   EmployeeHoursByType
WHERE  ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'
AND    ISNULL(is2on1, 0) = 0 
AND    EmployeeID = @EmployeeID

SELECT Name, ClockInDate, ClockIn, ClockOut, TotalHours
FROM   EmployeeHoursByType
WHERE  ClockInDate BETWEEN '2018-03-17' AND '2018-03-19'
AND    ISNULL(is2on1, 0) = 0 
AND    EmployeeID = @EmployeeID

Open in new window

0
 
Max DestinyCommented:
Insted of sql you can create a database with the System.Resources  assembly:

Check my code and notes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Resources;

namespace ConsoleApp48
{
    class Program
    {
        const string addentry = "Add entry";

        const string deleteentry = "Delete entry";

        const string updateentry = "Update entry";

        const string getentries = "Get entries";

      

        static void Main(string[] args)
        {
            Console.WriteLine("What do you want to do?");

            string doing = Console.ReadLine();

            if (doing == addentry)
            {
                Console.WriteLine("Enter the name of the entry:");

                string entry = Console.ReadLine();

                //check if entry exists->File

                if (File.Exists(entry))
                {
                    Console.WriteLine("Entry already exists");

                    //return result that entry already exists
                }
                else
                {
                    File.Create(entry);
                    //create entry
                }
            }
            else if (doing == deleteentry)
            {
                Console.WriteLine("Enter the name of the entry:");

                string entry = Console.ReadLine();

                //check if entry exists->File

                if (File.Exists(entry))
                {
                    File.Delete(entry);

                    
                    //entry exists so->delete

                }
                else
                {
                    Console.WriteLine("Entry wasnt found");

                    //entry doesnt exist ->return message

                }
            }
            else if (doing == updateentry)
            {
                Console.WriteLine("Add name:");

                string name = Console.ReadLine();

                //assigning name

                Console.WriteLine("Add age:");

                string age = Console.ReadLine();

                //assigning age

                Console.WriteLine("Entry name :");

                string entry = Console.ReadLine();

                //assigning entry

                try//check if entry,File exists
                {
                    ResourceWriter resourceWriter = new ResourceWriter(entry);

                    resourceWriter.AddResource("name", name);

                    resourceWriter.AddResource("age", age);

                    resourceWriter.Generate();

                    resourceWriter.Close();

                    Console.WriteLine("Entry updated");

                    //add values to the entry
                }
                catch (Exception)//if entry doesnt exist do nothing->just return message 
                {
                    Console.WriteLine("Entry wasnt found");
                }
            }
            else if (doing == getentries)
            {
                DirectoryInfo directoryInfo = new DirectoryInfo(Directory.GetCurrentDirectory());

               //assign directory to programms directory

                foreach(var file in directoryInfo.GetFiles())//entry = File->foreach file exists in the dictionary
                {
                    if (file.Extension =="")//necessary because programms directory already contains files
                    {
                        try//some of the programms files with extension nothing are pre-made
                        {

                            ResourceReader resourceReader = new ResourceReader(file.FullName);//reader to use for reading each entry

                            byte[] bytes = { };

                            byte[] bytes1 = { };

                            string resourcetype = "";

                            resourceReader.GetResourceData("name", out resourcetype, out bytes);//get entry

                            resourceReader.GetResourceData("age", out resourcetype, out bytes1);//get entry


                            Console.WriteLine("Name: " + Encoding.ASCII.GetString(bytes) + "age: " + Encoding.ASCII.GetString(bytes1));//print message
                        }
                        catch (Exception)//end of try loop
                        {

                        }
                    }
                }
            }
            System.Threading.Thread.Sleep(3000);//sleep current thread to see the results
        }
    }
}

Open in new window


my code just save for name and age but you can add more
0
 
Max DestinyCommented:
very sorry for your kid
0
 
Max DestinyCommented:
my code is written in c#
0
 
David ModugnoAuthor Commented:
Thank you all for the answers... I will check them out today
0
 
David ModugnoAuthor Commented:
if i change the table structure and use Date/Time instead of time and get rid of the date column what might that query look like
thanks
0
 
David ModugnoAuthor Commented:
so im guessing i am not getting a result here because i need to perform formatting on the datetime column
select EmployeeID from ClockDataNew 
where ClockInDate = '2018-03-26' and ClockOutDate is null

Open in new window

0
 
David ModugnoAuthor Commented:
figured this part out :)

where cast(ClockInDate as date)
0
 
_agx_Commented:
Not formatting, but you do need to change how you do comparisons. When you do this, you're saying find records with a date of March 26 and  time of midnight.

          where ClockInDate = '2018-03-26'

Since the db records usually contain a time other than midnight, the query won't find a match. In other words, you're essentially doing this:

          where '2018-03-26 14:30' = = '2018-03-26 00:00:00'   <=== same date, different times == no match

Instead, use the range comparison I mentioned above:

          WHERE ClockInDate  >= {startDate}
          AND      ClockInDate < {dayAfterStartDate}

... or translated:

        AND    ClockInDate >= '2018-03-17'
      AND    ClockInDate < dateAdd(d, 1, CAST('2018-03-17' AS Date))
0
 
_agx_Commented:
figured this part out :)

where cast(ClockInDate as date)

Yep, that does work, but ... generally you want avoid using functions on a column like that. The reason is it prevents the database from using any indexes on that column. (You may not have any indexes on it now, but that might change in the future).  The approach I mentioned above is more "index friendly" and avoids that problem.
0
 
David ModugnoAuthor Commented:
Thank you
0
 
_agx_Commented:
How'd you make out with the queries? Any further questions?
0
 
David ModugnoAuthor Commented:
Thanks for all the help... i was able to follow the suggestion and get things working
0
 
_agx_Commented:
Good to hear. Glad it helped.
0
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.

All Courses

From novice to tech pro — start learning today.