How to show zero values in conditional sums
 

I am trying to write a SQL statement to pull some data into an Excel
(2007)spreadsheet from our SQL Server (2005). In essence I am trying to
manipulate the same column twice. The only way I know how to do this is to
create two SQL queries. The problem is that the number of rows do not match.
Can I show zero values for items that do not meet the conditions?

I am trying to show total time entered into our timesheet program vs total
billable time for a given period.

Here are the queries

To show BILLABLE TIME:

SELECT EMPLOYEE, SUM(TOTAL) AS BILLABLE
FROM TRANS
WHERE (DATE CONVERT(DATETIME, '2007-06-01 00:00:00', 102))
AND (DATE < CONVERT(DATETIME, '2007-07-01 00:00:00', 102))
AND (BILLABLE = 'T')
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE

To show TOTAL TIME:
SELECT EMPLOYEE, SUM(TOTAL) AS BILLABLE
FROM TRANS
WHERE (DATE CONVERT(DATETIME, '2007-06-01 00:00:00', 102))
AND (DATE < CONVERT(DATETIME, '2007-07-01 00:00:00', 102))
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE


Thanks for your help...

Jeff

How to show zero values in conditional sums
 

"JMillians (beginner)" wrote:

I am trying to write a SQL statement to pull some data into an Excel
(2007)spreadsheet from our SQL Server (2005). In essence I am trying to
manipulate the same column twice. The only way I know how to do this is to
create two SQL queries. The problem is that the number of rows do not match.
Can I show zero values for items that do not meet the conditions?

I am trying to show total time entered into our timesheet program vs total
billable time for a given period.

Here are the queries

To show BILLABLE TIME:

SELECT EMPLOYEE, SUM(TOTAL) AS BILLABLE
FROM TRANS
WHERE (DATE CONVERT(DATETIME, '2007-06-01 00:00:00', 102))
AND (DATE < CONVERT(DATETIME, '2007-07-01 00:00:00', 102))
AND (BILLABLE = 'T')
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE

To show TOTAL TIME:
SELECT EMPLOYEE, SUM(TOTAL) AS BILLABLE
FROM TRANS
WHERE (DATE CONVERT(DATETIME, '2007-06-01 00:00:00', 102))
AND (DATE < CONVERT(DATETIME, '2007-07-01 00:00:00', 102))
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE


Thanks for your help...

Jeff

Sorry for the typo in the original message - the second query actually
begins with
SELECT EMPLOYEE, SUM(TOTAL) AS TOTAL