How to show zero values in conditional sums

  • Thread starter Thread starter JMillians (beginner)
  • Start date Start date
J

JMillians (beginner)

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
 
JMillians (beginner) said:
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
Click to expand...

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