How to show zero values in conditional sums

  • Thread starter JMillians (beginner)
  • 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
 
J

JMillians (beginner)

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top