Would someone be able to help me construct an SQL command to do the following report?

I would like a list of employees down the left side and total hours, by month in the columns to the right.  It would also be nice to be able to have hire and termination date.

Is this doable, or am I just dreaming?

This is basically a TimeByName report. In QuickBooks the nearest result to your request looks like this:

With QODBC the same report can be generated using stored procedure reports like this:

sp_report TimeByName show Label, DurationHours_1 as Oct07, DurationHours_2 as Nov07,
DurationHours_3 as Dec07, DurationHours_4 as Jan08, DurationHours_5 as Feb08,
DurationHours_6 as Mar08, DurationHours_7 as Apr08, DurationHours_8 as May08,
DurationHours_9 as Jun08, DurationHours_10 as Jul08, DurationHours_11 as Aug08,
DurationHours_12 as Sep07, DurationHours_13 as TOTAL
parameters DateMacro='ThisYear', SummarizeColumnsBy='Month' where Label like 'Total%'

It's not possible to get the hire and termination date within the one SQL statement, but you can extract them by running:

SELECT Name, SSN, HiredDate as "Hired Date", ReleasedDate as "Termination Date", IsActive as "Active" FROM Employee

Thanks for the reply Tom.  I want to dump the report into Excel to do additional calculations, and the time frame will change each time we run it. The purpose of this exercise is to calculate a moving (rolling) average of an employees hours to determine if they are eligible for benefits.  We haven’t yet decided if the average will be the preceding 12 months or the preceding 3 months.  Using the SP_Report approach looks like it would create a lot of work each time the report is run (because of the changes that would be required).

Is it possible to get employee name, month and total hours for the employee and month from the TimeTracking table, if I don’t have the requirement to spread the hours to a different column for each month?  Such as:

Joe Blow          January 2007    172.00

Mary Doe        January 2007    150.00

Joe Blow          February 2007   160.00

If that were possible I could do some of the calculations in the adjacent columns, then automatically feed it to a Pivot Table to put it in the format I want.

 Thanks for your help.

You asked for by month, so that's what I did. If you just want the total, then this would do the trick:

sp_report TimeByName show Label, DurationHours_13 as TOTAL
parameters DateMacro='ThisYear', SummarizeColumnsBy='Month' where Label like 'Total%'

You can do all sorts of things, see: Table Releations for TimeByName for more variations.

This will give me the grand total hours by employee

SELECT TimeTracking.EntityRefFullName, Sum(TimeTracking.DurationMinutes/60) FROM TimeTracking GROUP BY TimeTracking.EntityRefFullName

This will give me Employee, Year and Month as a Character string, and hours for each day.

 SELECT TimeTracking.EntityRefFullName, {fn LEFT({fn CONVERT(TxnDate,SQL_CHAR)},7)} as "YrMo",TimeTracking.DurationMinutes/60 as "Hours" FROM TimeTracking

What I want to get is total hours per employee, by month. Ignorant as I am, I would have though that I could add a GROUP BY to the second one with the EntityRefFullName and the formula for the TxnDate, or name given to it of YrMo, but every time I try I get errors.  I am using MSQuery, if that makes a difference.

Sorry, a GROUP BY cannot be used on expressions (evaluated values or functions). It must be a real column.

This will give you the hours for this month:

sp_report TimeByName show Label, DurationHours
parameters DateMacro='ThisMonth', SummarizeColumnsBy='TotalOnly' where RowType='DataRow'

The DateMacro values available to you are: |All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate
|ThisQuarter|ThisQuarterToDate|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth
|LastMonthToDate|LastQuarter|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks
|NextMonth|NextQuarter|NextYear|

or if you want to define a DateFrom to DateTo period to use, use:

sp_report TimeByName show Label, DurationHours
parameters DateFrom={d'2007-01-01'}, DateTo={d'2007-03-30'}, SummarizeColumnsBy='TotalOnly'
where RowType='DataRow'