SQL & GROUP BY

[hstello]

I have a rather simple SQL statement, but I can't get it to work properly. I have four columns in the activities table "LoginID", "StartDateTime", "StopDateTime", and "ProjectID". In a second table, Projects, I have "ProjectID" and "ProjectName". The goal of the SQL statement is to join the two ProjectID together, and get the difference in the stop and start time and any projects that are the same, sum or group them together. My SQL statement looks like this:

SELECT datediff('n', [StartDateTime],[StopDateTime]) AS TotalDay, Projects.ProjectName 
FROM Activity INNER JOIN Projects ON Activity.ProjectID = Projects.ProjectID 
GROUP BY Projects.ProjectName, Activity.LoginID, Projects.ProjectID, Activity.StartDateTime, Activity.StopDateTime 
HAVING (Activity.StartDateTime Between #9/25/2008# AND #9/25/2008 8:13:29 AM# AND Activity.LoginID=1) 
ORDER BY Projects.ProjectName

Long, but straight forward. I've tried adding SUM around the datediff, but that doesn't work. In the activities table I have two entries that the SQL statement selects and the "ProjectID" are identical on both of them, but the project is I keep getting back both entries (the same two rows), with the correct time difference. What I expect to get back is one row with the values/times combined. Does anyone know how I can solve this problem with a single SQL statement, multiple SQL statements, or an operation on a dataset?

Thanks in advance,

Harry

[coolsti]

I don't have any time to look into this in detail but your group_by clause looks way too wild. Too many things you are grouping by and this should not be necessary.

First of all, to get this query working, stop using the Projects table. This table is only supplying you with the project name for the given project ID, and so you can attempt to get the query working on the basis of Project ID alone. Once you get the query like you wish it, it will be not so hard to add a join to the Projects table to extract the project name as well.

You should be able to do what you want in one query. You can calculate the time interval for each row as I think you are doing. Then try to do a group by projectID with a sum(TotalDay). This should give you the total time used for the project by all users. You could then do a group by projectID,LoginID to also break it down to projectID and user.

If you are only looking to extract one user at a time always, then the query may be more efficient to drop the LoginID from the group by clause, and instead add a "where LoginID = XXX" just before the group by clause.