I continue to struggle with what I believe to be basic SQL and dealing with differences between timestamps.
I have a table as follows:
Employee#, Job#, Dept#, Category#, Timestamp
3,C92123,10,115,2013-01-06 15:09:40.25
3,C92123,10,120,2013-01-06 15:29:40.25
5,C92123,10,115,2013-01-06 15:09:40.25
5,C92123.10,120,2013-01-06 15:29:40.25
3,S00001,99,50,2013-01-06 18:09:40.25
3,S00001,99,999,2013-01-06 19:19:40.25
.......
What I would like to do is to group the data as follows by combining the columns so the basic difference between can calculated. But, maybe they don't even need combined, but instead just give the difference. e.g.
3,C92123,10,115,2013-01-06 15:09:40.25,2013-01-06 15:29:40.25
5,C92123,10,115,2013-01-06 15:09:40.25,2013-01-06 15:29:40.25
I researched on the web and found everything from basic select statements to statements with fancy functions and left outer joins that I didn't couldn't make work.
I will need to group this in several different ways for employee id#, job#, dept#, and category# depending on the type of report needed. For instance total time on a job, total time on a job by employee, time for an employee for a year by category, etc. i.e. the table massaged about every different way. In my old Access tables, I didn't have timestamps, but instead had already calculated the difference and entered that directly.
I am sure I am once again over thinking this and is something fairly simple. Maybe I need a "scrartch" table that temporarily holds the info with timestamps and then maybe does the calculation via a trigger and save it in another table like I had with Access?
Thanks for your thoughts.
I have a table as follows:
Employee#, Job#, Dept#, Category#, Timestamp
3,C92123,10,115,2013-01-06 15:09:40.25
3,C92123,10,120,2013-01-06 15:29:40.25
5,C92123,10,115,2013-01-06 15:09:40.25
5,C92123.10,120,2013-01-06 15:29:40.25
3,S00001,99,50,2013-01-06 18:09:40.25
3,S00001,99,999,2013-01-06 19:19:40.25
.......
What I would like to do is to group the data as follows by combining the columns so the basic difference between can calculated. But, maybe they don't even need combined, but instead just give the difference. e.g.
3,C92123,10,115,2013-01-06 15:09:40.25,2013-01-06 15:29:40.25
5,C92123,10,115,2013-01-06 15:09:40.25,2013-01-06 15:29:40.25
I researched on the web and found everything from basic select statements to statements with fancy functions and left outer joins that I didn't couldn't make work.
I will need to group this in several different ways for employee id#, job#, dept#, and category# depending on the type of report needed. For instance total time on a job, total time on a job by employee, time for an employee for a year by category, etc. i.e. the table massaged about every different way. In my old Access tables, I didn't have timestamps, but instead had already calculated the difference and entered that directly.
I am sure I am once again over thinking this and is something fairly simple. Maybe I need a "scrartch" table that temporarily holds the info with timestamps and then maybe does the calculation via a trigger and save it in another table like I had with Access?
Thanks for your thoughts.
Comment