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