K1000 Report for Logged Work Hours?
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
2 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
ViciousXUSMC
10 years ago
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)
Posted by:
ViciousXUSMC
10 years ago
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)
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. - pdsmith_barbnet 10 years ago
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. - ViciousXUSMC 10 years ago