Posted by Erland Sommarskog on 08/24/07 21:39

Paul (heythereto@yahoo.ca) writes:
> So far so good. But what I want to do next is take the sum of the last
> 3 columns and group them by ent.colDate. Here is the SQL that I have
> tried to execute. Obviously the code is wrong, but I am pasting it
> here because I think it should be obvious what I am TRYING to
> accomplish.

Illegal and made-up syntax is rarely obvious. This may be what you
are looking for:

SELECT ent.colDate,
SUM(act.TimeSpent) AS [Indirect Service Time],
SUM(ser.TimeSpent) [Direct Service Time],
SUM(ent.colTravelTime)
FROM tblEntry ent
JOIN tblEmployee emp ON ent.colEmployeeID = emp.colEmployeeID
JOIN tblGeneralNote g ON ent.colCaseNoteID = g.colCaseNoteID
JOIN tblClient c ON g.colClientID = c.colClientID
JOIN (SELECT colEntryID, SUM(colTimeSpent) AS TimeSpent
FROM tblEntryActivity
GROUP BY colEntryID) AS act ON ent.colEntryID = act.colEntryID
JOIN (SELECT colEntryID, SUM(colTimeSpent) AS TimeSpent
FROM tblEntryService
GROUP BY colEntryID) AS ser ON ent.colEntryID = ser.colEntryID
WHERE ent.colDate > DATEADD(month, -1, GETDATE())
AND ent.colDate <= GETDATE()
AND emp.colEmployeeID = 87
GROUP BY ent.colDate
ORDER BY ent.colDate

I first rewrote the query into JOIN syntax, as I find this syntax
easier to read, not the least when I work with derived tables. This
syntax is also required when you work with outer joins in SQL 2005
or later.

I then introduced two derived tables. A derived tables is logically
a temp table within the query, but it is not materialised, and the
optimizer can recast the actual computation order, so if these
Activity tables are huge, it is not likely that SQL Server will
compute the sum for all entry ids, but only for those that are
determined by the where clause.

Note here that I don't know your tables and keys, so the above is a
bit of guesswork. If the query does not cut it for you, please post

o CREATE TABLE statements for your tables, preferrably simplified.
o INSERT statements with sample data.
o The desired result given the sample.

Here is a second version of the query:

SELECT ent.Date,
SUM(act.TimeSpent) AS [Indirect Service Time],
SUM(ser.TimeSpent) [Direct Service Time],
SUM(ent.TravelTime)
FROM Entry ent
JOIN Employee emp ON ent.EmployeeID = emp.EmployeeID
JOIN GeneralNote g ON ent.CaseNoteID = g.CaseNoteID
JOIN Client c ON g.ClientID = c.ClientID
JOIN (SELECT EntryID, SUM(TimeSpent) AS TimeSpent
FROM EntryActivity
GROUP BY EntryID) AS act ON ent.EntryID = act.EntryID
JOIN (SELECT EntryID, SUM(TimeSpent) AS TimeSpent
FROM EntryService
GROUP BY EntryID) AS ser ON ent.EntryID = ser.EntryID
WHERE ent.Date > DATEADD(month, -1, GETDATE())
AND ent.Date <= GETDATE()
AND emp.EmployeeID = 87
GROUP BY ent.Date
ORDER BY ent.Date

I just wanted to show how much clearer and concise the query get
without those redundant col and tbl prefixes.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]