Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I ge tmy sum to show the + sign for positive sums? | Excel Worksheet Functions | |||
Help - Conditional Sums | New Users to Excel | |||
Conditional sums | Excel Worksheet Functions | |||
Conditional Sums | New Users to Excel | |||
conditional sums | Excel Programming |