I only dabble in SQL from time to time.

Looks like this should be an easy request but its just a bit over my head without spending serious time trying to figure it out.

What we want is a report that I can run that will show the number of hours "worked" logged by users on all tickets within a given time frame.

So the criteria would be all work logged since last monday to now (not a solid 7 days back since that would pull hours from a prior week)
work logged for the last month 

Not sure if a report with each user (group by) would be ok or if I need to make individual reports so that people do not look at others hours.

I am in the HD_WORK table and see that I have Start/Stop times in Date Format that I would need to get the difference from, and also a Adjustment_Hours field that I would need to sum into that.  First issue being that they are in different formats.

I see USER_ID to be a way to link it to Who but I need to join that to a table to get the real name.

I imagine if I just join HD_WORK to a table that will link the USER_ID to the actual User Name and find a way to parse out the total work time for a given time range the report will be good.

The MODIFIED field is probably the best variable to use for when the work was done since it would catch both Start/Stop and Adjustment_Hours.

I did not see any kind of way to do this with the Wizard so came here to ask about the SQL query for it. 

My first step to simply try out some of this was:

Select USER_ID, (SUM(STOP) - SUM(START)), SUM(ADJUSTMENT_HOURS) from HD_WORK GROUP BY USER_ID

I have not yet joined the table to get real user names, I have not found a way to convert the date format to actual hours and then add that to the hours from ADJUSTMENT_HOURS, and of course once that is working I need to then bring TIME into the equation so we pull this info for the desired period of time.

Thanks for the Help! (And advice)

Edit: Got the name :)   aka easy stuff

Select USER.FULL_NAME, HD_WORK.USER_ID, (SUM(HD_WORK.STOP) - SUM(HD_WORK.START)), SUM(HD_WORK.ADJUSTMENT_HOURS) from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID
GROUP BY USER.FULL_NAME


Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • I'm interested to see a screen shot of these results. Reason being, we use our ticket entries and work entries as our time cards as well for management when they run their reports. We also use this information to bill back to the corresponding group / company.

    I tried to use what you had to see if i could produce an example, but SQL, I know nothing about and got nothing but errors.
  • You have to make sure to take out the stuff that I plugged in as variables for my AutoIt script and hardcode the name(s) and dates if your running this straight out of a database reader or the KBOX.

    I can say now with testing that it does seem to be working 100% as it should and I have added some additional code so that I can sum the hours directly from the SQL query instead of in my AutoIT script so I will post that for you tomorrow.
Please log in to comment

Answers

0
Well messing around with it while waiting, maybe just maybe I am better at this than I thought.

I could just use some other eyes to check my code and verify its accurate as I know its easy to get SQL Queries that you think are working right but they are giving false information.

My SQL Query 
Select USER.FULL_NAME AS 'Tech Name', SUM(TIMESTAMPDIFF(hour, HD_WORK.START, HD_WORK.STOP)) AS 'Range Hours', SUM(HD_WORK.ADJUSTMENT_HOURS) AS 'Adjusted Hours' from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID
WHERE USER.FULL_NAME LIKE '%Patrick%' AND TIMESTAMPDIFF(hour, HD_WORK.MODIFIED, STR_TO_DATE('08/26/14', '%m/%d/%y %h:%i:%s')) < 0
GROUP BY USER.FULL_NAME;

And since its dynamic nature of each person needing to change the name and the "from date" I decided to wrap this up in an AutoIT script that gives 2 pop up boxes asking for the name & date "from" to check.  Then gives the results in a MsgBox

#include <mysql.au3>

#cs
Select USER.FULL_NAME AS 'Tech Name', SUM(TIMESTAMPDIFF(hour, HD_WORK.START, HD_WORK.STOP)) AS 'Range Hours', SUM(HD_WORK.ADJUSTMENT_HOURS) AS 'Adjusted Hours' from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID
WHERE USER.FULL_NAME LIKE '%Patrick%' AND TIMESTAMPDIFF(hour, HD_WORK.MODIFIED, STR_TO_DATE('08/26/14', '%m/%d/%y %h:%i:%s')) < 0
GROUP BY USER.FULL_NAME;
#ce

$FO = FileOpen("C:\kbox3.txt", 2)
$kname = InputBox("Black Magic Tools", "Please Enter Name To Check in KBOX", "Your Name")
$kdate = InputBox("Black Magic Tools", "Please Enter Date FROM Check in KBOX", "MM/DD/YY")
$sql = _MySQLConnect("snip", "snip", "snip", "snip")
$var = _Query($sql, "Select USER.FULL_NAME AS 'Tech Name', SUM(TIMESTAMPDIFF(hour, HD_WORK.START, HD_WORK.STOP)) AS 'Range Hours', SUM(HD_WORK.ADJUSTMENT_HOURS) AS 'Adjusted Hours' from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID WHERE USER.FULL_NAME LIKE '%" & $kname & "%' AND TIMESTAMPDIFF(hour, HD_WORK.MODIFIED, STR_TO_DATE('" & $kdate & "', '%m/%d/%y %h:%i:%s')) < 0 GROUP BY USER.FULL_NAME")
With $var
While NOT .EOF
FileWriteLine($FO,.Fields("Tech Name").value & @CRLF & .Fields("Range Hours").value & @CRLF & .Fields("Adjusted Hours").value)
.MoveNext
WEnd
EndWith

FileSetPos($FO, 0, 0)
$contents = FileRead($FO)

If StringInStr($contents, $kname) Then
FileSetPos($FO, 0, 0)
MsgBox(0, "Black Magic Tools", FileReadLine($FO, 1) & @CRLF & "Hours Worked Since - " & $kdate & @CRLF & @CRLF & _
"Range Hours From Start & End Time: " & FileReadLine($FO) & @CRLF & _
"Adjusted Hours Manually Entered: " & StringLeft(FileReadLine($FO), 4))

Else
MsgBox(0, "Black Magic Tools", $kname & " Not found in Database")
EndIf


FileClose($FO)
_MySQLEnd($sql)
Answered 08/28/2014 by: ViciousXUSMC
Senior White Belt

Please log in to comment
0
My final Query 

Select USER.FULL_NAME AS 'Tech Name', SUM(TIMESTAMPDIFF(minute, HD_WORK.START, HD_WORK.STOP)/60) AS 'Range Hours', SUM(HD_WORK.ADJUSTMENT_HOURS) AS 'Adjusted Hours' from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID
WHERE USER.FULL_NAME LIKE '%Patrick%' AND HD_WORK.MODIFIED BETWEEN STR_TO_DATE('08/24/14 00:00:01', '%m/%d/%y %H:%i:%s') AND STR_TO_DATE('08/29/14 23:59:59', '%m/%d/%y %H:%i:%s')
GROUP BY USER.FULL_NAME;

Ended up with a to/from date so we can use one program to get any result, also changed timestampdiff from hours to minutes because it was not pulling differences from a single ticket unless they were an hour, added /60 to convert the minutes back to hours.

To run this using AutoIT my script ended up as:

#include <mysql.au3>

$FO = FileOpen("C:\kbox3.txt", 2)
$kname = InputBox("Black Magic Tools", "Please Enter Name To Check in KBOX", "Your Name First or Last")
$kdate = InputBox("Black Magic Tools", "Please Enter Date to check FROM (Start)", "MM/DD/YY")
$kdate2 = InputBox("Black Magic Tools", "Please Enter Date to check TO (End)", "MM/DD/YY")
$sql = _MySQLConnect("my database information")
$var = _Query($sql, "Select USER.FULL_NAME AS 'Tech Name', SUM(TIMESTAMPDIFF(minute, HD_WORK.START, HD_WORK.STOP)/60) AS 'Range Hours', SUM(HD_WORK.ADJUSTMENT_HOURS) AS 'Adjusted Hours' from HD_WORK INNER JOIN USER ON USER.ID=HD_WORK.USER_ID WHERE USER.FULL_NAME LIKE '%" & $kname & _
"%' AND HD_WORK.MODIFIED BETWEEN STR_TO_DATE('" & $kdate & " 00:00:01', '%m/%d/%y %H:%i:%s') AND STR_TO_DATE('" & $kdate2 & " 23:59:59', '%m/%d/%y %H:%i:%s') GROUP BY USER.FULL_NAME;")

With $var
While NOT .EOF
FileWriteLine($FO,.Fields("Tech Name").value & @CRLF & .Fields("Range Hours").value & @CRLF & .Fields("Adjusted Hours").value)
.MoveNext
WEnd
EndWith

FileSetPos($FO, 0, 0)
$contents = FileRead($FO)

If StringInStr($contents, $kname) Then
FileSetPos($FO, 0, 0)
$line1 = FileReadLine($FO, 1)
FileSetPos($FO, 0, 0)
$line2 = FileReadLine($FO, 2)
FileSetPos($FO, 0, 0)
$line3 = FileReadLine($FO, 3)
$linetotal = ($line2 + $line3)

MsgBox(0, "Black Magic Tools", $line1 & @CRLF & "Hours Worked From - " & $kdate & " To " & $kdate2 & @CRLF & @CRLF & _
"Hours Logged From Start & End Time: " & StringLeft($line2, StringInStr($line2, ".")+2) & @CRLF & _
"Adjusted Hours Manually Entered: " & StringLeft($line3, StringInStr($line3, ".")+2) & @CRLF & @CRLF & _
"Total Hours: " & StringLeft($linetotal, StringInStr($linetotal, ".")+2))


Else
MsgBox(0, "Black Magic Tools", $kname & " Not found in Database")
EndIf


FileClose($FO)
_MySQLEnd($sql)
Answered 08/29/2014 by: ViciousXUSMC
Senior White Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share